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 smallest duplicate value in a column using conditional formatting in excel
2. Highlight unique values and unique distinct values in a range using conditional formatting in excel
3. Highlight dates within a date range using conditional formatting
4. Highlight duplicates using conditional formatting in excel
5. Highlight duplicate values in two ranges combined using conditional formatting in excel
6. Highlight the second or more duplicates in two lists using conditional formatting in excel
7. Highlight common values in two lists using conditional formatting in excel
8. Highlight duplicates where adjacent cell value meets criteria using conditional formatting in excel
9. Highlight unique distinct values in two ranges combined using conditional formatting in excel
10. Highlight duplicates where adjacent cell value meets criteria using conditional formatting in excel part 2