# How to Highlight Duplicates in Excel (Step by Step)

Excel gives you two reliable ways to color repeated values: the built-in Duplicate Values rule and a COUNTIF formula. This guide walks through both, shows what each one actually flags, and covers the cases where they disagree with what you expect.

## Quick Answer

- Select the cells you want to check, then go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values [1].
- Pick a fill color in the dialog and click OK. Every value that appears more than once in the selection gets colored [1].
- For more control, add a helper column with `=IF(COUNTIF($A$2:$A$13,A2)>1,"Duplicate","Unique")` and fill it down.
- The COUNTIF approach lets you label, filter, and count duplicates instead of only coloring them.
- Both methods compare values, not formulas. Cells showing the same result count as duplicates even if the underlying formulas differ [2].

## Before You Start

Decide what "duplicate" means for your data. The Duplicate Values rule and COUNTIF compare the stored values, so a cell showing 1.00 and a cell showing 1 count as duplicates even though their number formats differ. A number stored as text is a different matter, so convert text numbers to real numbers first.

Know the scope of your selection. The Duplicate Values rule only looks inside the range you select. If you select A2:A13, a value that also appears in A14 is not flagged. Select the whole column or the whole table if you want a global check.

Check for stray spaces and empty cells. A trailing space makes "ana.lopez@example.com " a different value from "ana.lopez@example.com". Counts of duplicate and unique values can also include empty cells and spaces, which skews what you see [1].

Finally, remember that highlighting is non-destructive. It colors cells but changes nothing. If your goal is deletion, read [How to Remove Duplicates in Excel (Step by Step)](/blog/data-analysis/how-to-remove-duplicates-in-excel) first, because the Remove Duplicates feature deletes data permanently [1].

## Step by Step

### Method 1: Conditional formatting

1. Select the cells you want to check for duplicates [1].
2. Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values [1].
3. In the box next to "values with", pick the formatting you want to apply to the duplicate values [1].
4. Click OK. Duplicates are now colored.

If you want the opposite, open the same dialog and choose Unique in the dropdown instead of Duplicate [2]. That colors the values that appear exactly once.

### Method 2: COUNTIF helper column

1. Add a header in the first empty column to the right of your data, for example `Duplicate?`.
2. In the first data row, enter the COUNTIF formula with an absolute range.
3. Fill the formula down to the last row.
4. Optionally select the helper column and apply Home > Conditional Formatting > Highlight Cells Rules > Text that Contains > Duplicate to color the labels.

The COUNTIF method is the one to use when you need to sort or filter by duplicate status, or when you want a count rather than a color. For a deeper look at the counting side, see [How to Count Unique Values in Excel (Step by Step)](/blog/data-analysis/how-to-count-unique-values-in-excel).

## Worked Example

The sheet below tracks customer emails and order IDs. Column A holds the email, column B the order number, and column C a COUNTIF check that labels each row.

|   | A | B | C |
|---|---|---|---|
| 1 | Customer Email | Order ID | Duplicate? |
| 2 | ana.lopez@example.com | 1001 | `=IF(COUNTIF($A$2:$A$13,A2)>1,"Duplicate","Unique")` -> Duplicate |
| 3 | ben.carter@example.com | 1002 | `=IF(COUNTIF($A$2:$A$13,A3)>1,"Duplicate","Unique")` -> Duplicate |
| 4 | carla.mendes@example.com | 1003 | `=IF(COUNTIF($A$2:$A$13,A4)>1,"Duplicate","Unique")` -> Duplicate |
| 5 | dan.owens@example.com | 1004 | `=IF(COUNTIF($A$2:$A$13,A5)>1,"Duplicate","Unique")` -> Unique |
| 6 | eva.singh@example.com | 1005 | `=IF(COUNTIF($A$2:$A$13,A6)>1,"Duplicate","Unique")` -> Unique |
| 7 | ana.lopez@example.com | 1006 | `=IF(COUNTIF($A$2:$A$13,A7)>1,"Duplicate","Unique")` -> Duplicate |
| 8 | frank.nguyen@example.com | 1007 | `=IF(COUNTIF($A$2:$A$13,A8)>1,"Duplicate","Unique")` -> Unique |
| 9 | grace.kim@example.com | 1008 | `=IF(COUNTIF($A$2:$A$13,A9)>1,"Duplicate","Unique")` -> Unique |
| 10 | ben.carter@example.com | 1009 | `=IF(COUNTIF($A$2:$A$13,A10)>1,"Duplicate","Unique")` -> Duplicate |
| 11 | hana.sato@example.com | 1010 | `=IF(COUNTIF($A$2:$A$13,A11)>1,"Duplicate","Unique")` -> Unique |
| 12 | ivan.petrov@example.com | 1011 | `=IF(COUNTIF($A$2:$A$13,A12)>1,"Duplicate","Unique")` -> Unique |
| 13 | carla.mendes@example.com | 1012 | `=IF(COUNTIF($A$2:$A$13,A13)>1,"Duplicate","Unique")` -> Duplicate |

The formula in C2 counts how many times the email in A2 appears in the whole email column. If it appears more than once, the row is labeled Duplicate. The dollar signs lock the range so every row checks the same block of cells.

$$\text{C2} = \text{IF}\left(\text{COUNTIF}(\$A\$2:\$A\$13, A2) > 1, \text{"Duplicate"}, \text{"Unique"}\right)$$

Read the results row by row. Row 2 is the first ana.lopez@example.com and row 7 is the second, so both show Duplicate. Row 3 is the first ben.carter@example.com and row 10 is the second, so both show Duplicate. Row 4 is the first carla.mendes@example.com and row 13 is the second, so both show Duplicate. Every other email appears once and shows Unique.

Notice that the helper column flags both copies, not just the later one. That is the behavior most people want when they are auditing a list, because you can see the full pair. If you only want to mark the second and later occurrences, change the range so it ends at the current row, for example `=IF(COUNTIF($A$2:A2,A2)>1,"Duplicate","Unique")`.

To color the emails themselves, select A2:A13 and apply Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values, then choose a fill color and click OK. The six cells holding the three repeated emails get colored, and the six unique emails stay plain.

## Other Ways to Do It

**Filter instead of color.** Advanced Filter can extract unique values or filter duplicates in place. Select your range, then go to Data > Sort & Filter > Advanced [2]. This is useful when you want a clean list rather than a visual flag. See [How to Filter in Excel (Step by Step)](/blog/data-analysis/how-to-filter-in-excel) for the full workflow.

**Use a formula rule for custom logic.** In Conditional Formatting, choose New Rule > Use a formula to determine which cells to format, then enter a COUNTIF expression. This lets you highlight an entire row when one column repeats, which the built-in rule cannot do.

**Count before you color.** If you only need the number of repeats, `=COUNTIF($A$2:$A$13,A2)` returns the raw count. A value of 3 means the email appears three times. This is often faster than reading colors.

**Check a whole table.** Conditional formatting can be applied to a range of cells, an Excel table, or a named range [3]. Applying it to a table means new rows inherit the rule as you type.

## Troubleshooting

**Nothing gets highlighted.** Your selection may contain no repeated values. Widen the range and try again. Also confirm the rule is still active under Home > Conditional Formatting > Manage Rules.

**Values that look identical are not flagged.** Check for trailing spaces, non-breaking spaces, or numbers stored as text in some cells and as real numbers in others.

**The whole column lights up.** You probably selected a range that includes a header or a formula column where every cell returns the same text. Restrict the selection to the data rows only.

**The rule disappears when you sort or paste.** Conditional formatting travels with cells, but pasting over them can overwrite the rule. Reapply it after large edits, or apply it to a table so it extends automatically [3].

**PivotTable values will not highlight.** Excel cannot highlight duplicates in the Values area of a PivotTable report [1]. Put the check on the source data instead.

## Common Mistakes

- **Selecting only part of the data.** The rule only sees the selected range, so duplicates outside it are missed. Fix: select the full column or table before applying the rule [1].
- **Assuming the highlight means the row will be deleted.** Highlighting is purely visual. Fix: use Data > Remove Duplicates when you actually want removal, and back up the data first because deletion is permanent [1].
- **Forgetting the absolute reference in COUNTIF.** A relative range shifts as you fill down and gives wrong counts. Fix: write `$A$2:$A$13` with dollar signs.
- **Ignoring hidden characters.** Trailing spaces and non-breaking spaces make otherwise identical values different. Fix: clean the text with TRIM or Find and Replace before checking.
- **Leaving blank cells in the range.** Empty cells and spaces can inflate the duplicate and unique counts reported after removal [1]. Fix: clean blanks first.
- **Applying the rule to a PivotTable Values area.** It will not work there [1]. Fix: apply the check to the underlying source data.

## Limitations

Conditional formatting tells you that a value repeats, not why or how many times. To get counts you need COUNTIF or a PivotTable. The built-in rule also cannot compare across multiple columns in a meaningful way. If you select two columns, it treats each cell as a single value, so a match in column A and a match in column B are unrelated.

The method also depends on exact text matching, except that case is ignored. Hidden spaces and other invisible characters change the result, but "ANA" and "ana" count as duplicates. For large datasets, a full-column COUNTIF over hundreds of thousands of rows can slow the workbook down, and the visual highlight does not survive export to most other formats. If you need a permanent, portable flag, the helper column is the safer choice.

## Frequently Asked Questions

### How do I highlight duplicates in Excel without conditional formatting?

Use a helper column with `=IF(COUNTIF($A$2:$A$13,A2)>1,"Duplicate","Unique")` and fill it down. You can then sort or filter on that column to group the repeats together. This gives you a text label you can copy, export, or count, which a color cannot do.

### Does the Duplicate Values rule highlight all copies or only the repeats?

It highlights every cell whose value appears more than once in the selected range, including the first occurrence [1]. If an email appears three times, all three cells get colored. There is no built-in option to color only the second and later copies, so use a COUNTIF rule with an expanding range if you need that.

### Why are my duplicates not being detected?

The most common causes are trailing spaces, numbers stored as text, and a selection that does not cover all the data. Trim spaces and convert text numbers, then widen the range and reapply the rule.

### Can I highlight duplicates across two columns?

Yes, but understand what it does. If you select A2:B13, Excel treats all 24 cells as one pool and colors any value that repeats anywhere in that pool. It does not compare column A against column B row by row. To flag values in column A that appear anywhere in column B, use a formula rule such as `=COUNTIF($B$2:$B$13,A2)>0`. For a strict row-by-row match, use `=$A2=$B2`.

### What is the difference between highlighting and removing duplicates?

Highlighting only changes the cell color and leaves your data untouched [1]. Removing deletes the duplicate rows permanently, so copy the original data to another worksheet first if you might need it [1]. If you plan to remove, review the highlights first so you know exactly what will disappear.

## References

1. [Find and remove duplicates | Microsoft Support](https://support.microsoft.com/en-us/excel/find-and-remove-duplicates)
2. [Filter for or remove duplicate values | Microsoft Support](https://support.microsoft.com/en-us/excel/filter-for-or-remove-duplicate-values)
3. [Use conditional formatting to highlight information in Excel | Microsoft Support](https://support.microsoft.com/en-us/excel/use-conditional-formatting-to-highlight-information-in-excel)

## Further Reading

- [Filter for unique values or remove duplicate values | Microsoft Support](https://support.microsoft.com/en-us/excel/get-started/filter-for-unique-values-or-remove-duplicate-values)
- [Broman KW, Woo KH (2018). Data Organization in Spreadsheets. The American Statistician](https://doi.org/10.1080/00031305.2017.1375989)
- [Ziemann M, Eren Y, El-Osta A (2016). Gene name errors are widespread in the scientific literature. Genome Biology](https://doi.org/10.1186/s13059-016-1044-7)

## Related Articles

- [How to Find Duplicates in Excel (Step by Step)](/blog/data-analysis/find-duplicates-in-excel)
- [How to Remove Duplicates in Excel (Step by Step)](/blog/data-analysis/how-to-remove-duplicates-in-excel)
- [Excel Conditional Formatting: How to Highlight Cells Step by Step](/blog/data-analysis/excel-conditional-formatting-step-by-step)
- [How to Count Unique Values in Excel (Step by Step)](/blog/data-analysis/how-to-count-unique-values-in-excel)
- [How to Add Multiple Rows in Excel (Step by Step)](/blog/data-analysis/how-to-add-multiple-rows-in-excel)