Highlight the second or more duplicates in two lists using conditional formatting in excel
Question: I need to delete duplicates in two different columns together. How do I highlight the second or more duplicates and not the "original" value? This makes it a no-brainer to select and delete duplicate cell values using CTRL + Left mouse button.
Answer: See picture below.
Conditional formatting formula A2:A6: =COUNTIF($A$2:$A2, $A2)>1
Conditional formatting formula B2:B6: =OR(COUNTIF(A2:A6,$B2)>0, COUNTIF($B$2:$B2,$B2)>1)
Customizing excel formulas to your workbook
If your first list starts at C3 and ends at C7, the formula would be =COUNTIF($C$3:$C3, $C3)>1. If the second list starts at F3 ,the conditional formatting formula would be =OR(COUNTIF(C3:C7,$F3)>0, COUNTIF($F$3:$F3,$F3)>1)
How to apply the conditional formatting formulas in excel 2007:
- Select the first list range
- Click "Home" tab on the ribbon
- Click "Conditional formatting"
- Click "New rule..."
- Click "Use a formula to determine which cells to format"
- Click "Format values where this formula is true" window.
- Type =COUNTIF($A$2:$A2, $A2)>1
- Click Format button
- Click "Fill" tab
- Select a color
- Click OK!
- Click OK!
- Repeat 1-12 with the second list range and second conditional formatting formula
Download excel example file
highlight-second-or-more-duplicates-in-two-lists-using-conditional-formatting-in-excel.xls
(Excel 97-2003 Workbook *.xls)
Functions used in this blog post:
COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition
OR(logical1, logical2, ...)
Checks whether any argument are TRUE and returns TRUE or FALSE. Returns FALSE
Related posts:
Highlight duplicates using conditional formatting in excel
Highlight common values in two lists using conditional formatting in excel
Highlight duplicates where adjacent cell value meets criteria using conditional formatting in excel
Recommended categories
