Problem
How do I highlight all of the duplicate values in a cell range?
Background
You can use Excel to easily identify duplicate values in a range. We will look at two simple methods. Download the example file here and try it out for yourself.
-
METHOD 1: Automatically highlight all of the duplicate values in a range (using conditional formatting)
-
METHOD 2: Identify the duplicates in a range, using a formula that shows the number of times each item appears in the range (using the COUNTIF function). All items with a count greater than 1, are duplicates.
Each solution has some advantages and some disadvantages, so check out both, then use the one that works for you.
Solution
Highlight Duplicates Using Conditional Formatting
Example 1
In the example below, all employees are getting brand new e-mail addresses, made up of the first four letters of their name and of their surname.
But before we tell the IT department to go ahead and create the new addresses, we need to check (automatically) that there are no duplicates. So let’s explore a way to highlight the duplicates in a range, using Conditional Formatting.
To do this, first select all of the e-mail addresses in column D, then on the Home tab click on Conditional Formatting, then on Highlight Cell Rules, then on Duplicate Values…
In the dialog box that pops up, indicate how you want the duplicates to be formatted. The default formatting is Light Red Fill with Dark Red Text:
All duplicates are now highlighted using the selected formatting:
But we want to be able to quickly select only the duplicates. To do this we will use auto-filter.
Click somewhere within the dataset, then on the Data ribbon, click Filter:
There will now be little twisties in the bottom right corner of each header in the data range. Click on the Email twisty, then select Filter by Color, then select the light red cell colour:
We now have a list of all the duplicates in the range:
We can then go ahead and manually change one version of each duplicate pair. You will see the shading disappear once we fix an entry and remove a duplicate:
Identify Duplicates by Counting The Number Of Instances (using COUNTA function)
Example 2
In the example below, we want to identify whether there are any employees that use the same bank account number:
There might be a perfectly valid reason for two people having the same bank details, e.g. a husband and wife using the same bank account. But often it can indicate fraud, e.g. fake employees were created by someone in the Payroll Department, in order to collect fraudulent salary payments.
We will check for duplicates by creating a formula using COUNTIF, in column I. In cell I2 we create the following formula:
=COUNTIF($G$2:$G$40, G2)
What does this formula do? It says “how many times does the value in cell G2 (being 555-127), show up in the range G2 to G40?
The result is “1” as there is only one instance of that bank account in the range G2 to G40.
Because we added dollar symbols to the range ($G$2:$G$40), it will remain unchanged if we copy the formula down.
If we copy the formula in cell I2 down to cell I40:
We can already see there are a few records where the same bank details show up more than once, e.g. the item with count “4” in the image above.
Once again, we can filter to only show the duplicates. Click somewhere in the dataset, and on the Data ribbon, select Filter:
Click on the twisty next to Count, and untick the “1” (i.e. only show the values that show up more than once), then click OK:
We can now see that the Human Resources Manager has the same bank details as three other employees, being three Sales Representatives, with consecutive employee numbers, each with a salary of exactly $50,000. Could it be legit? Possibly, but definitely worth investigating…
Conclusion
Method 1 is slightly quicker to apply, as you just select the option from the Conditional Formatting menu and you’re done. But if you apply it to a long list of entries (many thousands) the Conditional Formatting can take a while to run, especially when you then filter on a colour.
Method 2 is a relatively simple formula that can be quickly applied to thousands of rows, but it takes up an extra column in your worksheet, so it will take a bit of extra planning.
Download the completed example file here.
Do you know of any other methods? Let us know in the comments!
Time is Money
Haven’t discovered the wonders of PivotTables yet? Then it’s time you check out the 10 Steps to PivotTable Success. Make your life easier! Stop wasting time on repetitive analysis tasks that can be automated in a flash.