Forum Discussion
Joshua65
Nov 22, 2023Copper Contributor
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.
3 Replies
Sort By
- djclementsBronze Contributor
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)>1
Take 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
- Joshua65Copper ContributorThanks, this is exactly what I was looking for 🙂
- OliverScheurichGold Contributor
=IF(COUNTIFS($A$1:$A$6,A1,$B$1:$B$6,B1)>1,"Duplicate","")
A similar result can be returned with this formula.