Excel Remove Duplicates: How It Works and When to Use It

How-to basics · Updated November 21, 2026

Excel’s Remove Duplicates command deletes repeated records from a selected range or table: select the data, choose Data > Remove Duplicates, select the columns that define a duplicate, and confirm. It keeps the first matching record it encounters and removes later matches, so check which columns you select—and save or copy your data first if you may need the original.

What Excel’s Remove Duplicates command does

Remove Duplicates is a data-cleanup command, not a worksheet function that returns a result in a separate cell. It changes the selected data in place by removing entire rows that match earlier rows according to the columns you specify. Excel reports how many duplicate values it removed and how many unique values remain.

The key decision is what “duplicate” means for your task. If you select every column in a table, Excel removes rows only when their values match across all those columns. If you select just one column, Excel treats records with the same value in that column as duplicates—even if their details in other columns differ. In either case, the rows removed are whole records, not just repeated cells.

For example, a customer list might contain the same email address twice with different names or phone numbers. Selecting only Email keeps the first record for that address and deletes the other complete row. Selecting Email, Name, and Phone instead removes a row only when all three selected fields match. The right choice depends on whether you want one row per email address or only want to remove identical records.

How to remove duplicates in Excel for Microsoft 365

On Excel for Microsoft 365 for Windows, use the Data tab. It is usually safest to select a cell inside the dataset rather than highlighting a partial block, so Excel can identify the full range. Review the selection in the dialog before confirming.

  1. Click a cell in the range or Excel table you want to clean.
  2. Choose Data > Remove Duplicates.
  3. If the first row contains column names, leave My data has headers checked. If it does not, clear the checkbox.
  4. In the columns list, check the fields Excel should compare. Choose Select All to compare every column, or clear it and select specific fields.
  5. Click OK. Read Excel’s confirmation message to see how many duplicates were removed, then inspect the resulting data.

Suppose a range has columns Order ID, Customer, and Amount. To remove only repeated orders, select Order ID as the comparison field. To remove rows that are identical in every listed field, select all three. If Order ID repeats because one order has multiple line items, however, removing duplicates by Order ID alone would discard valid lines. The command follows your selected criteria; it cannot determine what constitutes a valid record for your business.

Choosing the columns and the record to keep

Excel keeps the first occurrence it encounters in the current order of the data. If the same email appears in rows 4 and 18, and you choose Email as the only comparison column, the earlier row is retained and the later row is removed. “First” means first in the current worksheet order, not newest, most complete, or most accurate.

If you need to keep a particular record, arrange the data before using the command. For instance, to keep the most recent customer record, sort the date field from newest to oldest, then remove duplicates using the customer ID or email field. Verify that the sort covers the entire dataset; sorting just one column can disconnect values from their associated records. For complex cases, make a copy and use a helper column or review the duplicates manually before deleting anything.

  • Use a unique ID when each real-world item should appear once.
  • Use all columns when you only want to remove fully repeated rows.
  • Use a combination of fields when no single field uniquely identifies a record, such as date plus product code.
  • Sort first if the row you want to retain is not currently the first occurrence.

What counts as a duplicate—and common surprises

A duplicate is determined by the selected comparison columns, not by whether two rows look similar to a person. Differences in an unselected column do not protect a row from deletion. Conversely, a difference in a selected value can make records distinct even when they appear nearly identical. Leading or trailing spaces, punctuation, hidden characters, and inconsistent data entry can therefore affect the outcome.

Formatting is not a reliable way to distinguish records. Two values that look different because of number or date formatting may still represent the same underlying value; changing a cell’s color or font does not make its record unique. Text that contains an extra space, on the other hand, may not match the otherwise identical text. If the source data is inconsistent, clean or standardize it first—for example, remove unwanted spaces in a separate helper column—then compare the cleaned values.

Blank values can also create unexpected results. If the selected comparison fields are blank in multiple rows, Excel may treat those rows as duplicates under those fields, even if other unselected details differ. Decide whether blank means “same unknown value” or “not enough information to compare” before removing records. When blanks have special meaning, filter them and review them separately.

Protecting data and undoing a removal

Remove Duplicates changes the original range rather than creating a separate output. Make a copy of the worksheet or workbook before using it when the source must be preserved, especially for imported customer, inventory, or transaction data. You can also copy the range to another sheet and run the command on that copy.

Immediately after the operation, Ctrl+Z on Windows reverses it, provided you have not taken later actions that change the undo history. On a Mac, the equivalent is Command+Z. Do not treat Undo as a long-term backup: after closing the workbook, or after other operations have altered the undo history, it may no longer be available. Save a separate copy when the result matters.

Excel tables are convenient for this task because their column names appear in the dialog, making it easier to choose fields. The command removes duplicate records from the table’s data, so review the table afterward and confirm that any totals, formulas, or summaries still make sense. If the range includes nearby notes or unrelated columns, ensure Excel has identified only the intended dataset before you confirm.

Remove Duplicates compared with other Excel options

Choose Remove Duplicates when you want to clean the existing data and are prepared to delete repeated rows. Other tools are better when you want to inspect matches first or retain the source unchanged.

  • Conditional Formatting: Highlights repeated values so you can review them before deleting anything. Select the relevant cells, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. This highlights matches in the selected range; it does not remove complete records.
  • UNIQUE: Returns unique values or rows in a separate, dynamically updated result. With the source data in A2:C100, =UNIQUE(A2:C100) returns distinct rows from that range and leaves the source untouched. UNIQUE is available in Microsoft 365 and Excel 2021 or later.
  • Advanced Filter: Can copy filtered unique records to another location, which is useful when you need a separate output rather than editing the source. Find it on the Data tab in the Sort & Filter group, then choose Advanced.

For example, if A2:C100 contains a product list and you need a clean copy to share while preserving the imported list, place =UNIQUE(A2:C100) in an empty area or another sheet. The formula spills a distinct set of rows, and changes to the source can update the result. Make sure the cells below and beside the formula are empty so the result has room to spill. If you need a fixed snapshot, copy the results and paste them as values.

Finding repeated values without deleting records

For a controlled review, Conditional Formatting or a helper formula can flag potential repeats. For instance, with product codes in A2:A20, enter the following in B2 and fill it down through B20:

=COUNTIF($A$2:A2,A2)>1

The formula returns TRUE for a code that has already appeared earlier in the checked range and FALSE for its first occurrence. If A2 is the first instance of a code, it returns FALSE; a later identical code returns TRUE. This is a review aid, not a command to delete rows: check the associated details before removing any record. If the data is sorted differently, the first occurrence—and therefore which rows are flagged—can change.

Another option is to filter the highlighted or flagged records, inspect them, and then decide whether to delete them. This is useful when records have conflicting dates or details. A duplicate check can reveal repeated identifiers, but it cannot tell whether two rows are accidental copies, legitimate transactions, or separate items that happen to share a field.

Frequently Asked Questions

Does Excel keep the first or last duplicate?
It keeps the first occurrence in the current row order and removes later matching rows. Sort the entire dataset first if you want a particular record—such as the newest one—to be first.

Can I remove duplicates based on one column only?
Yes. In the Remove Duplicates dialog, clear the other column checkboxes and leave the one comparison field selected. Excel will remove whole rows that repeat that field, even if their unselected details differ.

Does Remove Duplicates delete the whole row?
Yes. It removes the complete later row when the selected comparison values match. It does not simply clear the repeated cell, so check that the selected columns reflect your intended definition of a duplicate.

How can I highlight duplicates without deleting them?
Select the cells to check and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Excel highlights repeated values in the selected range for review; it does not remove rows.

Is Remove Duplicates the same as the UNIQUE function?
No. Remove Duplicates changes the original data by deleting matching rows. UNIQUE creates a separate result containing distinct values or rows, so it is often preferable when you want to preserve the source. UNIQUE requires Microsoft 365 or Excel 2021 and later.

Can I undo Remove Duplicates?
Usually, you can immediately press Ctrl+Z on Windows or Command+Z on Mac. Because undo history is temporary, save a backup copy first when the original records must be retained.

Related

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.