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
(Excel 97-2003 Workbook *.xls)
Functions used in this blog post:
Counts the number of cells within a range that meet the given condition