Only one copy is flagged
An expanding range marks later occurrences. Use the complete fixed range to flag every copy.
Find and flag duplicate values
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Fill B2:B9 with Duplicate or Unique. Ignore empty cells and treat uppercase and lowercase text as the same value.
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.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Mark every repeated registration, keep blank rows blank, and see which values need a closer look.
Choose the result you need
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"))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"))
| 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 below has its own stated task and checks; choosing an example here leaves that worksheet and your progress unchanged.
Open the primary practice ↓Microsoft function reference ↗ · Browser calculations checked. Native Excel and Google Sheets recalculation of these reference files has not been verified.
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.
Things to watch
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.
Content version 3. Practice checks compare calculated results across different datasets; they do not certify every possible formula.