US Trends

how to highlight duplicates in excel

To highlight duplicates in Excel, you normally use Conditional Formatting , and you can go from super simple (one column) to more advanced (across rows/columns). Here’s a practical, slightly story-like walkthrough you can follow.

Basic: Highlight duplicates in one column

Imagine you have a list of emails in column A and you just want any repeated email to be colored.

  1. Select the range, e.g. A2:A100.
  2. Go to Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values.
  3. In the dialog:
    • Make sure “Duplicate” is selected in the dropdown.
    • Pick a format (e.g., Light Red Fill with Dark Red Text).
  4. Click OK.

Now every value that appears more than once in that range is highlighted.

Only highlight the second (and later) duplicates

Sometimes you want the first occurrence to stay normal and only highlight repeats (useful for data entry checking).

  1. Select the range, e.g. A2:A100.
  2. Go to Home → Conditional Formatting → New Rule.
  3. Choose “Use a formula to determine which cells to format”.
  4. Use a formula like:
    • Highlight 2nd and all later duplicates (starting at row 2):

=COUNTIF($A$2:$A2,$A2)>1

  1. Click Format , choose your color, then OK → OK.

You can adjust the >=3 pattern if you want to start highlighting from the 3rd repeat and beyond:

  • 3rd and later:

=COUNTIF($A$2:$A2,$A2)>=3

Highlight duplicates across multiple columns (entire range)

Say you’ve got data in A2:C8 and want to highlight any value that appears more than once in that entire block.

  1. Select the whole range A2:C8.
  2. Home → Conditional Formatting → New Rule.
  3. Pick “Use a formula to determine which cells to format”.
  4. Enter:

=COUNTIF($A$2:$C$8,A2)>1

  1. Set the format and confirm.

Key idea: the range ($A$2:$C$8) is absolute, but the first cell A2 is relative so Excel can adjust it for each cell in the range.

Highlight duplicate rows (same combination of values)

Suppose a row is “duplicate” if both columns A and B match an earlier row (e.g., same customer + same date).

  1. Select the rows you want, for example A2:B100 (or whole rows A2:D100 if you want to color the entire row).
  2. Home → Conditional Formatting → New Rule → Use a formula…
  3. Use a formula with COUNTIFS, like:
    • Highlight duplicate rows except the first occurrence:

=COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2)>1

  1. Set a fill color and click OK.

This checks how many times that specific combination of A and B has appeared up to this row , and highlights when the count is more than 1.

Highlight duplicates between two columns (side‑by‑side)

If you just want to highlight where A1 equals B1 (and so on down the sheet):

  1. Select the full area you care about, e.g. A1:B100.
  2. Home → Conditional Formatting → New Rule → Use a formula…
  3. Use:

=AND($A1=$B1,$A1*$B1<>0)

  1. Pick your format and apply.

This highlights only where both cells in a row match and are not zero/blank (because the product is non‑zero).

HTML table: Quick cheat‑sheet

Here’s a compact reference in HTML (you can paste into a site editor if needed):

html

<table border="1" cellpadding="6" cellspacing="0">
  <thead>
    <tr>
      <th>Goal</th>
      <th>Range to select</th>
      <th>Rule type</th>
      <th>Formula / Menu</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>Highlight all duplicates in one column</td>
      <td>A2:A100</td>
      <td>Built‑in</td>
      <td>Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values → “Duplicate”</td>
    </tr>
    <tr>
      <td>Highlight 2nd and later duplicates (first stays normal)</td>
      <td>A2:A100</td>
      <td>Formula</td>
      <td>=COUNTIF($A$2:$A2,$A2)&gt;1</td>
    </tr>
    <tr>
      <td>Highlight 3rd and later duplicates</td>
      <td>A2:A100</td>
      <td>Formula</td>
      <td>=COUNTIF($A$2:$A2,$A2)&gt;=3</td>
    </tr>
    <tr>
      <td>Highlight duplicates in a block of cells</td>
      <td>A2:C8</td>
      <td>Formula</td>
      <td>=COUNTIF($A$2:$C$8,A2)&gt;1</td>
    </tr>
    <tr>
      <td>Highlight duplicate rows by columns A &amp; B</td>
      <td>A2:B100 (or full row)</td>
      <td>Formula</td>
      <td>=COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2)&gt;1</td>
    </tr>
    <tr>
      <td>Highlight where A and B match on same row</td>
      <td>A1:B100</td>
      <td>Formula</td>
      <td>=AND($A1=$B1,$A1*$B1&lt;&gt;0)</td>
    </tr>
  </tbody>
</table>

Tiny “story” example

Think of a growing signup list where multiple people accidentally register twice with the same email. By adding a conditional formatting rule like =COUNTIF($B$2:$B2,$B2)>1 on your Email column, every new repeated email instantly lights up, so you can stop duplicates the moment they’re typed instead of cleaning them up weeks later.

SEO‑friendly meta description

How to highlight duplicates in Excel using Conditional Formatting, formulas like COUNTIF and COUNTIFS, and multi‑column rules. Learn quick methods to catch repeated values, rows, and cross‑column matches.