Excel 2013: Find Duplicate Data Using Conditional Formatting

Finding duplicate data within an Excel worksheet is a difficult task to attempt to do manually. Conditional formatting techniques can highlight this duplicated data to facilitate easier removal.

Excel documents can get notoriously huge. Often this is due to duplicated and repeated data. Finding and removing this data can be mind-numbingly boring and painful. Some research secretaries have entire careers of just staring at the data in Excel trying to discover and remove duplicate row entries. Accidental repeated imports are common culprits in this situation.

This tech-recipe tutorial will introduce you to removing duplicate data through conditional formatting. Instead of having to manually scan for data, these steps will show you how to isolate your duplicated entries.

These techniques can be expanded to a wide range of data; however, we will start with the simple case of duplicated entries in a list.

1.Highlight the list which you are going to search for duplicates. 

2.From the Excel ribbon select Conditional Formatting, then choose Highlight Cells Rules, and finally click on Duplicate Values. 

3.Under Duplicate Values select the format that you like best. For this tutorial I chose Red Border. After you have adjusted your settings click OK

Now your duplicate entries will be highlighted. This highlighted data can be examined to see if it should be removed.

In a research environment, for example, this is one of the reasons for using unique IDs for samples. If these unique IDs are highlighted suggesting duplication, then the redundant data can be identified and removed.

 

About Alex Bahdanovich

Writer, author, wordsmith -- this tech enthusiast enjoys Starbucks, good reads, and golden retrievers.
View more articles by Alex Bahdanovich

The Conversation

Follow the reactions below and share your own thoughts.

  • Brittany

    This is helpful, but I have a question: my data has last names in column A and first names in column B. When I follow your steps, it looks for duplicates of either first or last. I need it to high light people with the same first AND last. So for example it currently highlights John Williams, John Smith, Andrew Smith, John Smith and Alexander Smith. But I only need the exact same names highlighted – so both John Smiths but not the other people. Short of combining the Last and First columns into one column somehow, how could I do this?

  • Tom

    This puts a border around every SINGLE cell, how do I make it put the bordering around a group of duplicates. For example, if the above image had “MILK” in A1, A2 and A3 I’d like the border to be around the outer perimeter of all 3 cells as a group of duplicates. This would be extremely helpful when working with Part #’s.
    Sometimes I might have “23545″ in A1, A2 and A3 then “23577″ in A4, A5 and A6 so to group those 2 sets of duplicates would be extremely helpful – ANYONE KNOW HOW? Perhaps using a special function within the formula? Or shoot, I’ll take normal VBA if anyone knows? THANKS SO MUCH!