
3 Ways to Remove Duplicates in Excel: Remove Duplicates, UNIQUE, and Power Query
Removing duplicates in Excel comes down to three methods: the fastest option, Remove Duplicates; the UNIQUE function, which preserves the source data; and Power Query, which is ideal for large datasets and reusable workflows. Follow the steps below to clean up your work data right away.
Quick Fix: Remove Duplicates in 20 Seconds
- Select the range (or select the entire table: create a table with Ctrl+T, then click any cell).
- Select Data > Remove Duplicates.
- Select the column(s) to use as duplicate criteria and click OK.
- Review the results summary, including the number removed and remaining unique values.
Method 1. Remove Duplicates (Deletes Data) — Including Multiple Columns
- The fastest method. Because it deletes actual data, consider making a backup or using Undo before running it.
- When you select multiple columns, only rows with the same combination of columns are considered duplicates.
- To highlight duplicates instead of deleting them: Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
Method 2. UNIQUE Function (Non-Destructive) — Unique Values or Values That Appear Exactly Once
=UNIQUE(range, [by_col], [exactly_once])
- Unique values in one column:
=UNIQUE(B2:B1000) - Unique rows across multiple columns:
=UNIQUE(A2:D1000)(compares by row) - Values that appear exactly once:
=UNIQUE(A2:A1000,,TRUE) - Preserves the source data and expands automatically as a dynamic array.
Method 3. Power Query (Large Datasets and Reuse) — A Repeatable Workflow
- Use Data > Get & Transform Data to load the range as a query.
- In the Power Query Editor, select the key column(s), then choose Home > Remove Rows > Remove Duplicates.
- Select Close & Load to output the results as a table. You can Refresh it with new data.
If you need to preserve the order, add a sort step before removing duplicates.
Practical Example & Sample Data
| Customer ID | Order Date | Item | Quantity |
|---|---|---|---|
| C001 | 2025-01-03 | T-Shirt | 2 |
| C001 | 2025-01-03 | T-Shirt | 2 |
| C002 | 2025-01-04 | Pants | 1 |
| C003 | 2025-01-04 | T-Shirt | 3 |
| C002 | 2025-01-04 | Pants | 1 |
Unique Customers in One Column
=UNIQUE(A2:A6)
Values That Appear Exactly Once (exactly_once)
=UNIQUE(A2:A6,,TRUE)
Remove Complete Duplicates Based on Customer ID + Order Date
- Remove Duplicates: Select <Customer ID> and <Order Date>, then run the command.
- UNIQUE:
=UNIQUE(A2:D6) - Power Query: Select the two columns → Remove Duplicates.
Alternative Methods & Notes
- Advanced Filter → Unique records only: Copies results to another location as a static result. Run it again when data needs updating.
- The AutoFilter drop-down displays only up to 10,000 unique items. Use another method for a large number of unique values.
- Power Query does not preserve sort order when removing duplicates. Add a sort step before removing duplicates.
Troubleshooting
| Issue | Cause | Solution |
|---|---|---|
| Some duplicates remain | Incorrect key columns selected | Reset the criteria using a combination of multiple columns, or use UNIQUE with multiple columns. |
| The unique list does not update | Static copy created with Advanced Filter | Use UNIQUE or refresh Power Query. |
| exactly_once produces different results | The third argument is not specified | Use =UNIQUE(range,,TRUE). |
| Results are in an unexpected order | No sort order specified in Power Query | Add a sort step before removing duplicates. |
Related Articles
- Complete Guide to the IF Function
- VLOOKUP vs. XLOOKUP Comparison
- Quick Guide to Conditional Sums with SUMIFS
- Manage Large Datasets with Power Query
- Split Text with TEXTSPLIT
Pingback: Keep Only the Latest Record for Each ID in Excel (Without Deleting History)