Find and remove duplicates
Updated · Works in: all current Excel versions, Google Sheets · Every example checked before release
Short answer
Flag duplicates with =COUNTIF($A$2:$A$11,A2)>1, only the repeats with an expanding range COUNTIF($A$2:A2,A2)>1, and count distinct values with SUMPRODUCT((range<>"")/COUNTIF(range,range&"")), which ignores case and survives blank cells. COUNTIF criteria are not case-sensitive.
Before you delete anything, flag it. Formulas show you exactly which rows repeat, so you can check them before removing duplicates for good.
Flag every duplicate
COUNTIF($A$2:$A$11, A2) > 1 is TRUE for every row whose value appears more than once. COUNTIF ignores case.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name | Age | Plan | Joined | Check | |
| 2 | mara@example.com | Mara | 34 | Pro | 12 Jan 2026 | Duplicate |
| 3 | jonas@example.org | Jonas | 41 | Basic | 15 Jan 2026 | Duplicate |
| 4 | MARA@example.com | Mara O. | 34 | Pro | 20 Jan 2026 | Duplicate |
| 5 | priya@example.net | Priya | Team | 2 Feb 2026 | ||
| 6 | tomas@example.com | Tomás | n/a | basic | 9 Feb 2026 | |
| 7 | elif@example.org | Elif | 29 | Pro | 17 Feb 2026 | |
| 8 | jonas@example.org | Jonas Lind | 41 | Basic | 23 Feb 2026 | Duplicate |
| 9 | kenji@example.net | Kenji | 52 | Enterprise | 3 Mar 2026 | |
| 10 | sofia@example.com | Sofia | 23 | Team | 8 Mar 2026 | |
| 11 | leo@example.org | Leo | 17 | Basic | 14 Mar 2026 |
Only the repeats
An expanding range, COUNTIF($A$2:A2, A2), counts only up to the current row: the first copy stays clean, later copies get flagged.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name | Age | Plan | Joined | Repeat? | |
| 2 | mara@example.com | Mara | 34 | Pro | 12 Jan 2026 | |
| 3 | jonas@example.org | Jonas | 41 | Basic | 15 Jan 2026 | |
| 4 | MARA@example.com | Mara O. | 34 | Pro | 20 Jan 2026 | Repeat |
| 5 | priya@example.net | Priya | Team | 2 Feb 2026 | ||
| 6 | tomas@example.com | Tomás | n/a | basic | 9 Feb 2026 | |
| 7 | elif@example.org | Elif | 29 | Pro | 17 Feb 2026 | |
| 8 | jonas@example.org | Jonas Lind | 41 | Basic | 23 Feb 2026 | Repeat |
| 9 | kenji@example.net | Kenji | 52 | Enterprise | 3 Mar 2026 | |
| 10 | sofia@example.com | Sofia | 23 | Team | 8 Mar 2026 | |
| 11 | leo@example.org | Leo | 17 | Basic | 14 Mar 2026 |
Count or list distinct values
Give each row 1 ÷ how often its value appears, so two copies add up to 1: SUMPRODUCT(1/COUNTIF(range, range)) counts distinct values, ignoring case. If the range can contain blank cells, use SUMPRODUCT((range<>"")/COUNTIF(range, range&"")), which skips them instead of dividing by zero. With dynamic arrays, UNIQUE lists them (wrap the range in LOWER if case shouldn't matter).
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Name | Age | Plan | Joined | Distinct | 8 | |
| 2 | mara@example.com | Mara | 34 | Pro | 12 Jan 2026 | ||
| 3 | jonas@example.org | Jonas | 41 | Basic | 15 Jan 2026 | ||
| 4 | MARA@example.com | Mara O. | 34 | Pro | 20 Jan 2026 | ||
| 5 | priya@example.net | Priya | Team | 2 Feb 2026 | |||
| 6 | tomas@example.com | Tomás | n/a | basic | 9 Feb 2026 | ||
| 7 | elif@example.org | Elif | 29 | Pro | 17 Feb 2026 | ||
| 8 | jonas@example.org | Jonas Lind | 41 | Basic | 23 Feb 2026 | ||
| 9 | kenji@example.net | Kenji | 52 | Enterprise | 3 Mar 2026 | ||
| 10 | sofia@example.com | Sofia | 23 | Team | 8 Mar 2026 | ||
| 11 | leo@example.org | Leo | 17 | Basic | 14 Mar 2026 |
Validate entries
Check types with ISNUMBER(C2) (TRUE only for real numbers, not “41” stored as text), count missing fields with COUNTBLANK(A2:E2), and check allowed values against a list on the sheet, the same list Data validation can use: COUNTIF($H$2:$H$4, D2) > 0. A list typed in braces works too: ISNUMBER(MATCH(D2, {"Basic","Pro","Team"}, 0)).
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Name | Age | Plan | Joined | Plan check | Plans | ||
| 2 | mara@example.com | Mara | 34 | Pro | 12 Jan 2026 | OK | Basic | |
| 3 | jonas@example.org | Jonas | 41 | Basic | 15 Jan 2026 | OK | Pro | |
| 4 | MARA@example.com | Mara O. | 34 | Pro | 20 Jan 2026 | OK | Team | |
| 5 | priya@example.net | Priya | Team | 2 Feb 2026 | OK | |||
| 6 | tomas@example.com | Tomás | n/a | basic | 9 Feb 2026 | OK | ||
| 7 | elif@example.org | Elif | 29 | Pro | 17 Feb 2026 | OK | ||
| 8 | jonas@example.org | Jonas Lind | 41 | Basic | 23 Feb 2026 | OK | ||
| 9 | kenji@example.net | Kenji | 52 | Enterprise | 3 Mar 2026 | Check | ||
| 10 | sofia@example.com | Sofia | 23 | Team | 8 Mar 2026 | OK | ||
| 11 | leo@example.org | Leo | 17 | Basic | 14 Mar 2026 | OK |
Check yourself
One real exercise from the “Data quality” unit. Checked on our server; no account needed.Flag every email that appears more than once with "Duplicate", in F2.
- Use COUNTIF
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name | Age | Plan | Joined | Check | |
| 2 | mara@example.com | Mara | 34 | Pro | 12 Jan 2026 | |
| 3 | jonas@example.org | Jonas | 41 | Basic | 15 Jan 2026 | |
| 4 | MARA@example.com | Mara O. | 34 | Pro | 20 Jan 2026 | |
| 5 | priya@example.net | Priya | Team | 2 Feb 2026 | ||
| 6 | tomas@example.com | Tomás | n/a | basic | 9 Feb 2026 | |
| 7 | elif@example.org | Elif | 29 | Pro | 17 Feb 2026 | |
| 8 | jonas@example.org | Jonas Lind | 41 | Basic | 23 Feb 2026 | |
| 9 | kenji@example.net | Kenji | 52 | Enterprise | 3 Mar 2026 | |
| 10 | sofia@example.com | Sofia | 23 | Team | 8 Mar 2026 | |
| 11 | leo@example.org | Leo | 17 | Basic | 14 Mar 2026 |
Common mistakes
Deleting duplicates without checking which copy has the newer or fuller data.
Forgetting that COUNTIF ignores case, or that extra spaces make two values look different. TRIM first.
A relative COUNTIF range that slides as you fill down.
Questions
How do I remove duplicates without a formula?+
Excel: Data → Remove Duplicates. Google Sheets: Data → Data cleanup → Remove duplicates. Keep a copy first.
How do I find case-sensitive duplicates?+
Use SUMPRODUCT(--EXACT(range, A2)) instead of COUNTIF.
Reading is step one. Now make it stick.
Practise it in a five-minute lesson on a live sheet, with instant feedback. No account needed for the free units.