Microsoft Excel: How to Remove Duplicate Rows Safely

In Microsoft Excel for Microsoft 365 on Windows, select your data, then choose Data > Remove Duplicates, select the columns that define a duplicate, and click OK. Excel keeps the first matching record in the current order and removes later matches, so make a copy of the data first if you may need to restore it.

How Excel defines a duplicate

Excel checks the values in the columns you select. If you select several columns, a row is considered a duplicate only when its values match the values in all of those selected columns. For example, selecting First Name and Last Name treats two people named Jordan Lee as duplicates even if they have different email addresses. Adding Email to the selection means the three selected fields must match together.

This choice matters because Excel removes entire rows, not just repeated cells. If you select only Email, and two records have the same email but different names or purchase details, Excel removes one whole record. Choose fields that identify the record you actually mean to deduplicate, rather than selecting a column simply because it contains repeated values.

Excel preserves the first occurrence it encounters and removes later ones. The order of the rows therefore affects which record remains. If you want to keep the newest record, for example, sort by date with the newest date first before removing duplicates. Check the sort and the key fields carefully before you confirm.

Prepare the data before removing duplicates

Duplicate removal is destructive to the selected data: it deletes rows from the range or table. Before using it, save a copy of the workbook, duplicate the worksheet, or copy the source data to another sheet. This is especially useful when the data came from a customer list, a report, or an import that would be difficult to recreate.

Also check that your dataset is organized as one continuous block with a clear header row. Remove or account for title lines and blank rows above the headings. If the data has unrelated content immediately beside it, select the intended range explicitly so Excel does not include that content by mistake. Excel tables are often easier to work with because their headers and columns are clearly identified.

Confirm which fields together identify one record, such as Order ID or Email plus Account ID.
Decide which matching record should remain, then sort the data if necessary.
Check that headings are present and that the header option will be set correctly.
Save a backup before making a change you cannot easily reconstruct.

Steps to remove duplicates in Excel

Click a cell inside the data range or Excel table. If the data is not a clearly bounded block, select the full range you intend to clean.
On the ribbon, choose Data > Remove Duplicates. Excel opens a dialog showing the available columns.
If your first row contains headings, make sure My data has headers is checked. If the first row is actual data, clear that option so Excel does not treat it as a header.
Select the columns that should define a duplicate. Use Select All if an entire row must match, or clear the selection and check only the relevant fields.
Click OK. Excel reports how many duplicate values were removed and how many unique values remain. Review the resulting data rather than assuming the count proves the selection was right.

If you use a table, click inside it before opening the command so Excel works with the table. You can also select a specific range first. When Excel asks whether to expand the selection, include adjacent columns if they belong to the same records; otherwise, removing rows from only one column could misalign it with related data.

Choose the right columns: examples

Consider a contact list with Name, Email, and City. If the goal is one record per email address, select Email only. If you select all three fields, two rows with the same email but different city values will remain, even though they may represent the same contact. Conversely, two people who share a family email could be incorrectly reduced to one row if Email alone is the key. The appropriate columns depend on what makes a record unique in the source system.

For sales data, an order may have several line items. Removing duplicates based on Customer alone could delete valid purchases. An Order ID and Product ID combination may be a better key if each product should appear once per order. For an event list, a combination of Email and Event Name might identify a registration, while Email alone would wrongly collapse registrations for different events.

When every field in a row must match, select all columns. That removes exact duplicate records while retaining rows that differ in any selected field. When only some fields define a duplicate, select those fields intentionally, knowing that Excel still removes the entire later row, including values in columns you did not select.

Check what changed and undo if needed

After completion, compare the reported number of removed rows with what you expected. Scan the remaining records for cases that should have been kept, and check that associated values such as dates, amounts, or status fields stayed with the correct person or order. A quick count is not enough if the wrong columns were selected: Excel can successfully remove duplicates according to a rule that was not the rule you intended.

If the result is wrong and the operation is still in the undo history, use Ctrl+Z on Windows or Command+Z on Mac. Undo immediately, before making other edits. If the workbook has been closed or the undo history is no longer available, restore the backup or source data. The command is not a preview, and saving should not be treated as a substitute for a backup.

One useful precaution is to copy the original range to a new worksheet and run the command on the copy. You can then compare the cleaned version with the original and keep both. This takes more space, but it makes the decision about which records to retain easier to audit.

Alternatives that preserve the source

If you want a distinct list without deleting source rows, use the UNIQUE function in a current version of Excel. With source records in A2:C100, enter =UNIQUE(A2:C100) in an empty cell to return distinct rows from those three columns. In Microsoft 365 and Excel 2021 or later, the results spill into neighboring cells and update when the source changes. This formula leaves the original records intact, but it returns unique combinations across the supplied range; it does not automatically keep a full record based on just one chosen column.

For a one-column list, with names in A2:A100, =UNIQUE(A2:A100) returns the distinct names. The formula result is a separate output, not a permanent cleanup of the source. If the list needs to be sent as fixed values, copy the results and use Paste Special > Values in the destination. Ensure the spill area is empty or Excel will show a spill error.

Another built-in option in desktop Excel is Advanced Filter, which can copy unique records to a different location. Select the list, choose Data > Advanced, choose Copy to another location, specify the destination, and check Unique records only. It is useful when you want a separate result while leaving the original list untouched. For a repeatable cleanup that needs to run on refreshed data, Power Query is another option; it can remove duplicates in a query and load the transformed result separately, though the exact key columns still need to be chosen correctly.

Common problems and how to avoid them

Rows that look alike remain: A selected field may contain a hidden space, a different spelling, or a genuinely different value. Clean or standardize the source first, and compare the exact columns used as the key.
Too many rows disappear: The selected columns may not uniquely identify a record. Undo the command and use a more specific combination, such as Email plus Event ID instead of Email alone.
The wrong row is retained: Excel keeps the first matching occurrence in the current order. Sort the data to put the preferred record first, then run the command again on a restored copy.
A heading is treated as data: Set My data has headers correctly. A mistaken setting can make the first row part of the duplicate comparison.
Only one column is cleaned: Selecting a single column in a larger dataset can prompt Excel to expand the selection. Keep related columns together so entire records remain aligned.

Repeated blank values can also count as duplicates when the blank-containing columns are part of the selected key. If a blank means unknown rather than the same real-world identity, do not use that field alone to decide that records are duplicates. Resolve or review incomplete records separately.

Excel for Mac and Excel for the web

In Excel for Mac, the same general command is available from the Data tab as Remove Duplicates. The dialog lets you identify header rows and choose columns; make a backup and review the selected fields just as you would on Windows. Use Command+Z to undo rather than the Windows Ctrl+Z shortcut.

In Excel for the web, look for Remove Duplicates on the Data tab. The ribbon layout can vary, so the command may be grouped or displayed differently from the Windows desktop app. If it is not available in your current view or workbook, open the file in the desktop application. Advanced Filter and some other data-management features may also have different availability in the web app.

Frequently Asked Questions

Does Remove Duplicates delete the whole row?
Yes. Excel removes later matching rows from the selected range or table; it does not merely clear the repeated cells. Select all related columns so the remaining records stay together.

How do I remove duplicates based on one column but keep the other data?
Select the one column that defines a duplicate, but understand that Excel removes the entire later row when that value repeats. Sort first if you need a particular record to remain, and make a backup before proceeding.

Can I highlight duplicates without deleting them?
Yes. Select the cells to inspect, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. This highlights repeated values for review; it does not remove rows or necessarily identify duplicate records across several fields.

Does Excel keep the first or last duplicate?
Remove Duplicates keeps the first occurrence in the current order and removes later ones. Sort the data before running the command if the record you want to keep is not currently first.

Can I remove duplicates without changing the original list?
Yes. Use UNIQUE in Microsoft 365 or Excel 2021 and later to create a separate distinct result, or use Advanced Filter to copy unique records to another location in desktop Excel. Both approaches preserve the source list.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.