How to remove duplicate rows with a formula in Excel
Formula (Excel and Google Sheets)
=UNIQUE(A2:C11)Pass the whole table to UNIQUE: =UNIQUE(A2:C11) keeps each distinct row once, comparing every column.
- Result on the example
- a 7-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
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.
=UNIQUE(A2:C11)Result{7 rows × 3 cols} "Ana Ruiz", "ana@ruiz.example", "Austin", "Raj Patel", …
A2:C1110 rows × 3 cols: "Ana Ruiz", "ana@ruiz.example", "Austin", "Raj Patel", …A range: 10 rows × 3 columns.
UNIQUE(A2:C11){7 rows × 3 cols} "Ana Ruiz", "ana@ruiz.example", "Austin", "Raj Patel", …UNIQUE: Distinct rows of a range. Spills.- array
A2:C1110 rows × 3 cols: "Ana Ruiz", "ana@ruiz.example", "Austin", "Raj Patel", …
- array
In Google Sheets
The same formula works in Google Sheets: =UNIQUE(A2:C11). We ran it in Google Sheets mode on the same data and it returns the same results, spilling 7 rows.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SORT(UNIQUE(A2:C11)) | spills 7 × 3Different result | Sort the clean list by name at the same time. Sorted A to Z by name. |
| =UNIQUE(A2:B11) | spills 6 × 2Different result | 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.
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.
Remove duplicate rows with a formula
E2, return the contact list from A2:C11 with the repeated rows removed.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.
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.