There are several ways you can remove duplicates in your data from a spreadsheet in Excel.

Remove Duplicate Data

Time needed: 2 minutes.

To remove duplicates in Excel,

  1. With your spreadsheet open, click into (or select) your data

  2. Click on the Data tab

    data tab

  3. In the Data Tools group, choose Remove Duplicates

    Remove duplicates

  4. When the dialogue box open, make sure you select whether or not your data has headers

    Headers

  5. Choose which columns you want Excel to look for duplicate data in (it may be all of them)

    Choose columns

  6. Click OK

    OK

  7. Excel will tell you how many duplicate records were removed, and how many unique records remain

    Excel removes duplicate data

  8. Click OK

Remove Duplicates in Data Using Filters

With your spreadsheet open, click on the Data Tab, and in the Sort & Filter group, click on Advanced.

Advanced filter

The Advanced Filter dialogue box will open. Choose if you want to copy your filtered list to a new location, or filter in place.

Filter in place or copy
Choose whether to filter your original list, or copy the new filtered list to a different location

If you filter in place, make sure to tick Unique Records only.

Then click OK

OK

The filtered list will have blue row numbers

Blue row numbers

In the bottom left-hand corner you will see how many records are unique.

Unique Records

If you copy to another location, click on the arrow next to Copy To

Choose location for new list

Click on the cell where you want your new list to start

Choose location

Click on the blue down arrow to restore your dialogue box.

Restore dialogue box

Tick Unique records only.

Unique Records Only

Click OK. You new list of unique records will appear.

OK

Removing Duplicate Data Video Tutorial

I hope you enjoyed this tutorial about how to remove duplicates in Excel. For more Excel tutorials, please click here. To view our YouTube channel just click here.