Question: How do I highlight duplicate values in a range (A1:D4) using conditional formatting?

Answer:

Conditional formatting formula:

=IF(ROW(A1)>MIN(ROW(tbl)), COUNTIF(OFFSET(tbl, 0, 0, MIN(ROW(A1)-MIN(ROW(tbl))), MAX(COLUMN(tbl))), A1)+COUNTIF(OFFSET(tbl, MIN(ROW(A1)-MIN(ROW(tbl))), 0, 1, MIN(COLUMN(A1))), A1), COUNTIF(OFFSET(tbl, 0, 0, 1, MIN(COLUMN(A1))), A1))>1

Named ranges
tbl (A1:D4)
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.

1. Select your range D4:G9.
2. Go to "Home" tab
3. Click Conditional formatting
4. Click "New Rule.."
5. Click "Use a formula to determine what cells to format"
6. Change A1 in the above conditional formatting formula to D4. Your range is D4:G9, remember?.
7. Copy the above conditional formatting formula to "Format values where this formula is true:"
8. Click Format button
9. Select a formatting you like. For example, cells filled with yellow.
10. Click OK
11. Click OK

Download excel example file.
highlight-duplicate-values-in-a-range.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article

IF(logical_test,[value_if:true],[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

ROW(reference)
returns the row number of a reference

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

MAX(number1,[number2],)
Returns the largest value in a set of values. Ignores logical values and text.

MATCH(lookup_value;lookup_array; [match_type]
Returns the relative position of an item in an array that matches a specified value

COLUMN(reference) Returns the column number of a reference

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

Related posts:

1. Highlight duplicates where adjacent cell value meets criteria using conditional formatting in excel part 2
2. Highlight odd/even months using conditional formatting in excel 2007
3. Highlight overlapping date ranges using conditional formatting in excel
4. Highlight duplicates on same date, week or month using conditional formatting in excel
5. Filter duplicate values in a range using “contain” condition in excel
6. Extract duplicate text values from a range containing both numerical and text values in excel