# How to list duplicate values with a formula in Excel

Keep the values that appear more than once, then list each one once: `=UNIQUE(FILTER(A2:A12,COUNTIF(A2:A12,A2:A12)>1))`.

**Excel:** `=UNIQUE(FILTER(A2:A12,COUNTIF(A2:A12,A2:A12)>1))` (Excel 2021, 2024 and Microsoft 365)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Invoice | Vendor | Amount |  | Entered twice |
| INV-2041 | Acme Supply | 420 |  |  |
| INV-2042 | Bluebird | 1250 |  |  |
| INV-2043 | Cedar Ltd | 310 |  |  |
| INV-2042 | Bluebird | 1250 |  |  |
| INV-2044 | Dune Inc | 880 |  |  |
| INV-2045 | Acme Supply | 1460 |  |  |
| INV-2046 | Elm & Co | 95 |  |  |
| INV-2045 | Acme Supply | 1460 |  |  |
| INV-2047 | Cedar Ltd | 730 |  |  |
| INV-2042 | Bluebird | 1250 |  |  |
| INV-2048 | Fern Foods | 515 |  |  |

Formula in E2: `=UNIQUE(FILTER(A2:A12,COUNTIF(A2:A12,A2:A12)>1))`

Result (spills 2 × 1): INV-2042; INV-2045

## 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.

## Other ways

- `=FILTER(A2:A12,COUNTIF(A2:A12,A2:A12)>1)`: 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))`: 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.

## 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))`.

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

Source: https://spreadsheetpractice.com/formulas/list-duplicate-values-with-a-formula