Fix: Excel Remove Duplicates Not Working (3 Solutions)

Trying to remove duplicates and it’s not working in your Excel worksheet? This article will explain the possible reasons behind the issue and will provide you with 3 quick solutions to fix this.

Download Practice Workbook

You can download the free Excel template from here and practice on your own.

The 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. Keep your data in the same format if you want to avoid this.

• Trailing/ Leading Spaces:

“Head”, “ Head ”, and “Head “ are the same? Excel says, no. Excel considers the extra trailing or leading spaces and will count these three words as three unique cell contents.  So make sure while typing that there is no extra trailing or leading spaces in your values.

• Different Formats:

If you type 672 in Text format and again type it as Number format in another cell then Excel will accept it as a unique value. So always keep your data in the same format before removing duplicates.

• Incorrect Ranges:

When you are working with a large dataset then it is quite possible to make mistakes while selecting the data range. So be sure that you have selected your data range 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. So give the $ sign in the same for a row or column.

3 Fixes to Remove-Duplicates-Not-Working Issue in Excel

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

To explore this method, I have placed some salespersons’ selling regions in my dataset. Have a look that some regions are common. But if I try to remove the duplicate regions using the Remove Duplicates command from the Data ribbon then I am getting this unexpected result.

After a while I got the reason, Unfortunately, I typed extra space after some regions’ names and that’s why the Remove Duplicates command was not working. Because Excel doesn’t accept it as duplicate if extra space exists.

Solution:

To overcome this trouble I have used the TRIM function in Cell D5 after adding another column.

Then dragged down the Fill Handle icon to copy the formula.

Now all values are extracted without extra spaces.

Then I tried the Remove Duplicates command and it worked properly as shown in the image below.

Read More: Remove Duplicate Rows Except for 1st Occurrence in Excel (7 Ways)

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

Another major reason for Remove Duplicates not working properly is that the Remove Duplicates command doesn’t work for multiple rows/columns. It is just made for a single row or column. Take a look at the image below that I tried for multiple columns and that’s why Excel got no duplicates although there are remaining multiple duplicates in my dataset.

Solution:

We can easily handle this problem smartly. Just copy the values of the two columns’ data in one column by using copy-paste option. I have added it in the new column named ‘Combined’.

Now try the Remove Duplicates command and get the output without any error.

Read More: How to Remove Duplicate Rows Based on One Column in Excel

Similar Readings

• How to Remove Duplicate Names in Excel (7 Simple Methods)
• Excel Formula to Automatically Remove Duplicates (3 Quick Methods)
• How to Remove Duplicates in Excel Using VBA (3 Quick Methods)
• Excel VBA: Remove Duplicates Comparing Multiple Columns (3 Examples)
• How to Delete Duplicates in Excel but Keep One (7 Methods)

Solution 3: Use Round Function to Fix Duplicate Number Problem

Remove duplicates not working problem can occur because of the number of the digits after decimal in a number. In this method, I have placed some fruits’ prices in the dataset given below. When I tried the Remove Duplicates command for the price column, it showed that it got no duplicates despite being duplicate price there. What’s the problem here?

Because excel is showing only two digits after the decimal because of formatting but I have used three digits and the third digit is not the same. That’s why the Remove Duplicates command found nothing as a duplicate.

Solution:

So to fix this issue I used the ROUND function to truncate the third digit after the decimal.

Then dragged down the Fill Handle icon to copy the formula.

Now all the prices are truncated with two-digit after the decimal.

Now try the Remove Duplicates command and remove duplicate successfully.

Read More: How to Remove Both Duplicates in Excel (5 Easy Ways)

Conclusion

I hope the procedures described above will be good enough to solve the removing duplicates problem in Excel. Feel free to ask any question in the comment section and please give me feedback.

Related Articles

• How to Remove Duplicates Based on Criteria in Excel (4 Methods)
• Remove Duplicates from Column in Excel (3 Methods)
• Remove Duplicates Using VLOOKUP in Excel (2 Methods)
• How to Remove Duplicate Rows in Excel (3 Ways)
• Remove duplicate rows based on two columns in Excel [4 ways]
• How to Remove Duplicate Rows in Excel Table