To highlight duplicates in Excel, select the cells and go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values, then press OK. To decide yourself what counts (whole rows, only the second copy, two columns at once), use a formula rule: =COUNTIF($A$2:$A$9,A2)>1 colours every cell whose value appears more than once in A2:A9.
| A | |
|---|---|
| 1 | Customer |
| 2 | Ana |
| 3 | Ben |
| 4 | Cara |
| 5 | Ana |
| 6 | Dan |
| 7 | Ben |
| 8 | Ana |
| 9 | Eve |
Ana and Ben are highlighted in every row where they appear: A2, A3, A5, A7 and A8. The rule is shown under the grid. Change A4 to Eve and both Eve cells light up.
Highlight duplicates with the Duplicate Values rule
- Select the cells to check, for example A2:A9. Leave out the header.
- Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
- Keep Duplicate in the first box, or switch it to Unique to colour the values that appear only once.
- Pick a format (the default is Light Red Fill with Dark Red Text) and press OK.
The rule ignores upper and lower case, so ana and Ana are coloured as duplicates. It always colours every copy, the first one included, and it only compares single cells: it cannot colour a whole row or check two columns together. For those, use a formula rule.
Highlight duplicates with a formula
A formula rule colours a cell when the formula returns TRUE for it. You write the formula for the first cell of the selection, and Excel moves it to every other cell the way it moves a formula you fill down.
- Select A2:A9, with A2 as the active cell.
- Go to Home > Conditional Formatting > New Rule.
- Choose Use a formula to determine which cells to format.
- Type
=COUNTIF($A$2:$A$9,A2)>1. - Click Format, pick a fill colour on the Fill tab, and press OK twice.
The range $A$2:$A$9 has dollar signs, so every cell counts in the same list. A2 has none, so in row 5 the rule reads A5. In the sheets on this page you can click a rule under the grid, edit it, and see which cells change. More rule types are on the conditional formatting page.
Highlight only the second occurrence
To leave the first copy plain and colour only the repeats, end the counted range at the current row: =COUNTIF($A$2:A2,A2)>1. In A5 the rule becomes =COUNTIF($A$2:A5,A5)>1, and Ana is already in A2:A5 twice.
| A | |
|---|---|
| 1 | Customer |
| 2 | Ana |
| 3 | Ben |
| 4 | Cara |
| 5 | Ana |
| 6 | Dan |
| 7 | Ben |
| 8 | Ana |
| 9 | Eve |
A5, A7 and A8 are highlighted: the rows that Remove Duplicates would delete. This is the rule to use when you want to check what the button will remove before you press it.
Highlight the entire row if a value is a duplicate
Select the whole table, A2:C8, and lock the column of the key with a $ before the letter only: =COUNTIF($B$2:$B$8,$B2)>1. In C5 the rule reads $B5, so every cell of row 5 asks the same question about the email in column B.
| A | B | C | |
|---|---|---|---|
| 1 | Name | Signed up | |
| 2 | Ana | ana@mail.com | 2026-03-02 |
| 3 | Ben | ben@mail.com | 2026-03-04 |
| 4 | Ana P. | ana@mail.com | 2026-03-09 |
| 5 | Cara | cara@mail.com | 2026-03-11 |
| 6 | Dan | dan@mail.com | 2026-03-15 |
| 7 | Benjamin | ben@mail.com | 2026-03-20 |
| 8 | Eve | eve@mail.com | 2026-03-21 |
Rows 2, 3, 4 and 7 are highlighted: two people signed up twice under different names. Without the $ before B, the reference moves with the column: the cells in column B would test the dates in C, the cells in C would test the empty column D, and only column A would be coloured.
Highlight duplicate rows (two columns must match)
When a row is a duplicate only if two columns both repeat, count with COUNTIFS. Here an order is a duplicate when the same customer ordered the same product: =COUNTIFS($A$2:$A$8,$A2,$B$2:$B$8,$B2)>1.
| A | B | C | |
|---|---|---|---|
| 1 | Customer | Product | Qty |
| 2 | Ana | Apple | 3 |
| 3 | Ben | Pear | 1 |
| 4 | Ana | Pear | 2 |
| 5 | Cara | Apple | 5 |
| 6 | Ana | Apple | 3 |
| 7 | Ben | Kiwi | 4 |
| 8 | Dan | Pear | 2 |
Only rows 2 and 6 are highlighted. Ana appears three times, but only two of her orders are for apples. To colour only the second of the pair, use the growing range here too: =COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2)>1.
Highlight duplicates across two columns
To colour the names in one list that also appear in another list, count each cell in the other column. Two rules: one on A2:A7 that looks in C, one on C2:C7 that looks in A. >0 means "found at least once".
| A | B | C | |
|---|---|---|---|
| 1 | January | February | |
| 2 | Ana | Dan | |
| 3 | Ben | Fay | |
| 4 | Cara | Ana | |
| 5 | Dan | Gus | |
| 6 | Eve | Hal | |
| 7 | Fay | Ivy |
Ana, Dan and Fay are in both months, so they are highlighted in both columns. The Duplicate Values rule with both columns selected (hold Ctrl, or Cmd on a Mac, to select A2:A7 and C2:C7) gives the same colours here, but it would also colour a name that repeats inside one column. To list the matches instead of colouring them, see compare two columns.
Count the highlighted duplicates
Conditional formatting colours cells but gives you no number. The same test inside SUMPRODUCT counts them: COUNTIF(A2:A9,A2:A9) returns how many times each cell's value appears, >1 turns that into TRUE or FALSE, and -- turns those into 1 and 0.
| A | B | C | |
|---|---|---|---|
| 1 | Customer | Highlighted | |
| 2 | Ana | ||
| 3 | Ben | ||
| 4 | Cara | ||
| 5 | Ana | ||
| 6 | Dan | ||
| 7 | Ben | ||
| 8 | Ana | ||
| 9 | Eve |
Your turn: In C2, count how many cells in A2:A9 the rule highlights.
The answer is 5: three Anas and two Bens. In Excel 365 and 2021, =SUM(--(COUNTIF(A2:A9,A2:A9)>1)) gives the same; in Excel 2019 that version needs Ctrl+Shift+Enter (Cmd+Shift+Enter on a Mac), while SUMPRODUCT does not.
Why the rule highlights the wrong cells
The usual cause is a missing $. Without it, the counted range moves down with each cell: in A8 the rule =COUNTIF(A2:A9,A2)>1 becomes =COUNTIF(A8:A15,A8)>1, which looks only from row 8 down and finds one Ana.
| A | |
|---|---|
| 1 | Customer |
| 2 | Ana |
| 3 | Ben |
| 4 | Cara |
| 5 | Ana |
| 6 | Dan |
| 7 | Ben |
| 8 | Ana |
| 9 | Eve |
Only A2, A3 and A5 are highlighted, and A7 and A8 stay plain although they are duplicates. Click the rule under the grid and change it to =COUNTIF($A$2:$A$9,A2)>1: all five cells light up. The other common causes:
- The active cell was not the first cell of the selection when the rule was written. Excel reads the formula relative to the active cell, so a rule written while A9 was active is shifted by seven rows. Check it in Home > Conditional Formatting > Manage Rules, where Applies to shows the range.
- The values are not really equal:
Anawith a trailing space is a different value. Clean the data with=TRIM(A2)first.
Frequently Asked Questions
How do I highlight duplicates in Excel?
Select the range, then go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values and press OK. Every cell whose value appears more than once in the selection gets a light red fill.
What formula highlights duplicates in conditional formatting?
=COUNTIF($A$2:$A$9,A2)>1, entered under Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, with A2:A9 selected. The range is locked with $, the cell reference A2 is not.
How do I highlight only the second duplicate, not the first?
Use =COUNTIF($A$2:A2,A2)>1. The range ends at the current row, so the first copy counts 1 and stays plain, and every later copy counts 2 or more and is highlighted.
How do I highlight the entire row if a value is a duplicate?
Select the whole table (for example A2:C8) and use a rule that locks the column of the key: =COUNTIF($B$2:$B$8,$B2)>1. The $ before B keeps every cell of the row looking at column B.
How do I remove duplicate highlighting?
Select the cells and choose Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. To edit a rule instead, use Home > Conditional Formatting > Manage Rules.
