Formula guide · Excel and Google Sheets · Updated May 13, 2026
Highlight Duplicates in Excel and Google Sheets
Use conditional formatting formulas to highlight duplicate values in a column or table.
Quick Answer
Use COUNTIF in a conditional formatting rule to highlight values that appear more than once.
Copyable Formula
=COUNTIF($A:$A,$A2)>1Syntax
=COUNTIF($A:$A,$A2)>1Excel and Google Sheets
Worked Example
| A | Rule result |
|---|---|
| Invoice ID | Duplicate? |
| INV-101 | TRUE if repeated |
| INV-102 | FALSE if unique |
Result: Cells with values that appear more than once are highlighted.
Steps
- Select the range where duplicates should be highlighted.
- Create a custom formula conditional formatting rule.
- Use COUNTIF with a locked lookup range and a relative current cell.
- Choose a highlight style that is visible but not distracting.
Common Mistakes
- Forgetting to lock the lookup range with dollar signs.
- Highlighting blanks as duplicates.
- Using duplicate formatting before cleaning extra spaces or inconsistent casing.
Excel vs Google Sheets Notes
The core idea works in both Excel and Google Sheets, but separators, function availability, and array behavior can vary by account, locale, and version.
Editorial check: This guide was last updated May 13, 2026. Formula behavior can vary by Excel version, Google Sheets rollout, and spreadsheet locale.