Excel provides five built-in methods for removing duplicate data, and the right choice depends on whether you need to delete, highlight, or extract unique records.
A single duplicate entry can throw off a pivot table, inflate a sum, or hide real trends in your data — and how to eliminate duplicate values in Excel comes down to picking the right tool from five built-in options. Each method handles the job differently, and the one you want depends on whether you’re cleaning a one-time import, building a repeatable report, or just scouting for problems before they spread.
Eliminating Duplicate Values in Excel: Which Tool Fits Your Task
Excel divides duplicate handling into two camps: tools that modify your original data and tools that leave it untouched. The table below shows each method at a glance so you can pick the right one without trial and error.
| Method | Changes Original Data? | Best For |
|---|---|---|
| Remove Duplicates | Yes — deletes rows in place | Permanent cleanup of a full dataset |
| Advanced Filter | No — extracts to a new location | Getting a unique copy without modifying the source |
| Conditional Formatting | No — highlights only | Finding duplicates before deciding what to delete |
| UNIQUE Function | No — spills results into a new range | A dynamic list that updates when source data changes |
| Power Query | No — outputs a deduplicated table | Recurring cleanup workflows on large datasets |
Remove Duplicates — The One-Click Delete
The fastest way to permanently remove duplicate rows is to use the Remove Duplicates command in the Data tab. Microsoft documents this as the primary in-place method, and it shows a summary of how many duplicates were removed and how many unique values remain.
Select the range or ensure the active cell is inside a table, then go to Data > Remove Duplicates in the Data Tools group. In the dialog, check the columns you want Excel to evaluate — if two rows match on every selected column, one gets deleted. Click OK, and Excel reports the count of removed duplicates and remaining unique values. An official Microsoft support page spells out each step.
If you accidentally delete the wrong rows, press Ctrl+Z or the Undo button immediately to restore them. Microsoft also recommends copying the original data to another worksheet before running Remove Duplicates so you don’t lose information permanently.
A dialog box confirms the number of duplicate values removed and how many unique values remain in the range.
Advanced Filter — Extract Uniques to a New Location
When you need a clean copy of unique values without touching the original data, the Advanced Filter is the tool. It sends the result to a different area of the worksheet, leaving the source completely unchanged.
Select the range or table, then go to Data > Advanced in the Sort & Filter group. In the dialog, choose Copy to another location, set the Copy to box to a blank cell where you want the unique list to start, then check Unique records only. Click OK, and Excel outputs only the unique rows to the target range.
A filtered list of unique rows appears starting in the cell you specified in Copy to, with no changes to the original data.
Conditional Formatting — Spot Duplicates Before You Delete
If you’re not sure which rows are duplicated, use highlighting to see them first. Microsoft’s conditional-formatting rule colors every duplicate cell so you can review before taking action.
Select the cells you want to check, then go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Pick a formatting style — the default light-red fill works well — and click OK. Every value that appears more than once gets highlighted.
After reviewing, you can delete the highlighted rows manually, use the Remove Duplicates command with confidence, or undo the formatting by going back to Conditional Formatting > Clear Rules > Clear Rules from Selected Cells.
Duplicate cells appear in the formatting color you selected, letting you see at a glance which entries need attention.
UNIQUE Function — Dynamic Results That Update Automatically
The UNIQUE function spills a list of unique values into a new range and recalculates whenever the source data changes. It is a modern dynamic-array formula available in Excel 365 and Excel 2021 or later.
In an empty cell, type =UNIQUE(range). For example, =UNIQUE(A2:C14) returns every unique row from that range. The results “spill” into the cells below and to the right automatically, expanding or shrinking as the source changes.
Compatibility note: If your Excel version does not support dynamic arrays — Excel 2019 and earlier — this function will not work. In that case, use Advanced Filter or Remove Duplicates instead.
Unique rows appear in a contiguous block starting at the formula cell, with no gaps or errors.
Power Query — Repeatable Deduplication for Large Datasets
For datasets you clean on a recurring basis, Power Query offers a repeatable workflow. Once you set up the steps, you can refresh the query later to apply the same deduplication to new data.
Select the range, then go to Data > From Table/Range in the Get & Transform Data group. In the Power Query Editor, click Home > Remove Rows > Remove Duplicates. Power Query removes rows that repeat based on all visible columns. Then go to Home > Close & Load to output the deduplicated table back to the worksheet.
Any future data added to the source table can be refreshed by right-clicking the output table and selecting Refresh.
A new table appears in your worksheet with only unique rows, and the Power Query pane shows the applied steps.
Which Method Should You Use?
Your goal decides the tool. Remove Duplicates is the fastest when you’re certain about what to delete. Advanced Filter and the UNIQUE function are better when the original data must stay untouched. Conditional Formatting helps you inspect before acting. Power Query is the right call for any dataset you clean more than once.
| Goal | Recommended Method |
|---|---|
| Permanently clean a dataset quickly | Remove Duplicates |
| Extract uniques without changing source | Advanced Filter or UNIQUE |
| Review duplicates before deleting | Conditional Formatting |
| Automate a recurring cleanup | Power Query |
| Keep results that update automatically | UNIQUE Function |
Common Pitfalls and How to Avoid Them
- Selecting only part of the data: When you choose only one column in the Remove Duplicates dialog, Excel removes rows where that column repeats — even if other columns differ. Always select the full range or all relevant columns.
- Forgetting headers: The data you select should include headers. If the header row is missing, Excel may treat it as data and possibly delete it.
- Deleting without a backup: Copy the original data to another sheet before running Remove Duplicates. Microsoft explicitly advises this, and it takes seconds.
- Using the wrong tool for the job: Remove Duplicates deletes rows permanently. Conditional Formatting only highlights them. Mixing these up can cost you data.
- Expecting a formula to physically delete rows: No formula removes rows from the source. The UNIQUE function returns unique values to a new range but leaves the original data untouched.
Start with a backup copy of your data. Then match the method above to your task — and Excel handles the rest in a few clicks.
References & Sources
- Microsoft Support. “Find and Remove Duplicates.” Covers Remove Duplicates and Conditional Formatting steps.
- Microsoft Support. “Filter for Unique Values or Remove Duplicate Values.” Covers Advanced Filter and Remove Duplicates documentation.