How To Find and Remove Duplicates in Excel

Now working in Excel, you can quickly remove duplicated values, numbers when you deal with few cells. But when you deal with thousands of cells, this is not the best way and not so much easy. It is only time-consuming but also error-prone. That’s why Microsoft Excel gives you a few ways to highlight duplicates. In this tutorial, I ‘ll show you How To Find and Remove Duplicates in Excel, both the basic ones and these more complicated, as well as with the VBA code included.

Highlighting duplicate values using conditional formatting command

Conditional formatting is probably the most popular command to distinct data visually. It can be used to highlight low and high values with different type of colors. Besides that, you can find here a tool that will help you find duplicated and unique values.

Example:

First, create the following a table with names.

Select all the cells, choose conditional formatting in HOME >> Styles >> Conditional Formatting >> Highlight Cells Rules >> Duplicate Values.

When you choose this option, a new window will appear.

Here, you can choose whether you want to search for duplicate or unique values, and mark them with one of the available formats or even create your own custom format.

We will get the following result.

Another trick to delete duplicates is by using the Advanced filter.

Example:

First select the data where you want to remove duplicates.

Go to DATA >> Sort & Filter and click the Advanced button.

A new window will appear.

Now you need to copy data to another location. Let’s copy it to the next column. Choose Copy to another location and change $A$1:$A$22 to $B$1:$B$22. This will copy the cells to column B. What you also need to do is to check Unique records only.

And the result only unique values were copied.