Home > Pivot > Errors > Duplicates Pivot Table Duplicate Items When you create a pivot table it groups the items from your data, and calculates a total for each group. Occasionally though, you might see duplicate items in the pivot table. Here's how to solve that problem

1) Duplicate Items in Pivot Table

When you create a pivot table it groups the items from your data, and calculates a total for each group. Occasionally though, you might see duplicate items in the pivot table.

In the following sections, there are some reasons why a pivot table might show:

-- Duplicates for text items

-- Duplicates for numbers

2) Duplicate Text Items

In this example, there are duplicates for one of the text items in the Row area -- Boston appears 3 times, instead of just once

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.

2.1) Clean Text Entries in Source 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.

There are steps below for 2 types of data cleanup, to help remove pivot table duplicates:

-- A) One-time data cleanup

-- B) Add Cleanup Formula Column

2.1 A) One-Time Cleanup

If the records were manually entered, and you just want to do a one-time cleanup, follow these steps:

1. Filter the table to show just the Boston records
2. Type “Boston” in the first record, to overwrite the existing City entry
3. Select that cell, and point to the fill handle, at the bottom right corner of the cell
4. 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.)
5. Clear the filter on the City column
6. Refresh the pivot table, and the duplicate items will disappear.

2,1 B) Add Cleanup Formula 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).

  

• 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.

In the screen shot below, the duplicate city names no longer appear in the pivot table.

3) Duplicate Numbers

If you see duplicate numbers in the Row area, it's usually because there are small, hidden differences in the numbers, caused by the floating point precision that's used in Excel.

For example, this pivot table shows three numbers that look like duplicates, for the Carrot Bars.

To learn more about this problem, go to the Remove Duplicates Problem page.

You'll see why the duplicates are listed (floating point precision), and how to fix the problem by using the ROUND function.

4) Get the Excel Files ✅

• Field Name Error: To see the pivot table "field name is not valid" error message, download the Pivot Table Errors sample file. The zipped file is in xlsx format, and does not contain any macros.

Get Monthly Excel Tips!

Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Sign Up.

Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!

More Pivot Table Tutorials

Error: Field Name Not Valid

Error: Pivot Table Overlap

Error: Couldn't Get Data

Error: Reference Isn't Valid

Hide Error Values in Pivot Table

Pivot Table Details List Macros

List All Pivot Tables – Details

Clear Old Items in Pivot Table