# How to mark the first occurrence of each value in Excel

Count from the top of the list down to the current row: =IF(COUNTIF($A$2:A2,A2)=1,"First","Repeat").

**Excel:** `=IF(COUNTIF($A$2:A2,A2)=1,"First","Repeat")` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Email | Occurrence |
| ana@northwind.example |  |
| ben@contoso.example |  |
| cara@fabrikam.example |  |
| ana@northwind.example |  |
| dev@litware.example |  |
| ben@contoso.example |  |
| eli@adatum.example |  |
| ana@northwind.example |  |
| fay@proseware.example |  |

Formula in B2, filled to B10: `=IF(COUNTIF($A$2:A2,A2)=1,"First","Repeat")`

Result: First; First; First; Repeat; First; Repeat; First; Repeat; First

## How it works

The range `$A$2:A2` starts locked at the top and grows as you fill down, so each row counts only the rows above it plus itself. The first copy counts 1, later copies count 2 or more. Filter on "First" to keep one row per email.

## Other ways

- `=COUNTIF($A$2:A2,A2)`: The running count itself: 1 for the first copy, 2 for the second, and so on. (Shows the occurrence number.)

## Common mistakes

- Lock only the start of the range. `$A$2:$A$2` wouldn't grow, and `A2:A2` would move entirely.
- Sort first if "first" should mean the earliest date rather than the highest row.

## Questions

**How do I keep only the first occurrence?**

Filter the column for "First", or use `=UNIQUE(A2:A10)` for a list of distinct values.

**How do I number the occurrences?**

Use the running count alone: `=COUNTIF($A$2:A2,A2)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-29.

Source: https://spreadsheetpractice.com/formulas/mark-first-occurrence-of-duplicates