Is there an easy way to locate and highlight duplicates in a list in Excel?
If you just want to remove the duplicates, the easiest way is to use the Advanced Filter or the built-in Remove Duplicates feature on the Data ribbon, but what if you want to find the duplicates in the list, keep them and highlight them with a different colour? That almost sounds like a job for a professional Excel consultant, but there’s no need for that – you can easily do it yourself! I’ll show you one easy way and one super-easy way:
First, the super-easy way:
Select the cells you want to check, go to the Home Ribbon, choose Conditional Formatting and select Highlight Cell Rules > Duplicate Values.
Then, the easy way
If you only want to locate duplicates, the super-easy way above is the right way to do it. But sometimes we want to make it more dynamic: If we want to be able to choose between highlightning duplicates, triplicates or quadruplicates, i.e. two, three or four occurrences of the same piece of data, we need another approach: Conditional Formatting with a formula.
First, let’s find out how to count the number of occurences in a list. In my example below I have 27 rows of data, with names in the range A2 to A28. In A2 we find the name Robert, so if we want to find out how many times Robert appears in the list, we can use this formula: =COUNTIF($A$2:$A$28,A2). In this example the formula will return 3.
We will use almost the same formula in Conditional Formatting: As you might already know, Conditional Formatting uses Boolean logic, which means that it checks whether or not a statement is TRUE, and formats the cells that return TRUE. First, let’s see how our formula works when we put it in the worksheet. We use the same formula as above, only with “=1”, “=2” or “=3” in the end, and we will get TRUE or FALSE for each statement. In this example, for Robert, the statement is true for “=3”.
So, let’s put the formula into Conditional Formatting, with one small adjustment: Instead of hard-coding the value after the equal sign (1,2,3 etc.) we’ll use a cell reference. I will have my reference in cell E2.
Select the cells you want to include in the search (A2:A28 in this example), go to the Home Ribbon and choose Conditional Formatting > New Rule. Select “Use a formula to determine which cells to format” and type this formula into the formula field:
Note that the range A2:A28 and the reference to E2 (number of occurrences) have to be locked with dollar signs (shortcut: F4). The criterion, A2, must remain unlocked.
The result: All the triplicates are highlighted. If you change the value in E2 to 2, you will get the duplicates instead, and if you change it to 1, all the unique values will be highlighted.
If you want to highlight all the recurrences of a value (both duplicates, triplicates, quadruplicates etc.), you can use this formula instead:
More easy tricks with Conditional Formatting:
- Create a Search Field in Excel in 5 minutes
- Highlight an Entire Row in Excel Based on One Cell Value
- The Easiest Way to Hide Zeros in Excel
- Hide Future Dates in Excel with Conditional Formatting
Are you using a non-English version of Excel? Click here for translations of the 140 most common functions in 17 different languages: