With the Excel Remove Duplicates feature, it's easy to remove all the duplicate items from a list, based on the items in one or more columns. You could run into problems though, if there are numbers in the list - duplicates might remain in the list.
NOTE: You can also use an Advanced Filter to create a list of unique values, and send the results to a different sheet.
This video shows the steps for removing duplicates 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.
Remember to make a backup of your Excel file, or the list, before you start.
In this example, the list in column B contains duplicate product names. Follow these steps to remove the duplicates:
In this example, there is a named Excel table on the worksheet, with 4 columns. Follow these steps to remove the duplicates, based on the values in one or more of the columns:
Here are a few questions about how the Remove Duplicates feature works, and answers to those questions.
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.
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.
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.
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:
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:
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.
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.
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
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: September 16, 2021 2:52 PM
Contextures RSS Feed