How to remove a duplicate value on slicer in Excel

Question

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

Solution

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.

Comments

[Gobabis]

I have tried replacing August with Aug but still have the same problem

[Gobabis]

Tried this did not work

[Rajesh Sinha]

I'm surprised why it is not working !! One question in my mind, are you using single or more than one table?

[Gobabis]

A single table, my suspicion that the data was entered on different excel versions and when I paste to my spreadsheet something just did not copy correctly.

[Gobabis]

What I now did was replace all the August (text) entries with an actual date 1/8/2017 this has solved the problem

[Rajesh Sinha]

Great,, you got a way to get rid from the issue. I'm sure version doesn't create such problem may possible while Paste you pasted the value twice has created duplicates since you got the solution when edited the source.