Detect Duplicates in Excel Column

Do you need to quickly detect duplicates in Excel columns? Excel makes it really easy with its conditional formatting feature. It takes just a few clicks using built-in capabilities of Excel. Here is a quick overview to see first hand how to do it.

First, just highlight the values that you want to detect duplicates in. You will need to be sure to highlight the range because otherwise you’ll be trying to detect duplicates within a single cell which will not work. Also note that the matching will be done for the entire cell rather than a subset or substring within the cell.

Next, click Conditional Formatting up in the ribbon. Then click Highlight Cells Rules, then click Duplicate Values.

You can just accept the next prompt because the setting will work to highlight duplicates just fine.

Voila — the duplicate values in your list are highlighted for you.

Removing Duplicate values in Excel

Now that you’ve found a bunch of duplicate values in your column of data do you wish you had a quick and easy way to remove these values? You’re in luck. Excel also provides a Remove Duplicates function. You can get to this in the ribbon by going to the Data tab. You’ll see the Remove Duplicates button in the Data Tools section of the ribbon. Note that this will remove the duplicates from the data in the column you’re working with so be sure you actually want to remove those duplicate values!

There you go.