Why do I have to refresh Pivot Table to include all fields?

2
votes

Here is the scenario:

  • Sheet1 has 15 columns and 16,000 rows
  • PivotTable created in Sheet2 for all data in Sheet1
  • 2 new columns of data added in the middle of existing columns in Sheet1. New total is 17 columns
  • Second PivotTable created in Sheet3 for all data in Sheet1

When I create the second PivotTable, based on all 17 columns of data, it only shows the original 15 columns as fields. I have to refresh the PivotTable to show all 17 columns.

I understand that I have to refresh an existing PivotTable after I change the source data, but if I make a second PivotTable after the source data is changed, why does that second PivotTable not show all selected source fields?

Using Windows 7, Excel 2010

UPDATE: I just replicated this condition by doing the following:

  1. Opened new blank workbook, while 3 other workbooks are open in the same instance
  2. Created the following table:

Table 1

  1. Created the following PivotTable:

enter image description here

  1. Added 2 new columns with arbitrary formulas:

(Text boxes, formatting, and header row insertion performed after the fact for ease of explanation)

enter image description here

  1. Create new pivot table - on a new worksheet - and you can see it does not include the new fields "Payment" and "License Factor". Click refresh, and then the two new fields appear.

enter image description here

1
excelpivot-table
I tried to reproduce the scenario you wrote and without luck. have you tried to copy this for a new file? – Balinti
This happens on many different and unique files--I've noticed this occur more often than not over the past 6+ months – Trevor D

1 Answers

2
votes

As pnuts says in the comments, when you create a PivotTable that uses the same data range as an existing PivotTable, Excel simply reuses the PivotCache from the first PivotTable without first refreshing it.

So if you added new columns to the source data after you created the first PivotTable and before you created the second PivotTable, then neither PivotTable will have these columns in the fields pane until you refresh one of the PivotTables.