formulastreak

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.

Member sign-ups · edit F2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultF2 = Duplicate
ABCDEF
1EmailNameAgePlanJoinedCheck
2mara@example.comMara34Pro12 Jan 2026Duplicate
3jonas@example.orgJonas41Basic15 Jan 2026Duplicate
4MARA@example.comMara O.34Pro20 Jan 2026Duplicate
5priya@example.netPriyaTeam2 Feb 2026
6tomas@example.comTomásn/abasic9 Feb 2026
7elif@example.orgElif29Pro17 Feb 2026
8jonas@example.orgJonas Lind41Basic23 Feb 2026Duplicate
9kenji@example.netKenji52Enterprise3 Mar 2026
10sofia@example.comSofia23Team8 Mar 2026
11leo@example.orgLeo17Basic14 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.

Member sign-ups · edit F2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultF2 = (empty)
ABCDEF
1EmailNameAgePlanJoinedRepeat?
2mara@example.comMara34Pro12 Jan 2026
3jonas@example.orgJonas41Basic15 Jan 2026
4MARA@example.comMara O.34Pro20 Jan 2026Repeat
5priya@example.netPriyaTeam2 Feb 2026
6tomas@example.comTomásn/abasic9 Feb 2026
7elif@example.orgElif29Pro17 Feb 2026
8jonas@example.orgJonas Lind41Basic23 Feb 2026Repeat
9kenji@example.netKenji52Enterprise3 Mar 2026
10sofia@example.comSofia23Team8 Mar 2026
11leo@example.orgLeo17Basic14 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).

Member sign-ups · edit G1 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultG1 = 8
ABCDEFG
1EmailNameAgePlanJoinedDistinct8
2mara@example.comMara34Pro12 Jan 2026
3jonas@example.orgJonas41Basic15 Jan 2026
4MARA@example.comMara O.34Pro20 Jan 2026
5priya@example.netPriyaTeam2 Feb 2026
6tomas@example.comTomásn/abasic9 Feb 2026
7elif@example.orgElif29Pro17 Feb 2026
8jonas@example.orgJonas Lind41Basic23 Feb 2026
9kenji@example.netKenji52Enterprise3 Mar 2026
10sofia@example.comSofia23Team8 Mar 2026
11leo@example.orgLeo17Basic14 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)).

Member sign-ups · edit F2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultF2 = OK
ABCDEFGH
1EmailNameAgePlanJoinedPlan checkPlans
2mara@example.comMara34Pro12 Jan 2026OKBasic
3jonas@example.orgJonas41Basic15 Jan 2026OKPro
4MARA@example.comMara O.34Pro20 Jan 2026OKTeam
5priya@example.netPriyaTeam2 Feb 2026OK
6tomas@example.comTomásn/abasic9 Feb 2026OK
7elif@example.orgElif29Pro17 Feb 2026OK
8jonas@example.orgJonas Lind41Basic23 Feb 2026OK
9kenji@example.netKenji52Enterprise3 Mar 2026Check
10sofia@example.comSofia23Team8 Mar 2026OK
11leo@example.orgLeo17Basic14 Mar 2026OK

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
Select F2 and type = to start its formula (in the cell or the formula bar)
ABCDEF
1EmailNameAgePlanJoinedCheck
2mara@example.comMara34Pro12 Jan 2026
3jonas@example.orgJonas41Basic15 Jan 2026
4MARA@example.comMara O.34Pro20 Jan 2026
5priya@example.netPriyaTeam2 Feb 2026
6tomas@example.comTomásn/abasic9 Feb 2026
7elif@example.orgElif29Pro17 Feb 2026
8jonas@example.orgJonas Lind41Basic23 Feb 2026
9kenji@example.netKenji52Enterprise3 Mar 2026
10sofia@example.comSofia23Team8 Mar 2026
11leo@example.orgLeo17Basic14 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.

Practise it now