# UNIQUE, COUNTUNIQUE and duplicates (Google Sheets lesson 8 of 17)

> List each value once, count how many different values there are, and flag the repeats before they cause trouble.

Source: https://spreadsheetpractice.com/google-sheets/learn/unique-and-duplicates · Updated October 29, 2026

- **Course:** [Google Sheets course](https://spreadsheetpractice.com/google-sheets), track "Sheets power tools"
- **Time:** about 9 minutes, 3 graded exercises
- **Functions:** `UNIQUE`, `SORT`, `COUNTUNIQUE`, `COUNTIF`
- **Access:** Pro (the lesson text is free; the graded exercises need Pro)
- **Key formula:** `=UNIQUE(C2:C9)`: Each value once, in first-seen order

## How it works

`UNIQUE` returns each different value in a range once, in the order it first appears: `=UNIQUE(C2:C9)`. The results fill the cells below, so leave them empty. Wrap it in SORT for an alphabetical list: `=SORT(UNIQUE(C2:C9))`. With only a range, SORT orders by the first column, A to Z.

`COUNTUNIQUE` counts the different values without listing them: `=COUNTUNIQUE(B2:B9)` tells you how many people signed up, however many times each one did. It is a Google Sheets function; Excel has no direct equivalent.

To find duplicates, count each value against the whole column: `=COUNTIF($B$2:$B$9, B2)>1` is TRUE for every email that appears more than once. Lock the range with $ so it stays put as you fill down, and leave B2 unlocked so it moves to B3, B4 and so on.

To keep the first copy and flag only the extras, let the range grow as you go: `=COUNTIF($B$2:B2, B2)>1`. In row 5 it only looks at B2:B5, so the first time an email appears the count is 1. The same formula works as a conditional formatting rule: select the column, choose Format, then Conditional formatting, pick "Custom formula is", and enter it to highlight the repeats.

## Syntax

| Formula | What it does |
|---|---|
| `=UNIQUE(C2:C9)` | Each value once, in first-seen order |
| `=SORT(UNIQUE(C2:C9))` | Each value once, A to Z |
| `=COUNTUNIQUE(B2:B9)` | How many different values |
| `=COUNTIF($B$2:$B$9,B2)>1` | TRUE when this value appears more than once |
| `=COUNTIF($B$2:B2,B2)>1` | TRUE only for the second and later copies |

## Watch out for

- UNIQUE needs empty cells below it for its results. Anything in the way stops it with an error.
- Without the $ signs, the COUNTIF range slides down as you fill and starts missing earlier rows.
- "jo@fernhill.com" and "jo@fernhill.com " with a trailing space are different values. Clean the column with TRIM first if counts look high.
- Data, then Data cleanup, then Remove duplicates deletes rows for good. A formula flag lets you check before anything is removed.

## The exercises

Solve them in the live grid at [UNIQUE, COUNTUNIQUE and duplicates](https://spreadsheetpractice.com/google-sheets/learn/unique-and-duplicates). Each answer is graded, then re-checked on changed data so typed-in numbers don't pass.

1. **Workshops on offer.** In F2, list each workshop from C2:C9 once, sorted A to Z. Use one formula.
2. **How many, not counting repeats.** In I2, count how many different people signed up, using the emails in B2:B9. In I3, count how many different workshops there are in C2:C9.
3. **Flag the repeats.** In E2, show TRUE when the email in B2 appears more than once in B2:B9, and fill down to E9. In F2, show TRUE only for the second and later copies of an email, so the first copy stays FALSE. Fill down to F9.

Previous: [Charts that fit the data](https://spreadsheetpractice.com/google-sheets/learn/charts) · Next: [ARRAYFORMULA: one formula for the whole column](https://spreadsheetpractice.com/google-sheets/learn/arrayformula)

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
