2

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

Improve this question
1
  • I have tried replacing August with Aug but still have the same problem
    – Gobabis
    Commented Nov 18, 2017 at 7:21

1 Answer 1

Reset to default
2

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.

Improve this answer
5
  • Tried this did not work
    – Gobabis
    Commented Nov 18, 2017 at 9:06
  • I'm surprised why it is not working !! One question in my mind, are you using single or more than one table?
    – Rajesh Sinha
    Commented Nov 18, 2017 at 9:42
  • 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
    Commented Nov 18, 2017 at 9:46
  • What I now did was replace all the August (text) entries with an actual date 1/8/2017 this has solved the problem
    – Gobabis
    Commented Nov 18, 2017 at 9:47
  • 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.
    – Rajesh Sinha
    Commented Nov 18, 2017 at 9:54

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .