Bottom Line: Learn how to compare two worksheets for duplicate values by highlighting the cells with conditional formatting. Also highlight values in a different color when there are more than two duplicates.
Skill Level: Intermediate
Watch the Tutorial
Download the Excel File
Here are both the BEFORE and AFTER files from the tutorial.
Highlighting Duplicates Between Worksheets
Let's say you have two Excel worksheets that have overlapping data and you want to call attention to any cells that have duplicate entries. You can do so using a formula and conditional formatting.
First let's look at how to write the formula and then we will see how to apply the conditional formatting.
Use the COUNTIF Formula
The formula we'll write is going to examine a cell to see if its contents can be found in another range that we specify. If so, it will return a value of the number of times that data is found. For this process we are using the COUNTIF function.
COUNTIF has two arguments. The first is range and the second is criteria. Range is the group of cells that you want to look in to find a specific value. In my video tutorial, my range is from B2 to F1001 on the “Historical” sheet.
The criteria argument is simply the value that we are looking for. In our example, that's cell B2.
If the value that is found in cell B2 is also found in our designated range on the Historical tab, the COUNTIF function will return a number greater than zero. If it is NOT found on that sheet, it will return a zero.
With conditional formatting, we use those numbers to highlight the entries that are duplicates.
For our example, the formula looks like this:
It's important that the B2 used for the criteria argument is expressed as a relative reference, not an absolute reference. That's because when we apply the conditional formatting to our entire table, Excel will examine each cell individually to see if the criteria apply, but only if it's expressed as a relative reference (no dollar symbols in the reference).
Applying Conditional Formatting
Now that we've looked at how the formula works, let's see how the conditional formatting is applied.
First select the entire range of cells that you want the formatting applied to. In our case it is all of the phone numbers on the “Current” worksheet. On the Home tab of the ribbon, choose the Conditional Formatting drop-down menu and select New Rule.
In the New Formatting Rule window, select the option that says Use a formula to determine which cells to format. That will open a field where you can write or paste the formula that we talked about above.
Next, select the Format… button and that will open the Format Cells window, where you can select any type of formatting you wish. Change the font, border, number type, or fill with color so that your duplicates will stand out. When you hit OK, and OK again, your formatting will be applied.
Any cell that returns a value larger than zero will have the new formatting applied.
Different Formatting for Multiple Duplicates
If you'd like to highlight in a different color the entries that have more than one duplicate in the other sheet, you can simply add a new rule.
Start by reopening the Conditional Formatting Rules Manager (Home tab → Conditional Formatting → Manage Rules). We're going to select the rule we've already made and then hit Duplicate Rule. Once the rule is duplicated, select one of them and hit Edit Rule.
The only change we will make to the rule is to add “>1” to the end of the rule. Then we can select Format to choose a different color.
After hitting Apply, we see that our table now shows entries which have more than one duplicate in a different color:
If you'd like to learn more about using conditional formatting, I think you'll find these two posts useful. Check them out:
- Conditional Formatting for List of Partial Matches
- Highlight Rows Between Two Dates with Conditional Formatting in Excel
As you can imagine, there are lots of uses and ways to expand this idea of comparing two sheets and using conditional formatting to highlight those comparisons. If you have any questions or ideas along those lines, I'd love to hear them in the comments below. I hope this was helpful for you.
Have a great day!