Fix: Excel Remove Duplicates Not Working (3 Solutions)

Reasons Why Excel Is Not Recognizing Duplicates

• Digits After Decimal:

For example, 5 and 5.00 are the same. But MS Excel counts them as two unique numbers as their formats are not the same.

• Trailing/ Leading Spaces:

“Head”, “ Head ”, and “Head “ are the same but Excel considers the extra trailing or leading spaces and will count these three words as three unique cell contents.

• Different Formats:

If you enter 672 in Text format and enter it as Number format in another cell, Excel recognizes it as a unique value.

• Incorrect Ranges:

Excel will not recognize duplicates when the data range is not selected properly.

• Incorrect $ Signs:

If you type the $ sign before a number or set the sign by clicking the currency command from the Number section of the Excel ribbon, Excel won’t count it as duplicates.

Solution 1 – Delete Extra Spaces Using Trim Function When Remove Duplicates Command Is Not Working in Excel

In the following sample dataset, when we try to remove the duplicate regions using the Remove Duplicates command from the Data ribbon, we are not getting the required result.

The reason is due to some extra space.

Solution:

Add another column and use the TRIM function in Cell D5.

Use the Fill Handle tool for the remaining cells.

All the values are without extra spaces.

The result will be displayed as shown in the following image.

Solution 2 – Convert Multiple Rows/Columns to Single Rows/Columns When Remove Duplicates Command Is Not Working in Excel

The Remove Duplicates command doesn’t work for multiple rows/columns. It works only on a single row or column.

Solution:

Copy the data of the two columns in one column by using copy-paste option. We have added a new column,  ‘Combined’.

The Remove Duplicates command will work.

Read More: How to Remove Both Duplicates in Excel

Solution 3 – Use Round Function to Fix Duplicate Number Problem

Remove duplicates will not work if the number of digits after the decimal are not the same.

The Remove Duplicates command could not find a duplicate because the decimals are not the same.

Solution:

Used the ROUND function to remove the third digit after the decimal.

Use the Fill Handle tool for the remaining cells.

All the prices have two digits after the decimal.

The Remove Duplicates command have removed duplicates.

Read More: How to Remove Duplicate Names in Excel

Download Practice Workbook

Related Articles

• How to Remove Duplicates but Keep the First Value in Excel
• How to Delete Duplicates But Keep One Value in Excel 
• How to Undo Remove Duplicates in Excel 
• How to Hide Duplicates in Excel 
• Remove Duplicate Rows Except for 1st Occurrence in Excel
• How to Remove Duplicate Rows in Excel Table

<< Go Back to Remove Duplicates in Excel | Duplicates in Excel | Learn Excel