Find and flag duplicate values

About this lesson

Clean & compare7 min·Growing

Mark every repeated registration, keep blank rows blank, and see which values need a closer look.

Task requirements

Fill B2:B9 with Duplicate or Unique. Ignore empty cells and treat uppercase and lowercase text as the same value.

Dataset

Teaching · read-only worksheet · Selected B2

Step 1 / 6Keep the list fixed

Inspect

Inspect the highlighted cells. This step has no automatic change to apply. Teaching is read-only; switch to Your turn to edit.

Loading your interactive spreadsheet…

Preparing the worksheet and formulas…

Follow the highlighted cells. Switch to Your turn to edit. Answer cells: B2, B3, B4, B5, B6, B7, B8, B9. Browser calculations may differ from your Excel version.

Apply your first formula, then fill the answer range. Relative references move; $-locked references stay put.

Practice stays on this device. No worksheet data is uploaded.

Choose the result you need

Reference example

COUNTIF counts the whole list, including the current row. A count above one flags every member of a repeated group. Fill B2 through B9.

=IF(A2="","",IF(COUNTIF($A$2:$A$9,A2)>1,"Duplicate","Unique"))

Download this example

Use this short version for ordinary text labels without wildcard symbols or leading comparison operators. Numeric-looking IDs may be coerced; choose literal comparison when spelling matters. Matching ignores case and keeps spaces significant. This flags records for review without deleting them.

All result formulas

B2: =IF(A2="","",IF(COUNTIF($A$2:$A$9,A2)>1,"Duplicate","Unique"))
B3: =IF(A3="","",IF(COUNTIF($A$2:$A$9,A3)>1,"Duplicate","Unique"))
B4: =IF(A4="","",IF(COUNTIF($A$2:$A$9,A4)>1,"Duplicate","Unique"))
B5: =IF(A5="","",IF(COUNTIF($A$2:$A$9,A5)>1,"Duplicate","Unique"))
B6: =IF(A6="","",IF(COUNTIF($A$2:$A$9,A6)>1,"Duplicate","Unique"))
B7: =IF(A7="","",IF(COUNTIF($A$2:$A$9,A7)>1,"Duplicate","Unique"))
B8: =IF(A8="","",IF(COUNTIF($A$2:$A$9,A8)>1,"Duplicate","Unique"))
B9: =IF(A9="","",IF(COUNTIF($A$2:$A$9,A9)>1,"Duplicate","Unique"))

Complete example · Workshop registrations. Quoted strings show their exact spaces and spelling. Result cells are calculated outputs.Scroll sideways to read every column.
Row	A	B
1	"Registration"	"Flag"
2	"maya@harbor.test"	"Duplicate"
3	"leo@harbor.test"	"Duplicate"
4	"maya@harbor.test"	"Duplicate"
5	—	""
6	"aya@harbor.test"	"Unique"
7	"LEO@harbor.test"	"Duplicate"
8	"noor@harbor.test"	"Unique"
9	"milo@harbor.test"	"Unique"

These are reference examples with matching downloads. The primary worksheet has its own stated task and checks; choosing an example here leaves that worksheet and your progress unchanged.

Open the primary practice

Microsoft reference ↗ · Browser calculations checked. Native Excel and Google Sheets recalculation of these reference files has not been verified.

All worked examples

Expand an example to read its complete source data and checked results, then download the matching workbook. Cell references belong to that example. Opening it leaves the primary practice and your progress unchanged.

Flag every repeated label

COUNTIF counts the whole list, including the current row. A count above one flags every member of a repeated group. Fill B2 through B9.

=IF(A2="","",IF(COUNTIF($A$2:$A$9,A2)>1,"Duplicate","Unique"))

Enter in B2 in this example's workbook. The download contains the complete source and all result formulas.

Complete example · Workshop registrations. Quoted strings show their exact spaces and spelling. Result cells are calculated outputs.Scroll sideways to read every column.
Row	A	B
1	"Registration"	"Flag"
2	"maya@harbor.test"	"Duplicate"
3	"leo@harbor.test"	"Duplicate"
4	"maya@harbor.test"	"Duplicate"
5	—	""
6	"aya@harbor.test"	"Unique"
7	"LEO@harbor.test"	"Duplicate"
8	"noor@harbor.test"	"Unique"
9	"milo@harbor.test"	"Unique"

B2: "Duplicate"
B3: "Duplicate"
B4: "Duplicate"
B5: ""
B6: "Unique"
B7: "Duplicate"
B8: "Unique"
B9: "Unique"

Download this example's workbook

Flag only later copies

The range ends at the current row. The first copy has one match; a later copy has more than one. Keep the first reference locked while filling B2:B9.

=IF(A2="","",IF(COUNTIF($A$2:A2,A2)>1,"Later copy","Keep"))

Enter in B2 in this example's workbook. The download contains the complete source and all result formulas.

Complete example · Workshop registrations. Quoted strings show their exact spaces and spelling. Result cells are calculated outputs.Scroll sideways to read every column.
Row	A	B
1	"Registration"	"Flag"
2	"maya@harbor.test"	"Keep"
3	"leo@harbor.test"	"Keep"
4	"maya@harbor.test"	"Later copy"
5	—	""
6	"aya@harbor.test"	"Keep"
7	"LEO@harbor.test"	"Later copy"
8	"noor@harbor.test"	"Keep"
9	"milo@harbor.test"	"Keep"

B2: "Keep"
B3: "Keep"
B4: "Later copy"
B5: ""
B6: "Keep"
B7: "Later copy"
B8: "Keep"
B9: "Keep"

Use ordinary text labels without wildcard symbols, leading comparison operators or numeric-looking IDs. A first occurrence is not flagged even when a match appears later. Source order determines which copy is kept. Literal-identifier comparison needs the literal method.

Download this example's workbook

Keep wildcard symbols literal

LOWER ignores letter case; EXACT compares each entire label literally. SUMPRODUCT counts those exact matches. The star in Part*1 does not stand for another character.

=IF(A2="","",IF(SUMPRODUCT(--EXACT(LOWER($A$2:$A$9),LOWER(A2)))>1,"Duplicate","Unique"))

Enter in B2 in this example's workbook. The download contains the complete source and all result formulas.

Complete example · Blanks and literal wildcard characters. Quoted strings show their exact spaces and spelling. Result cells are calculated outputs.Scroll sideways to read every column.
Row	A	B
1	"Registration"	"Flag"
2	"Part*1"	"Duplicate"
3	"PartX1"	"Unique"
4	"Part*1"	"Duplicate"
5	"Code?"	"Unique"
6	"CodeZ"	"Unique"
7	—	""
8	"~"	"Duplicate"
9	"~"	"Duplicate"

B2: "Duplicate"
B3: "Unique"
B4: "Duplicate"
B5: "Unique"
B6: "Unique"
B7: ""
B8: "Duplicate"
B9: "Duplicate"

This is the primary practice rule: all nonempty repeated text representations are flagged. Leading/trailing spaces remain significant. It neither extracts records nor removes rows.

Download this example's workbook

UNDERSTAND THE IDEA

How it works

All duplicates or only later copies?

This page flags every occurrence of a repeated label, including the first one. To mark only later copies, compare each label with the rows from the beginning of the list through its current row. Choose the rule before removing anything.

What counts as the same value?

The task compares the text representation without regard to letter case. Leading and trailing spaces remain meaningful. The number 7 and text "7" compare alike; text "007" remains a different label. Empty inputs are excluded.

Use the result in Excel

The flag column is a review aid. You can filter it to Duplicate, or use the same count condition in a conditional-formatting rule. This practice does not recreate Excel’s full conditional-formatting editor.

A shorter option for plain labels

For labels with no wildcard symbols, =IF(A2="","",IF(COUNTIF($A$2:$A$9,A2)>1,"Duplicate","Unique")) is shorter. COUNTIF interprets * and ? as patterns, so that version is not a literal-label replacement for the formula used here.

Read the full step-by-step walkthrough

Keep the list fixed
Every row is checked against the same eight records. Lock the range so it does not move when the formula is filled down.
$A$2:$A$9
Ignore letter case deliberately
LOWER gives both the whole list and the current value the same letter case. This changes the comparison, not the original cells.
LOWER($A$2:$A$9),LOWER(A2)
Compare complete text labels
EXACT returns TRUE only when a complete label matches. A star, question mark or tilde stays a literal character.
EXACT(LOWER($A$2:$A$9),LOWER(A2))
Count the matches
The double minus converts TRUE and FALSE to 1 and 0. SUMPRODUCT adds them, and a count above one marks a repeated group.
SUMPRODUCT(--EXACT(…))>1
Leave missing values alone
An empty registration is not a useful duplicate. Return an empty string before doing the count.
IF(A2="","",…)
Fill the flag column
Copy B2 down through B9. A unique label gets Unique; every occurrence of a repeated label gets Duplicate.

WHEN SOMETHING LOOKS OFF

Things to watch

Only one copy is flagged

An expanding range marks later occurrences. Use the complete fixed range to flag every copy.

The checked list drifts

Without dollar signs, the count range shifts as you fill down and earlier records disappear from the check.

A symbol matches too much

A plain COUNTIF criterion can interpret * or ? as a wildcard. This formula uses EXACT so these characters remain literal.

About this example

Published examples, formula filling and practice checks were tested in Chrome on October 13, 2026. Tests cover the supplied datasets and stated input rules, not every possible formula. These workbooks have not yet been independently recalculated in Microsoft Excel or Google Sheets.

Browser: testedExcel: not verifiedGoogle Sheets: not verified

Content version 3. Practice checks compare calculated results across different datasets; they do not certify every possible formula.

Reference material

Find and flag duplicate values

Choose the result you need

All worked examples

How it works

All duplicates or only later copies?

What counts as the same value?

Use the result in Excel

A shorter option for plain labels

Things to watch

Only one copy is flagged

The checked list drifts

A symbol matches too much

About this example

Keep the momentum.

Count distinct values and show the list

Clean spaces and line breaks in imported text

Fill formulas across and down with fixed references