Remove Duplicates in Excel: Steps, Options, and Tips

How-to basics · Updated November 21, 2026

To remove duplicate rows in Excel for Microsoft 365 on Windows, select a cell in your data, go to Data > Remove Duplicates, choose whether the data has headers, select the columns that define a duplicate, and click OK. Excel keeps the first matching row and removes later matches, so save a copy first if you may need the original records.

What Excel means by a duplicate

Excel does not decide that two records are duplicates based on what they mean to you; it compares the values in the columns you select. If you select every column, two rows count as duplicates only when their values match across all selected columns. If you select just an email column, rows with the same email count as duplicates even if their names, dates, or other details differ.

This distinction matters when a table contains repeated names. Two customers called Jordan Lee may be different people, while two rows with the same customer ID may represent the same record even if one has a different note. Choose the columns that identify a record in your particular dataset, rather than selecting columns arbitrarily.

In the built-in tool, Excel retains the first occurrence it encounters in the current row order and removes later matching rows. It does not combine information from duplicate rows or decide which version is more complete. If the newest or most complete row should survive, sort the data into the desired order before removing duplicates, or review and resolve the records manually.

Step-by-step: use Remove Duplicates

  1. Make a backup of the workbook or duplicate the worksheet. Removing duplicates changes the selected data directly.
  2. Click a cell inside the list or table. If the data is a normal contiguous range, Excel generally detects the surrounding data; check the selection before continuing.
  3. On the ribbon, select Data > Remove Duplicates.
  4. In the dialog, check My data has headers if the first row contains column names. When this is checked, the headers appear as column labels rather than being treated as records.
  5. Choose the columns Excel should compare. Select all columns for exact duplicate rows, or select only the fields that together identify a duplicate record.
  6. Click OK. Excel reports how many duplicate values were removed and how many unique values remain. Review the remaining data and the count before saving.

On Excel for Mac, the command is also available from the Data tab as Remove Duplicates; ribbon layout can vary with the Excel version and window size. In Excel for the web, look for the command on the Data tab. If you cannot find it, check the ribbon’s overflow or simplified ribbon options. To undo the operation immediately, use Ctrl+Z on Windows or Command+Z on Mac. Undo is not a substitute for a backup, especially after other edits.

Choosing which columns to compare

The checkboxes in the dialog define the duplicate rule. A useful way to decide is to ask: which combination of fields should never occur twice in a valid dataset? For a contact list, that may be email address; for an order line, it may be order number plus product code; for a list of people, a name alone is rarely enough.

  • All columns selected: Excel removes only rows whose values match in every selected column. Rows with the same email but different phone numbers remain.
  • One key column selected: Excel keeps the first row for each value in that column and removes later rows with that value, regardless of differences in unselected fields.
  • Several key columns selected: Excel treats a row as a duplicate when the values match across all those selected columns. For example, matching both order number and product code identifies repeated order lines without removing separate products from the same order.

Consider this example: a table contains an order number, product, and quantity. If the same order number appears on several rows because it includes several products, checking only the order-number column would wrongly discard legitimate lines. Checking order number and product together is more appropriate if duplicate order lines are the problem. The effect is destructive, so verify the rule on a copy before applying it to a large or important list.

Prepare the data before deleting rows

Values that look alike on screen may not be identical underneath. Leading or trailing spaces, inconsistent punctuation, different spellings, and mixed data types can affect whether records match. For example, a name with a trailing space may not be treated as the same text as the name without it. A number stored as text can also behave differently from a numeric value in some comparisons.

Before removing duplicates, consider cleaning and standardizing the fields used as keys. You can use TRIM to remove ordinary extra spaces, or use Find and Replace to standardize predictable variations such as hyphens. Check the cleaned values before replacing or deleting the originals. Do not assume that two similar-looking names or addresses refer to the same person or place; automated cleanup cannot resolve every real-world identity question.

Also check for blank rows and inconsistent headers. If Excel does not select the intended block, select the full data range yourself before opening the command. If the data is formatted as an Excel Table, clicking within the table helps Excel apply the operation to the table’s records and preserve its structure. Avoid selecting a total row or notes below the table as part of the data.

Remove duplicates without permanently changing the source

If you want a distinct list while keeping the original records intact, use the UNIQUE function in a separate area instead of the Remove Duplicates command. UNIQUE is available in Microsoft 365 and Excel 2021 or later. With complete records in cells A2:C8, enter this formula in an empty cell outside the source range:

=UNIQUE(A2:C8)

It returns each distinct row from A2:C8 once, comparing the full rows, and spills the results into the cells below and to the right. For example, if A2:C8 contains seven records and two rows are exact repeats of earlier rows, the result contains five rows. Keep the spill area empty; existing data in its path can produce a #SPILL! error. Because the formula result updates when the source changes, it is useful for a live summary, but it is not a deletion from the original dataset.

To get unique values from just one column, with names in A2:A8, use =UNIQUE(A2:A8) in an empty cell. That produces one entry for each distinct name; it does not preserve the associated details from other columns. If you need to choose which record to retain or reconcile different details, a distinct list alone is not enough.

Highlight and review duplicates first

When you are unsure which records should be removed, mark possible duplicates before deleting anything. Select the relevant cells or range, then use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Excel highlights repeated values within the selection, which is helpful for a quick visual check. This rule is for finding repeated cell values, however; it does not necessarily identify duplicate whole rows according to a multi-column key.

You can also sort by the identifying column or columns so matching records appear together, then inspect the differences before deciding what to keep. For example, sort a customer list by email and compare the name, phone, and address for repeated emails. A duplicate indicator is evidence that values repeat, not proof that the records are erroneous. Shared addresses, family email accounts, repeated product codes, and legitimate repeated transactions can all be valid.

Common problems and misconceptions

  • The wrong row survived: Remove Duplicates keeps the first matching row in the current order. Sort first if you want a particular record to be first, and verify the sort before deletion.
  • Excel removed too many records: The selected columns may not uniquely identify a record. Undo immediately, then repeat the operation on a copy with a more suitable set of columns.
  • Some apparent duplicates remain: Check for extra spaces, inconsistent punctuation, different spellings, or differences in a column that was included in the comparison.
  • Headers were treated as data: Reopen the operation only after undoing, and check My data has headers when the first row is a header. A header accidentally removed as a duplicate can affect how the data is interpreted.
  • The count seems surprising: Excel reports duplicate entries removed, not necessarily the number of groups that had repeats. A value appearing three times has two later occurrences that can be removed.

One common misconception is that Excel merges duplicate rows. It does not: it retains one row and removes the others according to the chosen columns. If repeated rows contain different useful information, copy or reconcile those details first. Another misconception is that removing duplicates is always safe because the rows appear identical. Confirm the key fields and retention order before applying it to important data.

When to choose each method

Use Remove Duplicates when you have reviewed the rule and want to permanently reduce the existing list. Use UNIQUE when you want a separate, refreshable list of distinct values or rows while retaining the source. Use conditional formatting or sorting when the correct decision depends on a person comparing records. These methods answer different questions: deletion edits the source, a formula displays distinct results, and highlighting helps you inspect repeated values.

For recurring data imports, consider keeping a raw-data worksheet unchanged and doing cleanup on a working copy. That approach makes it easier to compare later imports, investigate disputed records, or revise the duplicate rule. If you use Power Query for a repeatable import workflow, its remove-duplicates operation can be applied as a query transformation; the exact behavior still depends on the columns selected, so define and check the key fields carefully.

Frequently Asked Questions

Does Remove Duplicates delete the first occurrence?
Normally, no. Excel keeps the first matching row in the current order and removes subsequent matching rows. Sort before using the command if a particular record should be retained.

Can I remove duplicates based on only one column?
Yes. In the Remove Duplicates dialog, leave that column checked and clear the others. Excel then keeps the first row for each value in the chosen column, even when other fields differ.

How do I remove duplicate rows but keep the original data?
Use =UNIQUE(A2:C8) in an empty cell when the source records are in A2:C8 and your Excel version supports UNIQUE. It displays distinct complete rows separately; it does not delete source records.

Why does Excel say it removed fewer duplicates than I expected?
Excel removes later occurrences, not one item for every repeated group. If a row occurs three times, two occurrences are removed. Differences in selected columns can also mean rows are not duplicates under the rule you chose.

Can I undo Remove Duplicates?
Use Ctrl+Z on Windows or Command+Z on Mac immediately after the operation. If you have made other changes, closed the workbook, or cannot undo safely, restore a saved copy or use your backup.

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.