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:

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:

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.

Word forms and 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.

This post is also available in RU.