To remove duplicate rows from an Excel table, click a cell in the table, choose Data > Remove Duplicates, select the columns that define a duplicate, and click OK. Excel keeps the first matching row and deletes the later matches, so save a copy or be ready to use Undo if the result is not what you intended.
What Excel means by a duplicate
Excel does not decide that two records are duplicates just because they look similar as complete rows. In the Remove Duplicates dialog, the selected columns define the comparison. When every table column is selected, Excel removes rows whose values match across all those columns. When only some columns are selected, rows with matching values in those columns count as duplicates even if other columns differ.
For example, a customer table might contain Customer ID, Name, Email, and Region. If you select only Email, Excel keeps one row per email address. Two rows with the same email but different regions will be treated as duplicates, and one whole row will be removed. If you select all four columns, those records will remain because the Region values differ. The right choice depends on what makes a record unique in your data, not on which columns happen to be easiest to select.
Excel keeps the first occurrence it encounters in the current table order. “First” does not mean newest, most complete, or most accurate. If a particular record should survive, sort the table into the desired order before deduplicating, or use a review method that lets you choose deliberately.
Prepare the table before removing rows
Make a backup before deleting records, especially if the workbook is shared, contains important history, or will be used for reporting. You can use File > Save a Copy or save another version of the workbook. Remove Duplicates changes the table itself; it is not just a filter that hides repeated entries.
Before opening the dialog, check what counts as a valid record. A duplicate customer might mean a repeated customer ID, while a duplicate transaction might require matching both an account number and a transaction date. Decide whether blank cells should count as matching values, and whether differences in spelling, spacing, or punctuation are meaningful. A repeated product name may be intentional if the rows represent separate orders.
For a clearer review, consider adding a temporary helper column or sorting by the field that identifies the preferred row. Clean obvious inconsistencies first: an extra space at the end of an email address, for example, can make two entries appear alike while their contents differ. Similarly, a number stored as text may not behave like a numeric value in other spreadsheet operations. Do not assume that visual similarity alone proves that Excel will identify two cells as matching.
Remove duplicates from an Excel table
- Click any cell inside the table. If you select a cell in an actual Excel table, Excel can use the table’s full data area. Confirm that the table has headers and that each column has a clear heading.
- On the ribbon, choose Data > Remove Duplicates. In the dialog, make sure My data has headers is selected when the first row contains column names. Excel generally recognizes table headers, but check the setting before proceeding.
- Under Columns, select the fields Excel should compare. Select all columns to remove only fully identical records. Clear some selections when a smaller set of fields defines a duplicate.
- Click OK. Excel reports how many duplicate values were found and removed, and how many unique values remain. Check the remaining table before saving or sharing the workbook.
If you selected a cell range rather than clicking inside a formatted table, Excel may ask whether to expand the selection or continue with the current selection. Expanding is usually appropriate when you want to remove entire records: it keeps each row’s related fields together. Removing duplicates from just one column while leaving neighboring data in place can misalign values and damage the meaning of the records.
How to choose the comparison columns
Think of the selected columns as a definition of identity. Selecting more columns makes the match stricter; selecting fewer makes it broader. If you select only a person’s name, two different people with the same name may be merged. If you select name, address, and phone number, a spelling difference in the address may prevent a match that you meant to catch.
Use a stable identifier when one exists, such as a customer number or product code. For records that have no single identifier, a combination can work: for example, Account ID plus Invoice Number, or Email plus Signup Date. Verify that the combination is unique in the real-world context. A date alone is rarely enough to identify an order, since many orders can share a date.
Here is a practical illustration. Suppose the rows below share the same email address, but one says “North” and another says “South” in the Region column. Selecting Email alone tells Excel to keep just one of those records. Selecting Email and Region preserves both, because the pairs differ. Selecting every column also preserves both if any other field differs. Decide whether Region is a conflict to resolve or a meaningful part of the record before choosing.
What gets kept, and what gets deleted
Remove Duplicates deletes later matching rows from the table; it does not combine information from them. If one row has a phone number and another has a mailing address, removing one as a duplicate will not merge those fields into the surviving row. Review conflicting records first and consolidate needed information manually or through a separate data-cleaning process.
The first matching row in the current order is retained. Therefore, sort before deduplication if the preferred record can be placed first. For example, if you want the row with the latest update date to remain, sort the update-date column from newest to oldest, then remove duplicates using the identifier columns. Confirm the sort direction and selected comparison fields carefully: including the update date in the duplicate definition would make different dates count as different records.
The operation affects the whole matching row, not just the selected comparison cells. Other columns are not used to decide whether rows match unless selected, but their values go with the row that survives. This is the most important reason to avoid selecting a narrow portion of a dataset when the intention is to clean complete records.
Check the result and undo mistakes
After Excel displays its removal summary, inspect the remaining records rather than treating the count as proof that the right rows were deleted. Look at several examples around known duplicates, check the table row count, and verify that key totals or identifiers still make sense. If you were deduplicating a customer list, search for a few customers you expected to remain and confirm their associated details.
If the result is wrong and you have not made other changes since, use Ctrl+Z on Windows to undo the removal. On a Mac, use Command+Z. Undo is useful for an immediate correction, but it is not a substitute for a backup: once later actions complicate the undo history, restoring the original data may be harder. If you discover an error after saving or closing, use the backup or the workbook’s available version history rather than assuming Excel can reconstruct deleted rows.
Alternatives for a safer or repeatable workflow
For a non-destructive list of unique rows in Microsoft 365 or Excel 2021, use the UNIQUE function. If the data is in an Excel table named Table1, enter =UNIQUE(Table1) in a blank cell outside the table. The formula returns the distinct rows as a spill range and leaves the source table unchanged. The result updates when the source data changes, which is useful for reports, but it is an output rather than a replacement for cleaning the source. A spilled formula also cannot expand into cells that already contain data.
Conditional formatting can help you inspect repeated values before deleting anything. Select the relevant cells and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. This highlights repeats in the selected range; it does not delete rows or resolve conflicts. It is useful when you need to review which records match before choosing the survivor.
For recurring imports or larger cleanup tasks, Power Query can provide a repeatable process. In Excel for Windows, select a cell in the source table and choose Data > From Table/Range. In Power Query Editor, select the comparison columns, then choose Home > Remove Rows > Remove Duplicates. Use Close & Load to return the result to Excel. Power Query keeps the source data separate from the transformed output, and you can refresh the query after updating the source. If records have conflicting details, decide how to handle those conflicts rather than assuming deduplication merges them. Power Query availability and some interface details vary by Excel platform and version.
Version and platform notes
The instructions above describe Microsoft Excel for Microsoft 365 on Windows. Excel for Mac also includes a Remove Duplicates command on the Data tab, although ribbon layout and dialog appearance can differ. The Mac undo shortcut is Command+Z rather than Ctrl+Z. In Excel for the web, the command is available in current versions, but the ribbon may be arranged differently; check the Data tab and verify the selected table and columns before confirming.
UNIQUE is available in Microsoft 365 and Excel 2021 or later, including supported Mac editions. It is not available in older perpetual versions such as Excel 2019. Power Query capabilities also differ by platform and release, so a workbook designed around a query should be checked in the Excel version used by its recipients. The basic Remove Duplicates command is the direct option when you want to change the table in place; the formula and query approaches are useful when preserving or refreshing the original data matters.
Frequently Asked Questions
Does Remove Duplicates delete the first copy? No. Excel retains the first matching record in the table’s current order and removes later matches. Sort beforehand if another record should be kept.
Can I remove duplicates based on just one column? Yes. In the Remove Duplicates dialog, clear the columns you do not want to compare. Excel still removes the entire row for each later match, so check that deleting its other field values is acceptable.
Can I remove duplicates without deleting the original data? Yes. Use UNIQUE in Microsoft 365 or Excel 2021 and place the formula outside the source table, or create a Power Query output. Both let you retain the original data while working with a distinct result.
Why did Excel keep two entries that look the same? Their contents may differ because of spaces, punctuation, text-versus-number storage, or other subtle inconsistencies. Check the selected comparison columns and inspect the underlying entries; matching appearance does not always mean matching cell values.
Can I undo Remove Duplicates? Usually, if it was the most recent action, press Ctrl+Z on Windows or Command+Z on Mac. If you have continued working or closed the workbook, recover from a saved copy or version history if available.
Related
- Excel Protected Sheet Message: Meaning, Causes and Fixes
- Excel Sheet Password Remover: Safe Ways to Unlock It
- Excel Protect Sheet: How to Select Unlocked Cells Only
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Insert an E-Signature in Word: Steps and OptionsTo insert an e-signature in Microsoft Word, add a picture of your handwritten signature with Insert > Pictures,…
- Schedule Email in Outlook: Send It LaterTo schedule an email in Outlook, write your message, open the menu beside Send, choose Schedule send, and…
- How to Combine Cells in Excel Into One Cell EasilyTo combine the contents of Excel cells into a new cell, join them with the ampersand operator (&)…
- Excel Repair Software Free Full Version: What Works?There is no legitimate universal “free full version” of Excel repair software that can guarantee recovery of every…
- Debt Snowball Spreadsheet UK: Build One in ExcelA UK debt snowball spreadsheet is a simple way to list debts from smallest to largest, keep up…
- Copy Data Validation to Other Cells in Microsoft ExcelTo copy data validation in Excel, copy a cell that already has the rule, select the destination cell…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.