Highlight Duplicate Values With Color

Excel & Google Sheets

How it works

This formula doesn't go in a cell — it goes inside a conditional formatting rule, where it's evaluated once for every selected cell and colors the ones that return TRUE. COUNTIF($A$2:$A$20,A2) counts how many times each cell's value appears in the whole range; a count above 1 means duplicate, so every copy gets highlighted. In Excel: select A2:A20, then Home > Conditional Formatting > New Rule > "Use a formula to determine which cells to format", paste the formula, and pick a fill color. In Google Sheets: select A2:A20, then Format > Conditional formatting, set the condition to "Custom formula is", and paste the same formula. Write the formula from the perspective of the top-left cell of your selection (A2 here) — both apps adjust it automatically for every other cell.

$A$2:$A$20

The full range to count within. The $ signs lock it so every cell counts against the same range.

A2

The top-left cell of your selection, left relative so each cell checks its own value.

>1

TRUE when the value appears more than once — that's what triggers the highlight.

When to use it

Use this on any column where repeats mean trouble: invoice numbers, employee IDs, email lists, booking references. The color updates live, so new duplicates light up the moment they're typed.

Common mistakes

• ✕Locking the checked cell too: =COUNTIF($A$2:$A$20,$A$2)>1.

  ✓With $A$2 fully locked, every cell counts the first cell's value, so either everything highlights or nothing does. The range gets $ signs; the checked cell stays relative: =COUNTIF($A$2:$A$20,A2)>1.

• ✕Writing the formula for the wrong starting cell.

  ✓The relative reference must match the top-left cell of the selected range. If your selection starts at A5, the rule is =COUNTIF($A$5:$A$23,A5)>1 — otherwise every highlight is shifted by a few rows.

• ✕Pasting the formula into a cell instead of the rule box.

  ✓In a worksheet cell it just shows TRUE or FALSE. Open the conditional formatting dialog (Home > Conditional Formatting in Excel, Format > Conditional formatting in Sheets) and paste it there.

• ✕Using a whole-column range and slowing the sheet down.

  ✓=COUNTIF(A:A,A2)>1 forces a full-column count for every cell and can make large sheets crawl, especially in Google Sheets. Limit the range to your data: $A$2:$A$20.