Cleanup formula page
Find Duplicates Formula
Use this when you want a separate list of values that appear more than once.
Find duplicate values with a formula.
With the sample data, maya@example.com appears in the duplicate list.
Copy formulas
=UNIQUE(FILTER(A2:A100, (A2:A100<>"")*(COUNTIF(A2:A100, A2:A100)>1)))=IFERROR(UNIQUE(FILTER(A2:A100, (A2:A100<>"")*(COUNTIF(A2:A100, A2:A100)>1))), "No duplicates")Both formulas exclude blanks before UNIQUE returns duplicated values; Google Sheets adds IFERROR so an all-distinct range can show No duplicates instead of an error.
Example data
| Region | Status | |
|---|---|---|
| maya@example.com | East | Active |
| noah@example.com | West | Active |
| maya@example.com | East | Active |
| West | Inactive |
With the sample data, maya@example.com appears in the duplicate list.
How the formula works
- The nonblank condition excludes unused rows.
- COUNTIF checks how many times each remaining value appears.
- UNIQUE returns each duplicate value once.
| Syntax piece | Role in the formula |
|---|---|
| COUNTIF(A2:A100,A2:A100) | The frequency calculation for every source value. |
| >1 | The FILTER condition that keeps values occurring more than once. |
| A2:A100<>"" | The condition that excludes blank and unused rows. |
| UNIQUE | The function that lists each duplicated value once. |
Verified examples
=UNIQUE(FILTER(A2:A100, (A2:A100<>"")*(COUNTIF(A2:A100, A2:A100)>1)))Excel: Use the sample email column where maya@example.com occurs more than once and later rows are blank. Returns: maya@example.com once
=IFERROR(UNIQUE(FILTER(A2:A100, (A2:A100<>"")*(COUNTIF(A2:A100, A2:A100)>1))), "No duplicates")Google Sheets: Enter A, B, and C once each in A2:A4 and leave the rest of A2:A100 blank. Returns: No duplicates
=UNIQUE(FILTER(A2:A100, (A2:A100<>"")*(COUNTIF(A2:A100, A2:A100)>1)))Excel: Enter A, A, A, and B in A2:A5 and leave the rest of A2:A100 blank. Returns: A once
Common errors and fixes
| Issue | Likely cause | Fix |
|---|---|---|
| Excel returns #CALC! when every value is distinct | FILTER has no matching duplicate rows. | Wrap the formula with IFERROR or add an if-empty result when a text fallback is required. |
| Apparent duplicates are separate | Values differ by hidden spaces or other invisible characters. | Clean the source with TRIM or a helper column before counting. |
| The result shows a spill error | Cells below the formula already contain values. | Clear the spill area or move the formula to an empty column. |
When not to use this formula
- Do not use this formula to label each source row; use a row-level duplicate flag for that workflow.
Alternatives
| Alternative | When to use it |
|---|---|
| Flag Duplicate Values Formula | Use when every duplicated source row needs a Duplicate label. |
| Duplicate Checker Formula Builder | Use when duplicate criteria and labels need interactive configuration. |
Related formulas
Official references
- UNIQUE function from Microsoft
- COUNTUNIQUE function from Google
- Use the COUNTIF function in Microsoft Excel from Microsoft
FAQ
Is each duplicate value listed more than once?
No. UNIQUE returns each value that is duplicated exactly once in the result.
Does this flag source rows?
No. It produces a separate list of duplicated values; use a row-level flag when source rows need labels.