Remove Duplicates in Excel: how it works

With Data > Remove Duplicates you delete rows that appear more than once in your list. You choose which columns Excel compares.

Try the first exercises for freeIn the lesson Cleaning up data with the ribbon

What does Remove Duplicates do?

In a list that comes from different sources, the same rows often appear twice. With Remove Duplicates, Excel keeps only the first of every row that appears more than once, and moves the rest up. Afterwards, Excel tells you how many rows it removed.

You choose which columns Excel compares. With all the columns, only rows that are completely identical disappear. With one column, such as an email address, one row per address is left. The button is on the Data tab, and also on Table Design if you're working in an Excel table.

When do you remove duplicates?

A membership list in which some people have signed up twice.
Two exports that you pasted one under the other, with rows that are in both.
A list of email addresses to which you want to send one message per address.
A list of all the different regions or products, from a copy of your data.

Removing duplicate rows, step by step

This is how to go about it:

First make a copy of your sheet if you want to keep the original list.
Click in your list, or select the range Excel should search.
Choose Data > Remove Duplicates. Tick My data has headers if your list has a header row.
Tick the columns that have to match, and click OK.
Excel tells you how many duplicate values it removed and how many unique values are left.

Where it often goes wrong

The duplicate rows are really gone, not hidden. Work on a copy, or check first with Conditional Formatting > Highlight Cells Rules > Duplicate Values.
Leeds and Leeds with a space after it are different to Excel. Remove extra spaces first.

Want to try it yourself?
Practise this in the lesson Cleaning up data with the ribbon. The first exercises are free.

Try the first exercises for free

Frequently asked questions

Which row does Excel keep?

Always the first one. The rows below it with the same values disappear. If you want a list without duplicates that keeps up, use the UNIQUE function.