Remove duplicates in Excel

Removing duplicates is one of the most common operations in Excel. However, people often mean different things by “duplicates,” which is why there are many ways to remove them.

Here’s a closer look at what happens when you remove duplicates:

1. There is always an object you define as a duplicate. This can be:
   • a cell
   • a cell value (not the same thing!)
   • an entire row
2. Some people consider a duplicate to be all repeats except the first. Others include the first occurrence too (technically, it is a duplicate of the others).
3. There is also the issue of “hidden duplicates” — how should comparisons handle cases such as:
   • extra spaces in cells?
   • punctuation differences?
   • the same words in a different order?
   • different word forms, such as singular/plural or different verb tenses?

Different combinations of these definitions create many possible ways to remove duplicates. Let’s go through them in detail.

Remove duplicates within a single column

If there’s only one column, you can easily use Excel’s built-in Remove Duplicates tool. It works well if you just want to delete duplicates without worrying about the rest of the sheet:

1. Select the column.
2. On the Data tab, click Remove Duplicates.
3. Leave the column ticked and click OK. Excel reports how many duplicates it removed and how many unique values are left.

The drawback is that this tool removes either entire cells or entire rows. If you have multiple columns, deleting rows will shift the other data, which may break your layout. If that’s your case, see this article on how to remove duplicate values without shifting other data. In other words, how to remove duplicate cell values without deleting the cells themselves.

Remove duplicates based on another column

To compare two columns and find duplicate values between them, you can use a couple of built-in Excel features. See their dedicated guides:

• VLOOKUP function
• Conditional formatting

In my !SEMTools add-in, this process is made even simpler. Here’s how it works:

1. Select the column you want to clean.
2. Run DELETE → Cells → Duplicates → Compare with another range → Full match, or Ignore word order if the same words may come in another order.
3. When the add-in asks, select the range to compare with.
4. Cells found in that range are cleared, the rows stay where they were.

The problem of hidden duplicates

Data may look identical to the human eye but be different to Excel. Even one extra space means a difference of one character, so the cells won’t match. By default, Excel ignores letter case, but not spaces or punctuation.

When values differ only in punctuation or word order, they are called hidden duplicates. See this guide on how to find and remove hidden duplicates in Excel.

In !SEMTools, DELETE → Cells → Duplicates → Duplicates within range has four levels of strictness: Full match, Ignore obsolete spaces, Ignore word order, and Ignore word order and morphs. The picture at the top of the page shows all four on one list.

Duplicates with different word forms: lemmatization

Sometimes cells contain the same words, but in different forms — for example, singular/plural or different verb tenses. In these cases, you can use a process called lemmatization.

The !SEMTools add-in includes a built-in lemmatizer that converts words to their base form. This makes it possible to detect duplicates even when the words are written in different forms. See the article on lemmatization in Excel for details.