Excel – How to remove a duplicate value on slicer in Excel

microsoft excelpivot tablepivot-chart

I am trying to build a pivot table to compare some data.

I first made a Excel sheet for my colleagues to enter their respective data, expecting that discrepancies when they enter the data, I used drop down lists for all the standard input like, City, Province & Month.

They have populated the fields and mailed back their data, I have now pasted the data to one sheet, made a table and from that made a Pivot table, however when I Add a slicer for the Month, it duplicates the August option and slices some cities with the one option and others with the other August option.

How can I fix this problem? I am using Excel 2013

Best Answer

There are two possibilities.

First one is duplicate values entered in source data. Check it first.

The Second one should with Pivot Cache.

In this case you check the Dropdown Filters on the PivotTable , you will find there are check boxes for data, which are not currently part of your database.

Now the solution is,

Right click on PivotTable, go to PivotTable Options, on the Data Tab you find Number of items to retain per field change it from Automatic to None finally press OK.

Then refresh the PivotTable. The Filters on the PivotTable and the Slicer will now show only unique data.