Logo of Excel

Part 1 of our Excel Guides series.  Read Part 2Part 3 and Part 4 here.

We get a lot of calls for quick help on Excel and there are a number of regular questions. One in particular is identifying and getting rid of duplicate data.

This will take you through a step-by-step guide of how to get rid of duplicate data in excel.

Step One

Highlight the cells that you want to check for duplicates. After selecting the data that you wish to check for duplicates, navigate through this menu; Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.

Screenshot of Excel

You can then select from a drop-down box how you would like the information highlighted; in this example I have highlighted the duplicate data in green.

Screenshot of Excel

This will highlight all of the information that is repeated for you.

Step Two

Click on the Data tab > Remove Duplicates

Screenshot of Excel

The system will tell you how many rows have been found as duplicates and how many would remain.

If you were just interested in keeping one row for each, say, email address, then this works perfectly.

Unfortunately, this is not always the case and we often find that each occurrence of the duplicated data (e.g. email) has different data against it, which then needs to be merged or prioritised.