Remove Duplicates in Excel: How It Works and What to Know

In Excel for Microsoft 365 on Windows, select your data and choose Data > Remove Duplicates. Excel compares the columns you select, keeps the first row for each matching combination, and removes later matching rows from the selected data—so choose the comparison columns carefully and keep a copy if you may need the original.

What Remove Duplicates Means in Excel

Excel’s Remove Duplicates command finds repeated records within a selected range or table. A record is one row of related values, such as a customer name, email address, and region. The command does not simply erase repeated words wherever they appear: it checks values in the columns you tell it to compare, then removes rows that match according to those columns.

That distinction matters because a row can repeat in one field without being a duplicate record. Two orders might have the same customer name but different order numbers. If you compare only the customer column, Excel treats those rows as duplicates. If you compare customer and order number, it keeps both when the order numbers differ.

The tool changes the selected data rather than creating a separate list of unique values. It is useful for cleaning an imported list or consolidating repeated entries, but it is not a preview-only operation. Think of it as a delete action with a comparison rule.

How Excel Decides Which Rows Match

In the Remove Duplicates dialog, each column in the selected range appears as a checkbox. Excel treats the checked columns together as a key. A later row is considered a duplicate when its values match an earlier row across every checked column. The first matching row stays in place; subsequent matches are removed from the selected list.

For example, consider a list containing these records:

Mira | Sales | East
Noah | Support | West
Mira | Sales | East
Mira | Sales | West

If all three columns are checked, the third record repeats the first and is removed. The fourth remains because its region differs. If only Name is checked, Excel keeps the first Mira record and removes both later Mira rows—even though one has a different region. The command does not combine information from duplicate rows or decide which version is more complete.

Order therefore affects which version survives. If the earliest row has an outdated email or incomplete details, Excel still keeps it unless you sort or otherwise arrange the records first. Decide which record should be the preferred one before removing duplicates.

Steps to Remove Duplicates Safely

Use this sequence in Excel for Microsoft 365 on Windows:

Make a backup copy of the worksheet or workbook, especially if the data is important or difficult to recreate.
Click a cell in the list, or select the complete range of records. Include every column whose row data must stay together.
Choose Data > Remove Duplicates.
If the first row contains labels, select My data has headers. Check the column names and select the fields that should define a duplicate.
Click OK. Excel reports how many duplicate values it removed and how many unique values remain. Review the resulting list before saving or sharing it.

If you selected only one column inside a larger dataset, Excel may ask whether to expand the selection. Choose the option to expand it when the other columns belong to the same records. Continuing with only the selected column can remove values without preserving the alignment of related information, which can corrupt the list.

Choosing the Right Columns

Choosing columns is the most important judgment in the process. Ask what makes two records genuinely interchangeable for your purpose. A name alone is often a weak key: two people can share a name, and one person’s name may be spelled differently in different rows. An email address, account number, or combination of fields may be more meaningful, depending on the dataset.

Exact repeated rows: Check every field that describes the record. This removes rows only when all those selected values match.
One entry per email: Check the email column if the goal is one record for each email address. Consider sorting first if you want a particular row to survive.
One entry per product and location: Check both product and location. Checking product alone could remove valid entries for other locations.
One entry per person and date: Check the person identifier and date fields so separate events are not mistakenly collapsed.

Do not include a column that varies on every row, such as a unique transaction ID, if you are trying to find repeated customers. That ID makes otherwise similar records distinct. Conversely, excluding a field can cause Excel to remove rows that differ in that field. If uncertain, run the command on a duplicate worksheet and compare the result with the source.

Headers, Blanks, Formatting, and Text

The My data has headers setting tells Excel that the top row contains labels rather than records. With the setting enabled, the dialog displays column names; with it disabled, the first row is treated as data and can be removed if it matches another row. Check this option rather than assuming Excel has identified headers correctly.

Blank cells can also affect the result. If the selected comparison columns are blank in two records, those blank values can count as matching. With multiple comparison columns, however, the other checked values still matter: two rows that are blank in one field but different in another are not identical across the full key. Empty or incomplete key fields are a good reason to inspect the proposed duplicates before cleaning a production list.

Cell appearance is not a reliable comparison rule. Different font colors, number formats, or fills do not make otherwise matching records distinct. Dates may also look different because of formatting, while the underlying values determine whether they match. Text capitalization is generally not a safe way to distinguish records in this command; if upper- and lowercase forms have different meaning in your data, test a copy and use a more explicit cleaning or comparison method.

Leading or trailing spaces and inconsistent typing can make values appear alike while remaining different as stored. For instance, a name with an extra space may fail to match the neatly entered version. Standardizing the source data—such as trimming excess spaces and agreeing on a consistent format—can improve results. Do that in a copy first if the original text must be preserved.

Remove Duplicates Versus a Non-Destructive List

Remove Duplicates is appropriate when you want to change the existing list. If you want to preserve the source and display a separate unique list, Excel’s UNIQUE function is a safer option in Microsoft 365 and Excel 2021 or later. It returns results in another area and updates them when the source changes, rather than deleting rows from the source.

With complete records in A2:C8, where each row contains a name, department, and region, enter =UNIQUE(A2:C8) in an empty cell. If the range contains Mira | Sales | East twice, Noah | Support | West twice, Mira | Sales | West once, and Iris | Finance | East once, the formula returns four distinct rows in their first-seen order. Leave enough empty cells below and beside the formula for the results to spill.

To get unique names only, with names in A2:A8, use =UNIQUE(A2:A8). That returns one copy of each distinct name, but it does not tell you which department or other details to retain for each person. Use Remove Duplicates when you need the existing records physically reduced; use UNIQUE when a live, separate output is more useful. Excel’s Advanced Filter is another built-in way to copy unique records to a different location, though its options and workflow differ from the one-click removal command.

Undo, Review, and Version Differences

Immediately after removing duplicates, you can usually reverse the change with Ctrl+Z on Windows. On a Mac, use Command+Z. Undo is a short-term safeguard, not a substitute for a backup: after other actions, closing the file, or making further changes, relying on the undo history may not be practical. Excel actions performed after the removal can also complicate recovery.

After the operation, compare the reported removal count with what you expected. Check a few examples of records that shared the chosen key, especially if several versions contained different details. If the result is unexpected, undo immediately and revisit the selected range, header option, and checked columns. Keeping an untouched source sheet is especially helpful for recurring imports or records used for reporting.

On Excel for Mac, the command is also available from the Data tab as Remove Duplicates; dialog labels or ribbon layout can vary slightly by release. In Excel for the web, the available ribbon features may differ, so if the command is not visible, open the workbook in the desktop app. The UNIQUE formula is a practical cross-version alternative only where that function is supported; older Excel releases may require Advanced Filter or another workflow.

Frequently Asked Questions

Does Remove Duplicates keep the first or last occurrence?
It keeps the first matching row in the current order and removes later matches. Sort the data beforehand if a particular record should be first, and preserve a copy before changing the order.

Can I remove duplicates based on just one column?
Yes. In the dialog, select only that column. Excel then removes later rows with a matching value in that field, even if other fields differ. Include all fields that should distinguish separate records.

Does Remove Duplicates delete the entire worksheet row?
It removes duplicate records from the range being processed. To keep each record’s related fields together, select the full dataset or expand the selection when Excel prompts you. Avoid processing a single column by itself when adjacent columns contain linked information.

Can I find duplicates without deleting them?
Yes. Use conditional formatting to highlight repeated values, or create a separate results list with UNIQUE in a supported version of Excel. These approaches let you inspect or preserve the original data before deciding what to remove.

Why did Excel leave values that look identical?
They may differ in their stored contents, such as because of extra spaces, inconsistent text, or a value stored as text instead of a number or date. Check the selected comparison columns and standardize the data in a copy before running the command again.

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.