MS Excel Tips – Use Conditional Formatting To Find Duplicate Data

There may be many reasons to look for and eliminate duplicate data in Excel. If you use Excel as lots of people do for managing lists or tables of data you may manage a list of customers, employees or an inventory then duplicate records need ot be eliminated. Also as a database grows duplicate entries can be a problem, which in themselves can cause issues for data quality and analysis.
You also may have inherited an Excel work book or a Comma Separated Value (CSV) and are unaware of the quality of the data list. It is usually good practice to review data quality and cleanse the data. One the first stages of this cleansing process is to find and remove duplicate data entries.
One effective way to identify duplicate data in Excel is to use Conditional Formatting.
First of all you need to set the conditional formatting rules.
Select the range of cells that contain your data you want to analyse i.e your data table or list.

Select the Home Tab in Excel
Hit Conditional Formatting
Select the option New Rule to display the Conditional Formatting Dialog Box
Hit the option Use a formula to determine which cells to format
Under Format values where this formula is true type following formula
This formula will count how many values in the range A1:C3 are identical to the value in cell A1 in the first instance and appear more than once. As you have applied this formula to all cells in the data range it looks all cells in the range and applies the same formula.
Hit the Format button and select the formatting you want Excel to show if it finds duplicate data i.e if the condition specified is true.
I usually specify a colour to highlight duplicate values. Just choose whichever formatting you want to so that duplicates stand out for you.
Hit OK to finish and highlight the duplicate values specified.

Lets look at it this formula in action using an example.
Here is my data range in cell A1:C3
150 170 180
170 123 176
176 170 176
Applying my formula, with formatting to BOLD any duplicate values my result would look like the below
150 170 180
170 123 176
176 170 176
As you can see the values 170 and 176 are easily seen as duplicates in my data range.
A quick and easy way to highlight duplicate data in Excel!



Thomas E Welck a Microsoft Office expert has been working in the technology industry for the last 8 years. As a technical expert, he has written white papers, and reviews for many websites such as

Leave a Reply

Your email address will not be published. Required fields are marked *