Pivot tables often store data in the cache, and some of that data is also that about slicer elements. Slicers take values from the source table. If we add some of them and then delete them, it may happen that the slicers show phantom values. They are shown in pale color, as if there is no data. And indeed, if we pick such a value, we will get an empty pivot table report. How can slicer elements always be up to date?
For the demonstration, we will use one data table which contains data on workers’ salaries for a period of 6 months. Based on it, we create a pivot table and add a slicer that indicates the month.
Let’s add another row, for the date September 1, 2020, of a worker called “Nikola Tesla” and a salary of 120,000 RSD. If we go to Analyze/Refresh the pivot table and slicer will change. The ninth month will appear in the slicer, and a new worker will appear in the pivot table.
After this operation let’s delete the last row of the source table and refresh the report. There is no more worker named „Nikola Tesla“, but the phantom ninth month remains in the slicer.
How to solve this problem? Go to the Analyze ribbon and open the Pivot Table Options window. If you select the Data tab you will find a group of options marked as Retain items deleted form the data source. The Number of items to retain per field drop-down list has the value Automatic. Change it to None, and then refresh the pivot table again. The phantom ninth month is gone!