With the Microsoft Excel Remove Duplicates feature, it's easy to remove all the duplicate values from a list, based on the items in one or more columns. How to troubleshoot problems if Remove Duplicates doesn't work, and duplicate numbers remain in the list.
Video: Remove Duplicates
This video shows the steps for removing duplicate data from a worksheet list, using Excel's Remove Duplicates feature. You'll see the steps for working with a 1-column list, a 2-column list, and a multiple column list in a spreadsheet.
Remember to make a backup of your Excel workbook, or the original dataset list, before you start removing duplicate values. The timeline is below the video.
Remove Duplicates - 1 Column
In this example, the list in column B contains duplicate product names. Follow these steps to remove the duplicates:
Only the first occurrences for each value in the list are left, and the duplicate rows are gone.
Remove Duplicates - Multi Column
In this example, there is a named Excel table on the worksheet, with 4 columns.
Follow these steps to remove the duplicates from the Excel spreadsheet, based on the values in one or more of the columns:
Remove Duplicates - Advanced Filter
Another way to create a list without duplicate items is touse an Excel Advanced Filter. With this method, you can send a list of unique items to a different worksheet, and leave the original list unchanged.
This video shows the steps, and there are written instructions on the Advanced Filter page.
Remove Duplicates - How It Works
Here are a few questions about how the Remove Duplicates feature works, and answers to those questions.
Are Number and Text Numbers Removed as Duplicates?
No. The Remove Duplicates feature does NOT see text strings and numbers as duplicates.
For example, if the list has a 10 (real number) and a '10 (text number), they will NOT be treated as duplicates. Both values will remain in the list after you run the Remove Duplicates command.
Are Extra Space Characters Ignored?
No. The Remove Duplicates feature does NOT ignore extra space characters.
For example, if the list has "Pen" (no trailing space character) and "Pen " (with trailing space character), those items will NOT be treated as duplicates.
Both values will remain in the list after you run the Remove Duplicates command.
Which Duplicate Item is Kept?
The first instance of each item is kept, and all subsequent duplicate items are deleted.
So, if you want to keep the latest entries in a multi-column list, sort the list by a date field, or by another column that indicates the newest items.
For example, this list was sorted by date, in descending order. The duplicate product names will be deleted, and only the latest entry for each product will remain in the list.
Remove Duplicates Number Problem
After you use the Remove Duplicates command, you might see duplicate numbers that remained in the list. This does NOT mean that the Remove Duplicates feature is broken, or untrustworthy.
In the following sections, see:
Duplicate Number Problem Example
To see an example of the duplicate number problem, get the Number Problems workbook, in the sample files section below.
After using the Remove Duplicates command on a 3-column product list, there are still duplicate entries for some items. For example, this item is listed twice in the screen shot below:
Formulas See Values As Equal
Even though the Remove Duplicates features sees a difference in the numbers, worksheet formulas see the values as equal.
In this screen shot, formulas compare the values in E3 and E4, and none of the formulas detect a difference between the amounts in those cells.
Examine Worksheet XML
To see the hidden difference between the two numbers, you can dig deep into the workbook, and examine the XML code for a worksheet. Use the sample file below, to see this simple example.
To see the XML code, where you can compare the hidden values, follow these steps:
On the worksheet, Excel is limited to 15 digits of precision, so that 2 is not included.
Because the 2 is ignored, the values in A1 and B1 are seen as equal.
NOTE: You can see a detailed explanation of the floating point precision used in Excel on the Microsoft site.
How to Fix Duplicate Number Problem
To fix the problem, you can use the ROUND function, to reduce all the numbers to a set number of decimals.
NOTE: This is not an ideal solution, but will prevent differences in the hidden numbers stored in Excel.
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:
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
Use Fixed Numbers
You could use the column of fixed numbers in your future calculations, and keep the original numbers too.
Or, if you prefer, follow these steps to replace the original numbers with the rounded numbers.
Remove Duplicates: To see the Remove Duplicates examples from this page, download the Excel Remove Duplicates workbook. The zipped file is in xlsx format, and does not contain any macros.
Last updated: November 20, 2023 10:48 AM