Can You Delete Duplicates in Excel? Methods and Tips

How-to basics · Updated November 19, 2026

Yes. In Excel for Microsoft 365 on Windows, select your data and choose Data > Remove Duplicates to remove repeated rows; choose which columns define a duplicate before confirming. The command changes the selected data, so make a copy first if you may need the original. You can also identify or display duplicates without deleting them.

What Excel Counts as a Duplicate

A duplicate is not always an identical-looking row. Excel checks the columns you select in the Remove Duplicates dialog. If you select every column, it removes rows only when the values match across all selected columns. If you select just Email, for example, rows with the same email count as duplicates even if their names, dates, or other details differ.

This distinction matters because a repeated value may be valid in one field but not another. Two customers can share a surname, and two orders can have the same date. A repeated customer ID might be an error in a table intended to have one row per customer, but expected in a table that records multiple purchases per customer.

The command keeps the first occurrence it encounters and removes later rows that match the chosen criteria. It does not combine records, add their amounts together, or decide which conflicting details are more accurate. If two matching records contain different information in columns you did not use to define duplicates, the kept row’s other details remain; Excel does not merge them with the deleted row.

How to Remove Duplicates Safely

On Windows, the built-in command is on the Data tab. Use it on a complete, well-defined list rather than a single column when each row represents a record:

  1. Save a copy of the workbook or duplicate the worksheet if the records are important.
  2. Click a cell in the data range. Check that the list has one header row and no unrelated data directly beside it.
  3. Choose Data > Remove Duplicates.
  4. In the dialog, confirm whether My data has headers is checked. Check the boxes for the columns that should determine whether two records are duplicates.
  5. Choose OK, then read Excel’s message about how many duplicate values were removed and how many unique values remain.

For example, if a list has columns for Customer ID, Name, and City, select only Customer ID when each customer should appear once. Excel will keep the first row for each ID and remove later rows with that ID. Select all three columns instead if you want to remove only records where the ID, name, and city all match.

Select the whole dataset before using the command. If Excel asks whether to expand the selection, expanding is usually the right choice for row-based records; continuing with only a partial selection can separate values in that column from the rest of their original rows. If the range is an Excel table, the dialog lists its columns, and the operation applies to the table’s rows.

Choosing the Right Columns

Decide what makes a record unique before opening the dialog. The right choice depends on the purpose of the data, not on which columns happen to look repeated. A list of contacts might use an email address as its key, while an inventory list might need a combination of product code and location.

When a duplicate should be defined by more than one field, select each relevant column. For instance, to keep one row per product in each store, select Product Code and Store, but leave Quantity and Last Updated unchecked. Rows match when the values in both selected columns match. A repeated product code at a different store is then retained.

Be cautious about choosing a column that can change, such as a display name or mailing address. Two people can share a name, and a customer can move. If the data contains a reliable unique identifier, it is often a better key. When records conflict, sort or review them first and decide which record should survive; Remove Duplicates does not offer a rule such as “keep the newest date” or “keep the row with the most complete information.”

Keep the Source and Check the Result

Remove Duplicates edits the data in place. It is not a preview-and-merge tool, and there is no separate recovery report containing the deleted rows. If you notice a mistake immediately, use Undo (Ctrl+Z on Windows; Command+Z on Mac), but undo may no longer be available after other actions or after closing the workbook. A saved copy is safer for important information.

Before running it, consider copying the source sheet, or copy the range to a new sheet and work on that copy. Afterward, check the removal message and compare the remaining row count with what you expected. Inspect several examples of the key values and confirm that the surviving rows contain the right associated details. If the list is used for reporting, refresh or review dependent formulas, charts, and summaries as appropriate.

Filters and hidden rows can make a list look smaller than it is, so clear filters or understand their effect before cleanup. Also check whether blank rows divide the range or whether totals and notes are mixed into the records. A clean rectangular range with one header row is easier to select and verify.

Alternatives That Show Duplicates Without Deleting

Use conditional formatting when you want to inspect repeated values first. Select the relevant cells, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. This highlights repeated values in the selected range; it does not remove rows. It is a useful review step, but highlighting one column does not by itself establish that entire records are duplicates.

You can also make a separate list of distinct rows with the UNIQUE function. With the records in A2:C100 and a blank area available for the results, enter =UNIQUE(A2:C100). In Excel for Microsoft 365 or Excel 2021, the formula spills a list containing one instance of each distinct row across those three columns, leaving the source unchanged. It returns distinct combinations of the full rows in the range, not unique entries based on just one of those columns.

For a simple one-column review, you can mark later repeats with a helper formula. With values in A2:A100, enter =COUNTIF($A$2:A2,A2)>1 in B2 and fill it down. TRUE marks a value that has appeared earlier in the list; FALSE marks its first appearance. This helps you review before deleting, but it checks one column and COUNTIF is not case-sensitive. UNIQUE and dynamic-array behavior require Excel 2021 or Microsoft 365; in older versions, use a review method such as conditional formatting or the built-in Remove Duplicates command.

When Power Query Is a Better Fit

For recurring imports or a repeatable cleanup process, Power Query can remove duplicates while leaving the original source data intact. In Excel for Microsoft 365 on Windows, select the data and use Data > From Table/Range to open the Power Query Editor. Select the column or columns that define a duplicate, then choose Home > Remove Rows > Remove Duplicates. Choose Home > Close & Load to return the result to Excel.

Power Query is useful when the source will be refreshed and the same cleanup needs to be applied again. It records steps as a query, so a refresh can repeat the transformation on updated source data. As with the worksheet command, be deliberate about the selected columns. If records have conflicting non-key details, removing duplicates is not a substitute for deciding which information to preserve. Power Query’s interface and available connectors can differ by Excel version and platform.

Common Problems and Misconceptions

  • Rows that look the same are not removed. Check every selected column for a different value. A trailing space, a different date or a number stored as text can make entries distinct even when they appear alike.
  • Different-looking values are treated as the same. Formatting can make different underlying values look similar. Check the actual cell contents and number formats before deciding whether the records should match.
  • Only one field is selected by mistake. That can remove records with a repeated key even when their other details differ. Review the checkboxes in the dialog before confirming.
  • The wrong row survives. Excel keeps the first matching occurrence in the current order. If you want a particular record retained, arrange or filter the data deliberately before removing duplicates, and keep a backup.
  • The header is treated as data, or vice versa. Verify the My data has headers setting. If the first row is a heading, identify it as a header so it is not evaluated as an ordinary record.

Excel’s duplicate tools are designed for ordinary data cleanup, not forensic comparison. If letter case, invisible characters, or complex matching rules matter, inspect and normalize the data or use a carefully designed formula or query. Do not assume that two records should be merged just because one field repeats.

Frequently Asked Questions

Can I remove duplicates based on one column only?

Yes. In Data > Remove Duplicates, clear the other column checkboxes and leave the key column selected. Excel removes later rows sharing that value and keeps the first matching row, including its values in the other columns.

Does Remove Duplicates delete the whole row?

When you work with a complete data range, Excel removes duplicate records as rows. Select the full dataset so each row’s associated information stays together; avoid choosing only part of a record unless that is intentional.

Can I find duplicates without deleting them?

Yes. Use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values to highlight repeated cell values, or use a helper formula or UNIQUE in a separate area. These options let you review the data while retaining the source.

Does Excel keep the first or last duplicate?

Remove Duplicates keeps the first occurrence in the current row order and removes later matches. If you need to keep a particular record, sort or otherwise arrange the rows before using the command, and verify the result.

Can I undo removing duplicates?

Use Ctrl+Z on Windows or Command+Z on Mac immediately after the operation. Undo is not a dependable backup after additional changes or closing the file, so save a copy first when the data matters.

Is Remove Duplicates available in Excel for Mac or the web?

Excel for Mac and Excel for the web also provide duplicate-removal features, though the ribbon layout and dialog details can vary by version. Look on the Data tab for Remove Duplicates; if the command is not available in your build, use a formula or conditional formatting to identify repeats and check the app’s version-specific help.

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.