Fix: Excel Remove Duplicates Not Working (3 Solutions)

Get FREE Advanced Excel Exercises with 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.


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.


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


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


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 Both Duplicates in Excel


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 a 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 Duplicate Names in Excel


Download Practice Workbook

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


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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

2 Comments
  1. There’s one more you didn’t account for. I took me a while to figure out that not all “spaces” are created equal. Excel looks at ASCII character 160 as different than character 32. To anyone else, they both look the same.

    • Thank you very much for your valuable comment. You are right. The ASCII character 160 is different than character 32. If you have any cell containing the ASCII character 160 non-breaking space then the TRIM function described in method 1 won’t work.
      However, you can use the combination of the SUBSTITUTE and CHAR functions then. Suppose you have a value in cell A1 of your dataset containing the ASCII character 160 non-breaking space. Then you can use the following formula to remove unwanted spaces-

      =SUBSTITUTE(A1,CHAR(160), “”)

      After using this formula you can apply the Remove Duplicates button.

      Regards
      Sajid Ahmed
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo