How to list duplicate values with a formula in Excel
Formula (Excel and Google Sheets)
=UNIQUE(FILTER(A2:A12,COUNTIF(A2:A12,A2:A12)>1))Keep the values that appear more than once, then list each one once: =UNIQUE(FILTER(A2:A12,COUNTIF(A2:A12,A2:A12)>1)).
- Result on the example
- a 2-row list that spills from E2 in
E2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in E2. Change any input and the result updates, or edit the formula to experiment.
How it works
COUNTIF(A2:A12,A2:A12) counts every invoice number in the column, giving one count per row: 3 for INV-2042, 2 for INV-2045 and 1 for the rest. FILTER keeps the rows with a count above 1, and UNIQUE removes the repeats so each duplicate is listed once.
=UNIQUE(FILTER(A2:A12,COUNTIF(A2:A12,A2:A12)>1))Result{2 rows × 1 col} "INV-2042", "INV-2045"
A2:A1211 rows × 1 col: "INV-2041", "INV-2042", "INV-2043", "INV-2042", …A range: 11 cells in one column.
COUNTIF(A2:A12,A2:A12){11 rows × 1 col} 1, 3, 1, 3, …COUNTIF: Counts cells that meet one condition.- range
A2:A1211 rows × 1 col: "INV-2041", "INV-2042", "INV-2043", "INV-2042", … - criteria
A2:A1211 rows × 1 col: "INV-2041", "INV-2042", "INV-2043", "INV-2042", …
- range
COUNTIF(A2:A12,A2:A12)>1{11 rows × 1 col} FALSE, TRUE, FALSE, TRUE, …The > operator tests greater than.
FILTER(A2:A12,COUNTIF(A2:A12,A2:A12)>1){5 rows × 1 col} "INV-2042", "INV-2042", "INV-2045", "INV-2045", …FILTER: Rows of a range that meet every condition. #N/A when none match.- range
A2:A1211 rows × 1 col: "INV-2041", "INV-2042", "INV-2043", "INV-2042", … - condition1
COUNTIF(A2:A12,A2:A12)>1{11 rows × 1 col} FALSE, TRUE, FALSE, TRUE, …
- range
UNIQUE(FILTER(A2:A12,COUNTIF(A2:A12,A2:A12)>1)){2 rows × 1 col} "INV-2042", "INV-2045"UNIQUE: Distinct rows of a range. Spills.- array
FILTER(A2:A12,COUNTIF(A2:A12,A2:A12)>1){5 rows × 1 col} "INV-2042", "INV-2042", "INV-2045", "INV-2045", …
- array
In Google Sheets
The same formula works in Google Sheets: =UNIQUE(FILTER(A2:A12,COUNTIF(A2:A12,A2:A12)>1)). We ran it in Google Sheets mode on the same data and it returns the same results, spilling 2 rows.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =FILTER(A2:A12,COUNTIF(A2:A12,A2:A12)>1) | spills 5 × 1Different result | Without UNIQUE you see every occurrence, which shows how many extra copies there are. INV-2042 appears three times and INV-2045 twice. |
| =UNIQUE(FILTER(A2:C12,COUNTIF(A2:A12,A2:A12)>1)) | spills 2 × 3Different result | Return the whole row of each duplicate. Invoice, vendor and amount for each duplicate. |
Common mistakes
- COUNTIF ignores case but not spaces: "INV-2042 " with a trailing space counts as a different invoice. TRIM imported data first.
- Numbers stored as text and real numbers don't match each other in COUNTIF, so 2042 and "2042" aren't treated as duplicates.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
List duplicate values with a formula
E2, list each invoice number that appears more than once in A2:A12, once each.Questions
How do I show how many times each duplicate appears?
Count them next to the list: =LET(d,UNIQUE(FILTER(A2:A12,COUNTIF(A2:A12,A2:A12)>1)),HSTACK(d,COUNTIF(A2:A12,d))).
How do I list duplicates across two columns?
Use COUNTIFS on both columns: =UNIQUE(FILTER(A2:B12,COUNTIFS(A2:A12,A2:A12,B2:B12,B2:B12)>1)).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.