Delete duplicate rows

This example shows how to remove duplicate rows from a table. In the following table you can see two duplicate rows. It’s row number 2, 9, 10 and 3, 6.

Table with duplicates

Step 1

Select the table and go to the Advanced Filter menu. You find it on Data -> Filter -> Advanced (Excel 2003 and higher).

Advanced filter in Excel 2007

Step 2

Check Unique records only and press OK.

Advanced filter - unique records only

Result:Table without duplicates

Advanced filter hide duplicate rows (6, 9, 10).

Video tutorial:

If you want to keep the original table, choose Copy to another location and in the field Copy to write  the address of the cell where to place a new table.

Note: In the Excel 2007 and lower must be a new table on the same sheet as the source table.

Advanced filter - copy to another location

 

Result:

New table without duplicates

Video tutorial:

Add a comment »3 comments to this article

  1. Can I add some conditions into this feature? I want to delete duplicate rows but not all of them.

    Reply

    • Yes you can.There is Criteria range field in the Advanced Filter window. You have to create a criteria table with each column condition. You can use operators like Greater than (>), Less than (<) or combination with Equal.

      Reply

  2. This solution shows how to remove duplicity values. How can I do it if I want only hide this duplicate? Can I do it using Excel formulas or do I need a macro? I have a big table with many equal rows and I want to have clear data without duplication.

    Reply



Copyright © All Rights Reserved · Green Hope Theme by Sivan & schiy · Proudly powered by WordPress