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

Answer:

Conditional formatting formula:

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

Highlight all values having duplicates

=COUNTIF(tbl, A1)>1

Named ranges
tbl (G5:J8)
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 *.xlsx file
highlight-duplicate-values-in-a-range1.xlsx

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:

Highlight smallest duplicate value in a column using conditional formatting in excel

Highlight unique values and unique distinct values in a range using conditional formatting in excel

Highlight duplicate values in two ranges combined using conditional formatting in excel

Highlight dates within a date range using conditional formatting

Highlight duplicates using conditional formatting in excel