Before we get into the post though, just a quick reminder that Mynda Treacy's Dashboards Course will be closing on Thursday, so if you haven't already purchased it and don't want to miss out, get yourself over there now, before it's too late - you will also receive a free copy of my Introduction to PivotTables course. You won't regret it.
This week's post was prompted by a question from a client who was baffled by items appearing in the drop-down selection boxes in his PivotTables that were no longer in the data being referenced.
This was a particular problem as the PivotTables were being sent out to individual salespeople reporting on their own data - but because the pivot tables had previously been looking at the whole data set, all of the other salespeople's customers were appearing in the drop-down lists.
I wasn't quite sure myself why this was happening at first, but going through the PivotTable options (accessed by right-clicking on the PivotTable and selecting PivotTable Options...), I discovered the following setting on the Data tab:
Retain items deleted from the data source...Number of items to retain per field
This setting is by default set to Automatic and changing it to None will stop the PivotTable retaining these items in the drop-down lists (they will disappear the next time you click Refresh). It's as simple as that - once you know that the setting is there!
Post Script
Out of curiosity, I tried to find out what the three possible settings are supposed to do (the third setting is Max). Microsoft Excel help was not particularly enlightening on the subject:
Retain items deleted from the data source section
Number of items to return per field To specify the number of items for each field to temporarily cache with the workbook, select one of the following:
Automatic The default number of unique items for each field.
None No unique items for each field.
Max The maximum number of unique items for each field. You can specify up to 1,048,576 items.
It does not give any indication what the default number is or how you specify the maximum!
A search around the web didn't bring up any further clarification, but I'm sure some of our clever readers can elaborate, so please add a comment if you know what the difference is between the Automatic and Max settings!
If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".
Just received the following comment on this post in the Microsoft Excel Users LinkedIn group, from Shane Devonshire of Stanford University:
ReplyDelete"Regarding the Help explanation of what the three choices mean and particularly the "Default". About 7 years ago I decided to check this out. I read through a half dozen book - none with an explanation. I asked on the Microsoft MSD help site - no success. I called Microsoft help - the person I reached did not know the answer but suggested that I pay the $100 fee to get personal help. I hung up and chuckled - I should pay $100 to find out what the "Default" amount is?"
And I thought I was just being stupid!
came across this problem myself a couple of years ago and now an automatic thing I do when I create a Pivot Table is to reset this to "none" - I can't think of any reason why I would want it otherwise
Delete