How to Remove Duplicates in Excel (and Google Sheets)
Removing duplicate rows is one of the most common data‑cleaning tasks. Whether you prefer a quick UI command, a formula‑driven approach, or a more powerful query, this guide shows you how to get a clean list while preserving data integrity.
1. Using the Built‑In Remove Duplicates Feature
The built‑in command works in Excel for Windows, macOS, and Excel on the web (Office 365/2021 and later). Google Sheets offers a comparable tool under Data → Data cleanup → Remove duplicates; the steps are almost identical, only the dialog layout differs.
Step‑by‑step (Excel)
| Step | Action |
|---|---|
| 1 | Select the range you want to de‑duplicate. Include the header row if your list has one. |
| 2 | Data ► Remove Duplicates (in the Data Tools group). |
| 3 | In the dialog, tick My data has headers (if applicable) and choose the columns that define a duplicate. Leaving all columns checked removes rows that are identical across the whole row. |
| 4 | Click OK. Excel reports how many duplicate rows were removed and how many unique rows remain. |
Sample data before and after
| ID | Name | Department |
|---|---|---|
| 1 | Alice | Sales |
| 2 | Bob | Marketing |
| 1 | Alice | Sales |
| 3 | Carol | HR |
| 2 | Bob | Marketing |
After “Remove Duplicates” (all columns selected)
| ID | Name | Department |
|---|---|---|
| 1 | Alice | Sales |
| 2 | Bob | Marketing |
| 3 | Carol | HR |
Quick tips & gotchas
| Tip | Why it matters |
|---|---|
| Backup first | The command deletes rows permanently; copy the sheet or use Ctrl+Z immediately if you need to revert. |
| Partial column check | Unchecking columns lets you keep the first occurrence of a value (e.g., unique IDs) even if other fields differ. |
| Headers | Always tick My data has headers when your selection includes a header row; otherwise the header may be removed as a duplicate. |
2. Formula‑Based De‑duplication (No Data Loss)
When you need a duplicate‑free list without altering the source data, formulas are the way to go. The methods below work in Excel 365/2021 (or later) and Google Sheets.
2.1 Helper‑column method
| A (Name) | B (Score) | C (Helper) |
|---|---|---|
| Alice | 85 | |
| Bob | 92 | |
| Alice | 85 | |
| Carol | 78 | |
| Bob | 92 |
Excel (365/2021)
=IF(COUNTIFS($A$2:A2, A2, $B$2:B2, B2)=1, 1, 0)
Google Sheets
=IF(COUNTIF($A$2:A2 & "|" & $B$2:B2, A2 & "|" & B2)=1, 1, 0)
Result in column C: 1, 1, 0, 1, 0.
Now pull the unique rows:
Excel
=FILTER(A2:B6, C2:C6=1, "No data")
Google Sheets
=FILTER(A2:B6, C2:C6=1)
| Name | Score |
|---|---|
| Alice | 85 |
| Bob | 92 |
| Carol | 78 |
2.2 One‑formula solution (no helper column)
Excel (365/2021)
=UNIQUE(SORT(A2:B6, 1, 1))
Google Sheets
=UNIQUE(A2:B6)
UNIQUE removes duplicate rows; SORT (optional) orders the result.
3. Advanced Deduplication with Power Query
Power Query (Get & Transform) is ideal for large tables, repeatable workflows, or deduplication based on a computed key. The original worksheet stays untouched, and you can refresh the query whenever the source data changes.
When to choose Power Query
| Situation | Benefit |
|---|---|
| > 10,000 rows | Faster processing than the UI command. |
| Multiple tables need the same logic | Create one query and reuse it across workbooks. |
| You want an audit trail | Every step is recorded in the Applied Steps pane. |
| Deduplication on a calculated column | Create the column in the query, then remove duplicates on it. |
Step‑by‑step (Excel 365 / 2019 and later)
- Load data – select any cell in the table → Data ► From Table/Range.
- (Optional) Create a deduplication key – add a custom column:
m
= Table.AddColumn(Source, "DedupKey", each Text.Combine({[FirstName], [LastName], Text.From([Date])}, "|"))
3. Remove duplicates – select the column(s) (or the custom key), right‑click ► Remove Duplicates. The first occurrence of each key is kept.
4. Load back – Home ► Close & Load ► Close & Load To… and choose a destination table.
Sample data and result
| FirstName | LastName | Date |
|---|---|---|
| Alice | Smith | 2024‑01‑01 |
| Bob | Jones | 2024‑01‑02 |
| Alice | Smith | 2024‑01‑01 |
| Carol | Lee | 2024‑01‑03 |
After the query removes duplicates on the DedupKey you get three unique rows.
4. Clean the Data First
Minor inconsistencies (extra spaces, different case, hidden characters) can make identical rows appear unique. Use these simple formulas (they work in both Excel 2016+ and Google Sheets) before you run any de‑duplication step.
| Issue | Formula | Example |
|---|---|---|
| Leading/trailing spaces | =TRIM(A2) |
" Apple " → "Apple" |
| Inconsistent case (optional) | =UPPER(TRIM(A2)) or =LOWER(TRIM(A2)) |
"BaNaNa" → "BANANA" |
| Non‑printing characters | =CLEAN(TRIM(A2)) |
"Cherry"&CHAR(160) → "Cherry" |
| Combine columns for a key | =TEXTJOIN("|",TRUE,TRIM(B2),TRIM(C2),TRIM(D2)) |
"John","Doe","NY" → "John|Doe|NY" |
| Numbers stored as text | =VALUE(TRIM(A2)) |
" 123 " → 123 (numeric) |
Quick helper column (single column clean‑up):
=UPPER(CLEAN(TRIM(A2)))
Copy the formula down, then Copy → Paste Values over the original column or keep the helper column for later reference.
5. Best Practices for Data Integrity
| Stage | Action | Reason |
|---|---|---|
| Preparation | Duplicate the sheet or create a backup copy. | Allows you to revert if the wrong rows are removed. |
Convert the range to an Excel Table (Ctrl+T). |
Tables expand automatically and keep formulas consistent. | |
Add a stable identifier (e.g., =A2 & "|" & B2). |
Makes it easy to trace which rows were removed. | |
| Execution | Use Remove Duplicates with explicit column selection. | Prevents loss of distinct records that share a common field. |
| Apply filters first to limit the operation to a specific subset (e.g., a region). | Avoids unintended global changes. | |
| Verification | Flag potential duplicates before deletion: =IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2)>1,"Duplicate","Unique") |
Lets you review which rows will be removed. |
Count duplicates: =ROWS(Table1)-ROWS(UNIQUE(Table1[KeyColumn])) (Excel 365/2021) or =COUNTUNIQUE (Sheets). |
Confirms the exact number identified. | |
| Post‑Cleanup | Refresh any dependent pivot tables or formulas. | Prevents #REF! errors. |
| Re‑apply data validation rules. | Guarantees new entries follow the same standards. | |
| Log the operation (date, user, rows removed, criteria). | Provides an audit trail for compliance or future review. |
Quick Checklist
- ☐ Backup the worksheet.
- ☐ Convert data to a Table.
- ☐ Clean whitespace, case, and hidden characters.
- ☐ Decide which columns define a duplicate.
- ☐ Run the chosen de‑duplication method.
- ☐ Verify the result with a count or flag column.
- ☐ Refresh dependent objects and document the change.
FAQ
How do I remove duplicate rows in Excel?
Select the range, go to Data ► Remove Duplicates, tick My data has headers (if needed), choose the columns that determine uniqueness, and click OK. Excel will keep the first occurrence of each unique row and delete the rest.
Can I keep the first or last occurrence when removing duplicates?
The built‑in command always keeps the first occurrence. To keep the last occurrence, sort the data so the desired row appears first, then run Remove Duplicates. Alternatively, use a helper column with COUNTIFS (Excel) or COUNTIF (Sheets) to flag the last record and filter it.
What if I need to remove duplicates based on multiple columns?
In the Remove Duplicates dialog, check each column that should be part of the uniqueness test. Excel will treat a row as a duplicate only when all selected columns match another row. The same principle applies to the UNIQUE function (UNIQUE(A2:C100)) and to Power Query where you select multiple columns before clicking Remove Duplicates.
Is there a way to undo duplicate removal in Excel?
Immediately after the operation you can press Ctrl+Z (or click the Undo button) to revert the changes. If you saved a backup copy before de‑duplicating, you can also restore that version later.
How does Power Query help with duplicate removal?
Power Query performs deduplication in its own engine, leaving the original worksheet untouched. It can handle very large tables, apply the same logic to multiple data sources, and keep a step‑by‑step record that you can edit or refresh whenever the source data changes. This makes it ideal for repeatable, auditable cleaning processes.