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 duplicates where adjacent cell value meets criteria using conditional formatting in excel
- Highlight duplicates where an adjacent column is in a date interval using conditional formatting in excel
- Highlight common values in two lists using conditional formatting in excel
- Highlight dates within a date range using conditional formatting
- Highlight smallest duplicate value in a column using conditional formatting in excel
- Prevent duplicates using dynamic conditional formatting in excel
- Highlight duplicates where adjacent cell value meets criteria using conditional formatting in excel part 2
- Highlight duplicate values in a range using conditional formatting in excel
- Highlight unique values and unique distinct values in a range using conditional formatting in excel



Leave a Reply