When you create a pivot table it groups the items from your data, and calculates a total for each group. In the screen shot below, you can see the total sales for each category, in each city in the West region.
Occasionally though, you might see duplicate items in the pivot table. For example, when the East region is selected, Boston appears 3 times, instead of just once.
Check the Source Data
Even though these items look like duplicates, there is something different about them, and that’s why they’re appearing on separate rows in the pivot table. Usually, the problem in trailing spaces – one or more space characters are at the end of some items in the data, but not all of them.
To check the Boston entries, you can use the LEN function, to find the length of each item. It shows that the first instance, in cell A6, has 6 characters, and there are extra characters in cells A7 and A8.
Find the Problem Data
To get rid of the duplicates in the pivot table, you’ll need to clean up the source data. However, the filters in an Excel table ignore trailing spaces, so it’s not easy to find the problem entries.
If you use the drop down filters to check the source data, you’ll only see only entry for Boston, even though some of those rows have space characters at the end.
One-Time Cleanup
If the records were manually entered, and you just want to do a one-time cleanup, follow these steps:
- Filter the table to show just the Boston records
- Type “Boston” in the first record, to overwrite the existing City entry
- Select that cell, and point to the fill handle, at the bottom right corner of the cell
- Double-click on the Fill Handle, to copy the entry down to the last filtered record. (Scroll down to make sure that all the records were changed.)
- Clear the filter on the City column
- Refresh the pivot table, and the duplicate items will disappear.
Add a New Column
If the records were imported from another source, and you expect that the problems with trailing spaces will be ongoing, you can use a TRIM formula to clean up the data. It will remove any leading or trailing space characters, and any multiple space characters between words.
Follow these steps to add a new field:
- Insert a new column in the source data, with the heading CityName.
- In Row 2 of the new column, enter the formula =TRIM(C2).
-
TRIM formula in source data table
-
- Copy the formula down to the last row of data in the source table. If the source data is stored in an Excel Table, the formula should copy down automatically.
- Refresh the pivot table
- Remove the City field from the pivot table, and add the CityName field to replace it.
Video: Remove Duplicates in Excel List
To see the steps for removing duplicates from a worksheet list in Excel, you can watch this 4-minute video. Remember to make a backup of the Excel file, or the worksheet list, before you start to remove duplicates.
Note: The timeline is listed below the video.
Video Timeline
- 0:00 Excel List With Duplicate Items
- 0:21 Make Backup Copy of Excel List
- 0:42 Use Remove Duplicates Tool
- 1:24 Excel List – Two Column Duplicates
- 1:58 Select Columns
- 2:50 Excel List – Multiple Column Duplicates
- 3:42 Excel List – Multiple Column Example 2
________________
This used to happen a lot in a project I did some years ago.
And I used the CLEAN function as well, to clear off any debris that the TRIM function doesn’t take care of 🙂
=CLEAN(TRIM(A2))
Also if the data is being refreshed in a data table from an external data source, the cleanup formula would have to be put after the last column in the data table, so that it doesn’t get over-written when the data is refreshed.
Thanks!!! It worked :)!!
it works. thank you
A coworker found me a fantastic add-in for the TRIM function from Ablebits.com – it has saved me so much time in data scrubbing. I get a monthly worksheet with a ridiculous amount of leading spaces over several columns and since I put the add-in on my Quick Access toolbar, it is just one click on it and one click to confirm, and all those columns are cleaned up.
LEN function solved my problem. Very thankful for these helpful tips
The only way I was able to fix this was to take the data, copy it, then paste values into a new spreadsheet. After that it worked fine. I tried every solution listed above and more and it did not work.
Yep, same here. Nothing else worked. I think this is an ancient Excel bug.
In a way I would be glad if this is the reason. I had a worksheet with about 100 randomly generated numbers in Excel, resulting in either 1.5 or 0.6 to multiply 100 with a few times, and then had a summary in a pivot table. Some of these numbers appeared in two different rows. For example 100*1.5*0.6*0.6*.6*1.5 gives 48.6 (exact, no rounding). However, the pivot table shows two separate rows of 48.6. Adding to the annoyance is the fact that the original data show this number occurring 20 times, but the pivot tables counts 9 and 12, giving 21. I checked the original data in every aspect I could find as a cause, but even Excel says all the 48.6s are the same (no number/text issues, no spaces, no decimal differences). This means Excel is not reliable.
Amazing, thank you! 🙂
Another problem for duplicates could be that the word/number is written as a number/word.
LEN did the trick! Thanks for the helpful guide, this has been a real annoyance and it’s good to finally have a solution.
It worked! thank you!
My problem was that it was duplicating years. It was Simke’s comment that did it for me. I tried the LEN solution, but there were no deviant values. I tried the paste value solution but that did nothing.
Then, after reading Simke’s comment, I multiplied the entire column by 1 to convert any text fields into values. Bingo, no more duplicates and all in order. Thank you!
Thank you so much. I’ve been up all night trying to figure this out. This really helped! 😊
Thanks a million.
I was really struggling with such a issue
thanks a lot! have solved my problem with double category on columns. how about if there is blank on the category but in reality there are no blanks?
I have a spreadsheet with formulas to determine “Done” and “Incomplete”. It’s duplicating the data even though they’re the exact same characters. Do I have to copy/paste without the formulas just to make the piviot work?
Sabrina
I didn’t find a solution, i do have similar issue, which seems to be due the usage of formulas.
Excellent point on the fill handle. I was losing my cool and that solved everything. Thank You!
Your response is unmatched and not found amongst anyone at Microsoft.