Question: How do I highlight duplicate values in a range (A1:D4) using conditional formatting?
Conditional formatting formula:
Highlight all values having duplicates
What is named ranges?
How to implement conditional formatting formula to your workbook in excel 2007
For example, your range is D4:G9. Change named range tbl to D4:G9.
- Select your range D4:G9.
- Go to "Home" tab
- Click Conditional formatting
- Click "New Rule.."
- Click "Use a formula to determine what cells to format"
- Change A1 in the above conditional formatting formula to D4. Your range is D4:G9, remember?.
- Copy the above conditional formatting formula to "Format values where this formula is true:"
- Click Format button
- Select a formatting you like. For example, cells filled with yellow.
- Click OK
- Click OK
Download excel *.xlsx file
Functions in this article
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE
returns the row number of a reference
Counts the number of cells within a range that meet the given condition
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range
Returns the largest value in a set of values. Ignores logical values and text.
Returns the relative position of an item in an array that matches a specified value
COLUMN(reference) Returns the column number of a reference
Returns the smallest number in a set of values. Ignores logical values and text