How to Find & Remove Duplicate Rows in Excel

One very common use of Excel is: it is used as a tool to clean up data. Cleaning up data means:

  • Getting raw data into a worksheet
  • Manipulating data so it conforms to various requirements

In summary, data is cleaned-up so it can be properly analyzed.

Data is cleaned-up from so many different angles. We shall discuss those techniques one by one. In our this post we shall confine our analysis only in removing duplicate rows.

Remove Duplicate Rows in Excel

Your data may have duplicate rows, whatever the reasons. Most of the time, you need to eliminate the duplicate rows. In old days, removing duplicate data was a manual task- although removal works could be done with advanced techniques. But with Remove Duplicates command, removing duplication is now an easy job. Remove Duplicates command was introduced in Excel 2007.

Now, see the following image. You will find that there are some duplicate rows in the column. We shall remove the duplicate ones.

Removing duplicate rows in Excel

Removing duplicate rows. Observe the column, there are some duplicate rows.

Select any cell of column A in your sample file. Then choose Data ➪ Data Tools ➪ Remove Duplicates. The Remove Duplicates dialog box will appear, see the following image.

Removing duplicate rows in Excel

Remove Duplicates dialog box. Column A is automatically selected.

Note: If your data is in a table, you can also use Table Tools ➪ Design ➪ Tools ➪ Remove Duplicates commands to remove duplicate rows. These two commands work exactly in the same way.

The Remove Duplicates dialog box lists all the columns in your data range or table. Place check marks on the columns that you want to add in the duplicate search. Generally, you will want to add all the columns and it is the default. Click OK, and Excel clean-out the duplicate rows and a message will be showed to let you know how many duplicate rows have been removed.

Removing duplicate rows in Excel

Click OK. It tells you the number of duplicate rows has been removed.

Excel does not give you chance to change your mind. But if you want to undo your actions you can do it by clicking the Undo Button in the Quick Access toolbar or just pressing CTRL + Z.

When duplicate rows are found, the first row is kept and subsequent duplicate rows are deleted.

Note: Excel treats values as duplicates on the basis what cells are displaying, not on the basis of what cells are containing. For example, 5/12/2013 and 5 Dec 2013- these two has the same value for Excel. In the following image, you will see that they generate the same value in the formula box. You will get second date format (5 Dec 2013) if you select “Long Date” from the number format option. In same way $500.50 is different from 500.50, though the first one is just the formatted product of 500.50 value. If you want to count both 5/12/2013 and 5 Dec 2013 as duplicate value, then format the whole column at first, then find out the duplicate rows.
Removing Duplicate Rows

Long date format used. Cell contains 5/12/2013 but cell display 5 Dec 2013.

Removing Duplicate Rows

Currency format is used. Cell contains 500.5 but the cell display £500.50 due to using Currency Format

Download Working File

Download the working file from the link below:

Duplicate-rows1.xlsx

Read More…

How to Remove Duplicate Rows in Excel Table

How to Find Unique Values in Excel & Detect Duplicates


Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share here. Not only how to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned!

4 Comments
  1. Reply
    fina loren August 21, 2016 at 2:34 PM

    I used to have similar problems too, but after using “DuplicateFilesDeleter” everything was solved. Try this software and you would be glad you did,
    there are many ways in which you can sort the randomized files in the hard disk.

    • Reply
      Kawser August 21, 2016 at 3:23 PM

      Thanks Fina for sharing.
      Regards

  2. Reply
    johnraf2 September 4, 2016 at 11:05 AM

    I used to have similar problems too, but after using
    “long path tool” You can use to solve this problem.

  3. Reply
    barryk desteve September 7, 2016 at 12:22 PM

    Do not worry if you want to remove the blocked files or too long path files from your system, here I suggest a smooth way. Use “Long path tool” software and keep
    yourself cool.

    Leave a reply