How To Prevent Pivot Tables From Changing Source Data Numbers Into Dates In Field Columns
May 13, 2013
why is pivot table changing numbers to dates. It was fine all along, numbers retained the fromat of source data and now, all numbers in field columns are turning into dates. is there a setting I can change to prevent excel from reformatting numbers to dates?
I have a single workbook with multiple worksheets. Each worksheet has a different pivot table displaying a different view of the data. Each pivot table uses the same source data at worksheet1.
Each week i add new data to the end of the source data, which means that I need change the source data reference separately in each pivot table to update each pivot table view to include the new data. This is laborious as there are quite a few pivot tables.
Was wondering if there is some way of changing the pivot table source data reference on all pivot tables at the same time.
I am using pivot tables which were created by another (no longer contactable) and have found two fields in the field list which are not part of the source data i.e. neither are column headings but are used in the pivot table.
My questions are:
1. How are these field created?
2. How can I examine these fields for formulae (as I have found one of them returns an incorrect value)?
Field1 return a numerical value which is the difference between two columns Field2 shows this as a % (but incorrectly)
I have multiple pivot tables with different data source. I wish to have one slicer which control all the pivot tables. I would have one common colum for all the pivot tables which is the one i wish to control for instance the person in charge. Note that all my pivot tables are from different data source. how to do it?
The spreadsheet is comprised of 4 sales data sheets, one for each store. Each of these sales data sheets runs several pivot tables (One for dept sales, one for waste, one for sales on Selected UPCs etc) and I used to have 1 copy of each pivot table for each store (ie 24 pivot tables). Now I have found a way to have only 1 copy of each PivotTable which changes based on which store is selected using the following code:
I am trying to generate several pivot tables from one data source table. I have successfully created my first pivot table (A date field, and a water storage facility level reading) and subsequently a graph from this. I have worked out that I need to group my dates as I am supplied a daily reading, but only need monthly average. All worked great.
Now i need to create more pivot tables and graphs. The next one I want is to create one grouped by years. But when I create this new pivot table and change the grouping of the date field to yearly, it also changes the grouping on my first pivot table, which is undoing my work.How to tell excel that these pivots are independent, and I don't want them changing in unison? See screen grab of my source data and where I am up to...
Microsoft Excel - 401027_0100.00_0221.00.csv_2014-03-10_11-41-35.jpg
I have the following code to update a pivot table:
Code: Dim pt As PivotTable Application.EnableCancelKey = xlDisabled For Each pt In ActiveWorkbook.Worksheets("sheet1").PivotTables pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create _ (SourceType:=xlDatabase, SourceData:="source!R4C1:R33443C55" _ , Version:=xlPivotTableVersion10)
the R33443 term is what will be changing, the columns and the starting row should stay the same. is there a way to instead of using R33443, to enable the range to be changed based on a cell value?
I wish to hide some columns that contain data used to update a pivot table in another worksheet. What I want to know is though, will this affect the pivot table? I think that graphs in excel will normally ignore data in hidden cells, so I was wondering if it was the same with pivot tables.
Ive attached a spreadsheet showing what im trying to achieve. basically i have a scheduling objective, in which only a few slots (AA, BB, CC) are available. so the starting availability is fixed. for this example assume the starting values are 6,7, and 8 respectively. as 'bookings' are entered, im using the value of -1 against A, B, or C respectively, to indicate that AA, BB, or CC should be decreased by 1 unit of availability.
im trying to incorporate the subtraction within a pivot table. as you can see i am way off. the hypothetical formula logic that im trying to implement within the PivotTable would look something like this:
for A,B or C, sum all (-1) values for each instance of Date and Stage... this will give total bookings
add this total value to the starting values of AA, BB, CC respectively to get the new availability
When i tried to drop another field into data field, by default, the new data field will appear below the field which was already in there. However, i would like the new field showing in an new column - horizontally - rather than showing in a new row.
For example: Rather than the Pivot table appear like this:
I have my raw data in one Worksheet and have made multiple pivot tables based off this data. Since I needed to have the same calculated fields in all these Pivot Tables, I used to simply duplicate the pivot table sheet and edit the pivots to create a new table.
Now, I'm trying to create a new pivot table from scratch, by selecting the base data table and going to "Insert Pivot Table". However I see the same Calculated Fields and upon removing, the field is removed from all other Pivot Tables.
there are a multitude of issues with US date formats when you're not in the US but I've run across one that I can't figure out.
I have a source table that has approx 5000 lines on it, everything looks correct and all the dates are in the correct (Australian) format. However when I use it to make a pivot table, any dates that are before the 12/m/yyyy gets changed to the mmddyyyy format in the pivot table only, all the source dates are still correct.
When I enter this : =now(). it gives the local date and time in my PC in that exact cell. assume it is in (A2). when I enter in Any cell in the spreedsheet the sam formula =now(). then the previous formula (A2) is changed to the new date and time.
I am trying to write a list of pivot table page filter pivot items to the Immediate window - but only hidden items. The code below should do the job where the active sheet is a pivot table.
However when I change the page filter pivot items being hidden, the pivot items returned by the macro don't change. It seems to assume that all pivot items are hidden when in fact it may be only one or two. It will work though for pivot row items (pvt.RowFields) and pivot column items (pvt.ColumnFields) where the user changes them.
Does the pf.HiddenItems collection work for RowFields and ColumnFields but not PageFields? If so, is there a pivot field object that reliably holds hidden pivot items residing in the page filters?
Dim wb As Workbook Set wb = ThisWorkbook Dim ws As Worksheet Set ws = wb.ActiveSheet Dim pvt As PivotTable Dim pf As PivotField Dim pi As PivotItem
Using Pivot Table, I added a field to the "Values" field in order to give me a sum of the numbers. The title automatically changes to "Total", I am needing it to show "Total Invoice to OOM Delta". Is there a way to do this? I tried the Active Field option, but that's not working for some reason.
I have a report that was created for 2005 that contains two worksheets: a "source data" worksheet and a " pivot table" worksheet. I cleared out the 2005 data in the "source data" worksheet and replaced it with 2006 data...after this I refreshed the Pivot Table and everything seemed fine. When looking at the file size I noticed that it was almost twice its original size....upon further investigation I found that the Pivot Table was internally holding onto the old source data (the "Show" functionality of the rows/columns in the table lists the 2005 row/column headers as well as the 2006 headers....even though no data from 2005 is shown in the Pivot Table).
Does anyone know how to purge the old data from the internal Pivot Table memory?
I hope this is enough information....let me know if you need more.
I have created a pivot table and grouped the dates by month and year. Unfortunately I did not realise I left the auto box clicked which has inserted an end date. Is there any way I can change this easily so that I can insert more data.
I need to modify a pivot table that has dates as one of it's headings. Instead of listing each individual date, I need to group them into months. I know it can be done, but I haven't been able to figure out how to do it.
I often use the same file/pivot table for both month and weekly reporting. In my source data my field headers show 1,2,3,4..etc. This way I can use for month or weekly. But I often change the "Custom" name on the Field Value Settings to something more descriptive like "Jan" or "Week1"
The problem is that each time I update the the "Custom" name the Pivot Table auto refreshes and it takes forever to re-label all the columns.
Is there a way to suppress this refresh when updating the Custom Field Name?
I have a file with numerous data tables that slows down the file significantly due to it's large size. I would like to set the calculation method to Manual, but also be able to manually turn the tables on and off so that they don't slow the file down when I hit F9 to calc. Is there a formula or method to accomplish this? BTW, I can't use VBA.
I am working pivot tables off of one large excel spreadsheet for my data. I have multiple pivot tables on each of 5 worksheets. The problem is this. Every other week I am sent an updated spreadsheet with the same column names each week which I then copy and paste it into the spreadsheet of the last one. When I refresh the pivot tables, I end up with duplicate field sets. For example, on column in the original data sheet is gender. On the first sheet I went through and renamed the "M"'s and "F"s to be "Male" and "Female" so that it is better looking when printed. Now when i get the new information and copy and paste it to rplace the old, and refresh the pivot tables, I end up with "Male" "Female" AND "M" "F". Now the "Male" and "Female" fields are empty and the "M" and "F" fields have the information. Is this a result of me renaming the fields?
I have just started using pivot tables and I have ran into a problem with the count feature. I have an original data list that breaks down in the pivot table to:
date events on that date
Using the pivot table facilities, I want to do a count of those who said they were going to attend, and those who actually attended. The detailing both of these columns on the original list have a true false designation. When I do the count, the summarised information only counts the number of records for each of these events, and not the 'true' field entries for each of these columns.
How do I count these two parameters for 'true' from the original list in the pivot table so I can see a comparison between these two numbers?
I have a huge data set with survey data (sample attached: sample.xlsx). Column headings: survey questions Row headings: respondent identifiers (names) Row data: answers to questions (numeric values, only five answer options 1,2,3,4 or 5, no blanks)
I need to build that kind of pivot table:
Rows - list of particular questions (i.e. questions no. 3, 7 and 12) Columns - list of all diffrent answer entries (eventually, it will be 1,2,3,4 and 5) - it could also be questions in columns and answers in row (no difference) Values - count answer entries (i.e. how many answers "5" are on quesiton 3)
------ UPDATE: explanation added.
The reason I need pivot tables: i'll have to cross analyze multiple sets of questions. I'll have to do such cross-analysis 100+ times, so, writing a formula for each time does not quite work...
I have 29 excel files with some number of worksheets from 1 to 4. The name of the worksheets are the same in all the spreadsheets. Then I've a got a pivot table. I have to compare some data (3 columns) from the pivot table to the numbers from all these sheets from 29 excel files.
I am trying to create a macro which will allow me to change the source data for a particular chart. what i want to do is to have a code which looks in a range of cells (A8:B28) and then updates the chart to only include those cells within the range that contain a value? The number of cells with values changes depending on what options are selected elsewhere in the spreadsheet.