Formula guide · Excel and Google Sheets · Updated May 13, 2026
Find Duplicates with a Formula
Use COUNTIF or COUNTIFS to identify duplicate spreadsheet rows or values.
Quick Answer
Use COUNTIF to test whether a value appears more than once, then filter or highlight TRUE results.
Copyable Formula
=COUNTIF($A$2:$A$100,A2)>1Syntax
=COUNTIF(range,current_value)>1Excel and Google Sheets
Worked Example
| A | B |
|---|---|
| Duplicate? | |
| maya@acme.com | TRUE if repeated |
Result: Returns TRUE for values that appear more than once in the checked range.
Steps
- Choose the column that should be unique.
- Use COUNTIF against the full checked range.
- Compare the count to greater than 1.
- Filter TRUE values or use the formula in conditional formatting.
Common Mistakes
- Using a moving range instead of locking the checked range.
- Not cleaning spaces before duplicate checks.
- Checking only one column when duplicates require multiple fields.
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.