Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Pivot table; remove old values from slicer

Tags:

excel

I have two pivot tables that are connected with a slicer.
The slicer is used to set the date on both pivot tables.

The problem is that it keeps showing old values that are not in the data anymore.
On one of the files (I have four similar files) I have deleted everything that is newer than 2016-05-09, but still the slicer is showing other dates that month.
If I click on them the tables are empty so clearly it can't find any values with those dates.

The old values are written with gray text.
I have tried to find a solution to this issue on google but no luck.
I have tried to update both tables and I have even removed the slicer and then created a new one, but still the old values does not go away.

Anyone have a clue why it's doing this? Excel 2010.

like image 820
Andreas Avatar asked Jul 01 '16 13:07

Andreas


People also ask

Why does PivotTable filter shows old data?

Those old items can appear if you change the pivot table source data – for example, you might remove a few obsolete products, or change a sales rep's name. When you refresh the pivot table, the new data can appear, but the old names still show up in the drop down lists, that you use for filtering.

How do I remove a value from a slicer in Excel?

To clear any filters applied, click the “Clear Filters” button in the upper-right corner of the slicer pane. To delete a slicer, click to select the slicer pane and then press the “Del” or “Delete” key on your keyboard.


1 Answers

This has nothing to do with the slicer.This is to do with the PivotCache for the PivotTable. If you check the Dropdown filters on the PivotTable , you will also see there is checkboxs for data that is not currently in your dataset.

To change this:-

Right click your PivotTable> PivotTable Options

On the Data Tab , Change "Number of items to retain per field" from Automatic to None and press OK.

Then refresh your PivotTable. The Filters on the PivotTable and the Slicer will now only show values that are currently in the dataset.

like image 190
Steven Martin Avatar answered Sep 22 '22 02:09

Steven Martin