PivotTable’s are a wonderful thing, but they have their downsides. One of them is the fact that they will retain deleted data from the data source, which shows up in filter drop downs and slicers. This can be slightly (up to extremely) annoying and hard to find where to not show pesky deleted items.
There are two areas where this is most affected – filters and slicers. You can take care of this in one fell swoop with a PivotTable setting change, or you can fix this for slicers only. Both are detailed below. I’ve utilized a Microsoft template using Excel 2013 for this example and screenshots.
The template I started with has a PivotTable already, which just made it easy to use for this example. Here is what it looks like:
There’s some dashboard data up top, then an overall chart, and below that is our PivotTable with slicers to its right. Before we get to the fixes, we need to show the problem, and before we do that, well, we need to create it.
Add a record
To create this particular problem, I’ll just add a new row of data to the data source. Before we get there, you should get an idea of what values are currently in the PivotTable. Here is the filter drop-down for the AREA field:
Notice there are only three unique items listed. We’ll revisit this in a moment. First, let’s create this behavior we’re talking about.
For this particular template used, data input is on the COST INPUT worksheet.
If the totals row is showing for a table, and you can’t just start typing a new row, select the bottom-right-most cell in the table data body range and press Tab.
Now we add a row of data:
So I added a cabinet to the upstairs bathroom. Ooh, and it only cost me $250. Yay! Ok, data added.
If you now look at the pivot table, you won’t automatically see your data.
To refresh the PivotTable, either right-click any cell in it and select Refresh (as seen in the picture below), or go to the PIVOTTABLE TOOLS
ANALYZE ribbon and select Refresh there.
Once you refresh the PivotTable you should now see the newly added data. We will check the filter drop-down, which should show us a list of unique values from that field.
As you can see, there is now an “Upstairs” item listed for that field.
Delete the record
Down to the nitty gritty. We are going to delete this newly added record of data. So go to the data source table, make sure the new row (or any cell in it) is selected and either right-click -> Delete -> Table Rows, or from the HOME tab, select Delete (in the Cells group) -> Delete Table Rows:
If you now refresh your PivotTable and look at your filter drop-down menu, you should notice you will still see data for the deleted row from the source menu! In addition, you will see it on any related slicers as well.
Now for the fixes.
This is a very simple, yet hidden and somewhat cryptic fix. Quite simply if you select your slicer (any slicer you want this to work on, which, alas, must be done individually) you can get to the slicer settings by either the right-click menu, or the ribbon (SLICER TOOLS
OPTIONS -> Slicer Settings):
This will bring up the slicer settings dialog box. What you’re looking for is in the bottom-right corner, a little check box that says “Show items deleted from the data source”. Uncheck that, click OK and your slicer is now no longer showing phantom data.
Fixing the slicers are nice, but that doesn’t clear these deleted items from the PivotTable filter drop-downs. To do this we need to go into the PivotTable Options and look at the Data tab. Here you will see an item that says “Retain items deleted from the data source” with a combobox for you to set the number of items to retain per field:
By default it’s in automatic. Just change this to None and click OK. Voila! That’s it, you’re done. No more phantom deleted items showing up in your PivotTable. The best part of this fix is it takes care of the slicers too. Think of it as a cascade of deleted data. Fix the PivotTable and it will fix the slicers – all of them (using that data source).
While PivotTable’s are a tremendously powerful tool, sometimes the default settings aren’t so helpful. This is one of those ‘PivotTable’s 101’ things, but is hard to find if you don’t know where it’s at.