Forum Discussion
Conditional formatting to highlight duplicates across two columns
Hi, I'm looking for some help to highlight duplicate data across multiple columns. For example I wish to use conditional formatting or similar to show me at a glance if I have multiples of John Smith or any other name
In this example:
| A | B |
| 1 | John | Smith |
| 2 | Gordon | Low |
| 3 | Derek | Reilly |
| 4 | John | Smith |
| 5 | Lesley | Nicoll |
| 6 | Derek | Reilly |
I would want the formatting to then highlight the cells across columns A and B containing Derek&Reilly as well as John&Smith as these are duplicates. If this makes sense? I want to retain first name and surname in separate columns for this process.
Thank you.
Joshua65 Start by selecting the entire data range of both columns, from A1 to B6 in this example. On the Ribbon, go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format and use the following formula:
=COUNTIFS($A$1:$A$6, $A1, $B$1:$B$6, $B1)>1Take note of the absolute cell references for criteria_range1 and criteria_range2, while both criteria cells are using a mixed reference of absolute columns and relative rows.
Then choose your desired cell formatting and click OK. The results should look something like this:
Conditional Formatting Duplicates in Multiple Columns
- Thanks, this is exactly what I was looking for 🙂
=IF(COUNTIFS($A$1:$A$6,A1,$B$1:$B$6,B1)>1,"Duplicate","")
A similar result can be returned with this formula.
