Duplicate Data in Filter Dropdown

Hello,

I recently created a pivot table that has a field called "City". I noticed duplicates for some cities on the filter dropdown option atop the City row. I checked the data source and everything under City looks accurate, including the format (General). Any thoughts on why the filter dropdown displays duplicates of some cities?

-Tommy

If you are using a Data Filter on a Column, then it will not show duplicates. It will however show duplicates if the source data looks the same, but actually isn't. This could be something as simple as having a space in the source cell in front of the city name, or even less obvious, a space after the city name. First thing to try is to ensure that the source data is uniform, rather than data that just 'looks accurate'. Go back to the source data, apply an autofilter to the appropriate 'city' column. Then filter to the city which currently shows duplicates on the pivot. Pick the first cell & drag it down the column. This ensures every cell actually contains the exact same data, rather than looking like it contains the same data.

You should then refresh the pivot (PivotTable Tools -> Refresh) & when you filter on the city column in the pivot it should show only one option on the list for that city.

To avoid typos in the source data which can lead to such issues, it can be useful to define a Range & have a 'pick from list' drop-down on the cells for data entry to the source. This ensures every time anyone enters the same city name in the source, it really is the same as the last person to enter it. Which in turn, ensures the Pivot output is what you expect.

Dezondi - thanks so much for the advice. It worked!

-Tommy