The Duplicate Values rule guide
Select the column to check, then Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values. Every value that appears more than once in the selection lights up, both copies. Capitals don't count: "inv-2213" and "INV-2213" are duplicates.
Pick one column
The rule compares every selected cell with every other selected cell. Select an invoice column and you find repeated invoices. Select the whole table and supplier names and amounts light up too, because they repeat for good reasons.
Select all of it
A range that stops one row short misses a copy at the bottom, and its partner higher up stays plain as well: within the selection it's unique. Ctrl+Shift+↓ from the first value selects to the end of the column.
What counts as the same
- Capitals don't matter. "INV-2213" and "inv-2213" match.
- Spaces do. "INV-2213 " with a trailing space is a different value. Clean the column with TRIM first if you suspect them.
- Blanks are left alone. Empty cells are never marked as duplicates.
Count or list them instead
=COUNTIF($A$2:$A$13, A2)>1 in a helper column gives TRUE for each repeated row, which you can filter. UNIQUE lists each value once. See UNIQUE.
FAQ
- How do I highlight duplicates in Excel?
- Select the column, then Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values, and press OK. Both copies of each repeated value are highlighted.
- Is Excel's duplicate check case-sensitive?
- No. The Duplicate Values rule treats "ABC" and "abc" as the same. Extra spaces do make values different.
- How do I highlight only the second copy of a duplicate?
- Use a formula rule: select A2:A13 and use =COUNTIF($A$2:A2, A2)>1. Only the repeats after the first are highlighted.
- How do I remove duplicates instead of highlighting them?
- Data > Remove Duplicates deletes repeated rows. Highlight them first to see what will go.
