# How to remove duplicate rows with a formula in Excel

Pass the whole table to UNIQUE: `=UNIQUE(A2:C11)` keeps each distinct row once, comparing every column.

**Excel:** `=UNIQUE(A2:C11)` (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 |
| --- | --- | --- | --- | --- |
| Name | Email | City |  | Clean list |
| Ana Ruiz | ana@ruiz.example | Austin |  |  |
| Raj Patel | raj@patel.example | Boston |  |  |
| Lee Chen | lee@chen.example | Denver |  |  |
| Ana Ruiz | ana@ruiz.example | Austin |  |  |
| Kim Ortiz | kim@ortiz.example | Miami |  |  |
| Raj Patel | raj@patel.example | Chicago |  |  |
| Mo Diaz | mo@diaz.example | Seattle |  |  |
| Lee Chen | lee@chen.example | Denver |  |  |
| Eve Lund | eve@lund.example | Portland |  |  |
| Mo Diaz | mo@diaz.example | Seattle |  |  |

Formula in E2: `=UNIQUE(A2:C11)`

Result (spills 7 × 3): Ana Ruiz, ana@ruiz.example, Austin; Raj Patel, raj@patel.example, Boston; Lee Chen, lee@chen.example, Denver; Kim Ortiz, kim@ortiz.example, Miami; Raj Patel, raj@patel.example, Chicago; Mo Diaz, mo@diaz.example, Seattle; Eve Lund, eve@lund.example, Portland

## How it works

Given several columns, UNIQUE compares whole rows: a row is dropped only when every column matches an earlier row. The second Ana Ruiz row goes, but Raj Patel appears twice because his city differs. The original list is left as it is, and the clean copy updates when rows are added.

## Other ways

- `=SORT(UNIQUE(A2:C11))`: Sort the clean list by name at the same time. (Sorted A to Z by name.)
- `=UNIQUE(A2:B11)`: Compare only the name and email columns, so a changed city doesn't count as a different person. (Raj Patel appears once, and only two columns come back.)

## Common mistakes

- Differences you can't see still count: a trailing space or a different capital in an email makes two rows "different". Clean the data with TRIM and LOWER first.
- UNIQUE keeps the first copy of each row. If the newest record is further down, sort the data by date, newest first, before removing duplicates.

## Questions

**How do I remove duplicates based on one column but keep the whole row?**

Keep the rows where that value appears for the first time: `=FILTER(A2:C11,XMATCH(B2:B11,B2:B11)=SEQUENCE(ROWS(B2:B11)))`.

**Is this different from Data > Remove Duplicates?**

Remove Duplicates deletes rows from the data once. UNIQUE leaves the data alone and keeps a clean copy that updates.

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/remove-duplicate-rows-with-a-formula