Excel - Find Duplicates

Introduction

I had a table and I knew that some of the rows were duplicates. Excel 2013 (and 2010) have an option on the Data menu to Remove Duplicates. But I did not want to do that because I did not know which of the duplicates I wanted to keep. In this article I'll explain how to highlight duplicates and sort them to easily decide which ones you want to keep. My example is using Excel 2013 but it should also work fine with Excel 2010.

Highlight Duplicates

Here's a table of data, I know there are some duplicates in the Town column:

Select the column where you think the duplicates are, in my case the Town column - as shown above.

Click Conditional Formatting

Click Duplicate Values - the following box will appear...

Click OK

In the above you can see the duplicate rows are highlighted! 

Sort the Duplicates

The above is quick and easy but some of the duplicates are not together (Ilminster for example) and you may have hundreds or thousands of rows of data with lots of duplicates. To make your life easier you can sort the highlighted duplicates so they come to the top. Here's how:

Click Sort & Filter 

Click Custom Sort 

Sort by = select the column where the duplicates are (Town in my example)

Sort on = Cell Color

Order = the red you used for the highlight should already be selected

The last box says On Top, this is where the sorted results will be.

Click OK 

Conclusion

Quick and easy to do, it should help you work with large data-sets to strip out unwanted rows.

Reference

Microsoft page explaining about highlighting duplicates:

http://office.microsoft.com/en-001/excel-help/find-duplicate-values-fast-by-applying-conditional-formatting-HA102809491.aspx [accessed 25/08/2014]