To permanently remove duplicate rows in Excel, select your range or table, choose Data > Remove Duplicates, and select the columns that define a duplicate. Excel keeps the first matching row and deletes the others, so make a copy first. If you only want to find duplicates or create a unique list without changing the source, use Conditional Formatting, Advanced Filter, or the UNIQUE function instead.
Choose what Excel should treat as a duplicate
Excel compares the values in the column or columns you select. That choice determines which entire rows are removed:
- One selected column: rows with the same value in that column match, even if their other fields differ. For example, selecting only Email can remove one of two rows with the same email but different names or dates.
- Several selected columns: rows match only when the selected fields have the same combination of values. Selecting Name and Email, for example, uses both fields to identify a duplicate.
Remove Duplicates deletes the whole matching row, not just the values in the selected columns. If one record should be retained over another, sort or arrange the data so the preferred record appears first: Excel keeps the first occurrence.
Remove duplicate rows permanently
- Make a copy. Copy the source range to another worksheet or workbook, or save a separate copy of the workbook. Microsoft recommends preserving the original because removal deletes data.
- Select the data. Select the range to clean, or activate a cell in the table.
- Open the removal tool. Choose Data > Remove Duplicates.
- Set the comparison fields. In the Columns list, select every column that should jointly define a duplicate. Select just one when that single field defines a match. Confirm that the selection reflects your intended rule for the entire row.
- Run and check the result. Choose OK and review Excel’s removal count. Compare the cleaned data with your copy, or use Undo immediately if you chose the wrong columns.
Microsoft advises removing outlines or subtotals before removing duplicates. Its support guidance also notes that counts can include empty cells or spaces, so inspect unexpected results rather than assuming the count represents only the records you intended to remove. See Microsoft’s instructions for finding and removing duplicates.
Windows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallOutdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware match#1 Best Overall
- The Microsoft Office 365 Bible: The Most Updated and Complete Guide to Excel, Word, PowerPoint, Outlook, OneNote, OneDrive, Teams, Access, and Publisher from Beginners to Advanced
- ABIS BOOK
Find duplicates before deleting anything
To mark repeated values for review, select the relevant cells and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Excel highlights duplicates so you can inspect them before deciding which records to keep. The rule highlights based on the cells to which it is applied; it does not remove rows.
This is useful when duplicate status depends on context, such as deciding whether two records with a shared email address are actually redundant. Review the other fields and choose the record to retain before using the removal command.
Show or copy unique records without deleting the source
Advanced Filter can display unique records in the original list or copy them to another location. Select the range or activate the table, then choose Data > Advanced. Select Filter the list, in-place to filter the displayed list, or Copy to another location to create a separate result. Check Unique records only and apply the filter. This filters or extracts records; it does not delete the source rows. Microsoft documents this option in its guidance on filtering for unique values or removing duplicate values.
Generate a live unique list with UNIQUE
In a version of Excel that supports dynamic arrays, enter =UNIQUE(A2:A100) in an empty cell to return a distinct list from that range. Excel spills the result into neighboring cells. To return distinct full rows, pass a multi-column range to the function instead of a single column.
For an Excel Table, a structured reference can make the formula’s source expand as table rows are added or removed. The UNIQUE function is listed for Microsoft 365 and Excel 2021 and 2024 on Windows, Mac, and mobile platforms; see Microsoft’s UNIQUE function reference.
To return only values that occur exactly once, use =UNIQUE(A2:A100,,TRUE). This is different from ordinary deduplication: it excludes every value that appears more than once rather than keeping one copy of each repeated value.
Rank #4
Use Power Query for a repeatable cleanup
If you need to repeat the same data-preparation steps, use Power Query. In Power Query Editor, select one or more column headers and choose Home > Remove Rows > Remove Duplicates. To isolate repeated rows instead, choose Keep Rows > Keep Duplicates. The selected columns determine which records match. Microsoft lists these Power Query commands for Microsoft 365 and Excel 2016, 2019, 2021, and 2024. See Microsoft’s Power Query instructions for keeping or removing duplicate rows.
Which Excel method should you use?
| Goal | Method | Effect on source data | Version information |
|---|---|---|---|
| Permanently clean a range or table | Data > Remove Duplicates | Deletes matching rows and keeps the first occurrence | Microsoft lists Excel 2016 and later |
| Inspect repeated entries first | Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values | Highlights values for review; does not delete rows | Microsoft’s guidance covers classic Excel versions |
| Filter or copy unique records | Data > Advanced; select Unique records only | Filters the list or copies results without deleting the source | See Microsoft’s unique-value filtering guidance |
| Create a live distinct output | UNIQUE formula |
Returns a separate spilled result | Microsoft lists Microsoft 365 and Excel 2021 and 2024 on Windows, Mac, and mobile |
| Repeat data-shaping steps | Power Query Remove Duplicates or Keep Duplicates | Applies a transformation in the query | Microsoft lists Microsoft 365 and Excel 2016, 2019, 2021, and 2024 |
Why Excel may not identify records as duplicates
A duplicate depends on the values in the selected cells, not necessarily on how the records look or what they mean in your workflow. Microsoft notes that dates that display differently can be treated as unique. Spaces or empty cells can also affect results or counts. If the result is unexpected, check the selected fields and the underlying values, then test again on a copy. Menu placement can vary by platform or interface release.
Free tools Windows power users keep installed
One-click scans. No signup required.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
