When you set up a pivot table, and put fields into the Rows Area or Columns area, Excel groups the items, and calculates the totals for each group. For example, see count of products for each Unit Price. Each item should only be listed once in the pivot table, but sometimes you might see duplicates.
List of Product Prices
In this example, there’s a long list of products, with their category name, and the product’s unit price.
This screen shot shows the first few rows from the list, and you can get the sample file from my Contextures website. Go to the Remove Duplicate Numbers page, and the download link is at the bottom of the page.
Pivot Table Summary
To summarize that product list, I created a pivot table, based on the list.
In the pivot table, I put Category in the Rows area, and Product in the Values area.
Each Category is only listed once, and the pivot table shows a total count of products, for each Category.
Product Price Pivot Table
Next, I made another pivot table, with Unit Price in the Rows area, and Product in the Values area.
This time, there was a problem – some of the prices were listed twice.
Check the Numbers
The numbers looked the same in the pivot table, and the formula bar didn’t show any differences.
A quick formula, using the equal sign, gave the result of TRUE – the values are equal.
Worksheet Decimal Places
This type of duplicate number problem is usually caused by Excel’s floating point precision.
To troubleshoot this duplicate number problem, I dug into the workbook’s XML code.
- To see how to do that, go to the Remove Duplicate Numbers page, on my Contextures site
In that code,
- one cell had the number: 1.68 – only 2 decimal places
- the other cell had a number with 16 decimal places, and the final digit was a 2
However, on the worksheet, Excel is limited to 15 digits of precision. A formula that compares the cells doesn’t see any difference between them.
The pivot table must be using that buried data, so it shows two “different” numbers.
Create Rounded Numbers
There’s no built-in way to change that floating point precision setting, for the worksheet, or for pivot tables.
NOTE: You can see a detailed explanation of the floating point precision used in Excel on the Microsoft site.
As a workaround, you can use the ROUND function, to reduce the source data numbers to a set number of decimals.
For example, there are unit prices in column C, and the Remove Duplicates feature sees some differences in those numbers. Follow these steps to round the numbers:
- In cell D2, enter this formula, to round the numbers to 12 decimal places:
- =ROUNDE(C2,10)
- Copy the formula down to the last row of data
NOTE: The result should have a total 15 digits or fewer. For example, if there are 5 digits before the decimal point, round to 10 or fewer
Duplicate Text Items in Pivot Table
In other pivot tables, you might see duplicate text items, instead of duplicate numbers. For those duplicates, there might be small differences between the values, and that’s causing the problem.
For example, in the pivot table shown below, Boston is listed 3 times in the City column.
In column H, a LEN formula shows that each items has a different number of characters. There are extra space characters in some of the source data entries, so the pivot table lists each variation.
How to Fix Duplicate Text Items
To get rid of those text item duplicates in a pivot table, you’ll need to clean up the source data.
- You can do a manual cleanup, if there are just a few entries to fix.
- Or, add a new column, with a TRIM formula to remove extra space characters
To see the steps for both methods, go to the Duplicate Items Appear in Pivot Table post, here on the Pivot Tables blog.
Get the Sample Workbook
You can get the sample file for Duplicate Number Problems from my Contextures website. Go to the Remove Duplicate Numbers page, and the download link is at the bottom of the page.
The zipped file is in xlsx format, and does not contain any macros
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
____________________
Duplicate Numbers in Pivot Table Items Problem
____________________