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.

By Removing Extra Spaces Using Trim Function to Fix Remove Duplicates Not Working Problem

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.

By Removing Extra Spaces Using Trim Function to Fix Remove Duplicates Not Working Problem

Solution:

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

By Removing Extra Spaces Using Trim Function to Fix Remove Duplicates Not Working Problem

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

By Removing Extra Spaces Using Trim Function to Fix Remove Duplicates Not Working Problem

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.

By Converting Multiple Rows/Columns to Single Rows/Columns to Fix Remove Duplicates Not Working Problem

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’.

By Converting Multiple Rows/Columns to Single Rows/Columns to Fix Remove Duplicates Not Working Problem

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

By Converting Multiple Rows/Columns to Single Rows/Columns to Fix Remove Duplicates Not Working Problem

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


Similar Readings


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?

Use Round Function to Fix Duplicate Number Problem

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.

Use Round Function to Fix Duplicate Number Problem

Solution:

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

Use Round Function to Fix Duplicate Number Problem

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

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo