Remove Duplicates in Excel: Steps and Safer Options

How-to basics · Updated November 18, 2026

Yes. In Excel for Microsoft 365 on Windows, select your data, choose Data > Remove Duplicates, select the columns that define a duplicate, and confirm. Excel keeps the first matching record and removes later matches, so make a copy or use a formula-based method if you need to preserve the original data.

What Excel means by a duplicate

A duplicate is not always a row that matches every cell. When you use Remove Duplicates, you tell Excel which columns to compare. If you select every column, Excel removes rows that match across all of those columns. If you select only Email, for example, it treats records with the same email address as duplicates even if their names, dates, or other details differ.

That distinction matters because two records can share one value without being the same record. Two customers might have the same surname; two orders might have the same date; or two people might share a household phone number. Decide what makes a record unique before removing anything. A customer list might use email address as its identifier, while an order list may need both Order ID and Line Item.

Excel retains the first occurrence it encounters in the range and removes the later matching rows. If the first occurrence is incomplete or outdated, sort or review the data first. Removing duplicates is not the same as merging records: Excel does not combine fields from duplicate rows or choose the most complete record for you.

Remove duplicates with the built-in command

These steps apply to Excel for Microsoft 365 on Windows. The command changes the selected data, so make a backup copy of the sheet or workbook if you may need to recover the original.

  1. Click a cell inside the data, or select the full range you want to check. Include the header row if there is one.
  2. Choose Data > Remove Duplicates on the ribbon.
  3. In the dialog box, select My data has headers if the first row contains column names. Check the columns Excel should use to identify duplicate records.
  4. Click OK. Excel reports how many duplicate values it removed and how many unique values remain.

For example, suppose a table has columns named Customer, Email, and City. Selecting all three columns removes only rows that match in all three. Selecting Email alone removes later rows with an email address already seen, even if the other fields differ. Read the column labels carefully: the choice is about the duplicate rule, not just which columns you want to erase.

If you select only one column within a larger table, Excel may ask whether to expand the selection. Choose to expand when you want entire duplicate records removed. Continuing with only the selected cells can leave the rest of the table untouched and misalign the data. Selecting the whole table first is the clearest way to avoid this problem.

Choose the columns that define a match

Before running the command, write down the record key: the field or combination of fields that should identify one record. A unique ID is usually a better key than a descriptive field such as a name. If no single column is unique, a combination may be. For instance, a class roster could use Student ID, while a transaction table might use Invoice Number and Item Number together.

  • Use all columns when you want to remove only completely repeated rows and preserve records that differ in any field.
  • Use an identifier column when repeated IDs should count as the same record, and you have checked which version to retain.
  • Use multiple columns when the combination, rather than any one field, identifies a record.
  • Do not use a convenient but non-unique field without checking the consequences. A shared city or last name is rarely enough to identify a person.

A practical safeguard is to sort by a field that helps you choose which copy should remain, such as a last-updated date, and inspect the groups before removal. Sorting does not tell Excel to keep the newest or most complete row; it only changes which matching record appears first. Confirm the order before using the command.

Check and clean data before removing rows

Values that look the same on screen may not compare as expected because of extra spaces, inconsistent spelling, or different data entry. Conversely, different formatting does not necessarily mean that two underlying entries are different. For example, dates can be displayed in different formats even when they represent the same date. Inspect suspicious entries and standardize the data before deciding that they are unique.

For text with accidental leading or trailing ordinary spaces, a helper column can make a cleaned comparison value. With source text in A2, enter this in B2 and fill it down: =TRIM(A2). TRIM removes leading and trailing spaces and reduces repeated ordinary spaces between words to one. It does not remove every kind of invisible character, such as a nonbreaking space copied from a web page. If the cleaned results look unchanged but matches still fail, inspect the source or use a more specific cleanup method.

Keep the original column until you have checked the cleaned values. If you decide to replace the originals, copy the helper results and use Paste Special > Values so the cells contain the cleaned text rather than formulas that depend on the helper column. A cleanup formula can change how entries compare, so review examples such as addresses, product codes, and names before applying it to a full dataset.

Preview duplicates without deleting them

If you want to review matches first, use Conditional Formatting to highlight repeated values. Select the relevant cells, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. This is useful for a quick visual check, but it highlights repeated values in the selected area; it does not determine whether whole records are duplicates based on a multi-column key.

A helper formula can flag later occurrences in one column. With values in A2:A100, enter this in B2 and fill it down through B100: =COUNTIF($A$2:A2,A2)>1. The first occurrence returns FALSE, and each later occurrence of the same value returns TRUE. For example, if A2:A5 contains Lee, Kim, Lee, and Patel, the results are FALSE, FALSE, TRUE, and FALSE. This checks one column, so it is not a substitute for comparing a combination of fields. Review the flagged rows before deleting them.

Use UNIQUE when you want a separate result

The UNIQUE function is a useful alternative when you want a distinct list without deleting the source records. It is available in Microsoft 365 and Excel 2021 or later. With values in A2:A100, enter =UNIQUE(A2:A100) in an empty cell to return each distinct value once. Excel spills the results into cells below the formula, so leave enough space in that area.

To return distinct complete rows, put the formula in an empty area and use the full data range. With records in A2:C100, enter =UNIQUE(A2:C100). Excel returns one copy of each row that is identical across columns A, B, and C. UNIQUE does not remove rows from the original range; the output is a formula-driven result. If you need a permanent, independent list, copy the output and paste it as values after checking it.

UNIQUE is not identical to Remove Duplicates in every workflow. It is particularly helpful for a live summary because its results can update when the source range changes, but a formula result cannot be edited cell by cell while it is spilling. It also compares the full supplied range; if your rule is based on one key column but you want to return complete records, you may need a more tailored process. For older Excel versions without UNIQUE, use the built-in command on a copy, or use a PivotTable or a helper-column review.

Common problems and how to avoid them

  • The wrong row remains. Excel keeps the first occurrence, not necessarily the best one. Sort or review the records before removal, and make sure the desired version comes first.
  • Different records disappear. You may have selected too few columns, so Excel matched rows using a field that is not unique. Undo immediately with Ctrl+Z on Windows, then repeat with the correct columns.
  • Duplicates remain. Check for inconsistent spaces, spelling, or other differences in the selected fields. A value that looks alike may contain an extra character or may have been entered differently.
  • Rows no longer line up. You may have removed duplicates from a single selected column instead of the entire table. Undo if possible, then select the complete range and run the command again.
  • The header is treated as data. In the dialog, confirm whether the range has headers. If the first row is a header but the option is not selected, Excel can include it in its comparison.
  • You cannot undo the change. Excel’s Undo may be unavailable after other actions or after closing the workbook. Keep a backup before using a destructive command.

Which method should you choose?

Use Remove Duplicates when you have settled on a clear matching rule and want to change the current table. Use Conditional Formatting or a COUNTIF helper when you need to inspect suspected duplicates before taking action. Use UNIQUE when you want a separate distinct list and have a compatible Excel version. These methods solve related but different problems: highlighting and formulas help you review or produce results, while Remove Duplicates deletes rows from the selected data.

For a one-time cleanup, a copied worksheet is often enough protection. For a recurring import, keep the raw data unchanged and create a separate cleaned output, or document the rule so another person can reproduce it. If this is a formal record, such as a customer, inventory, or payment log, check the business rules for which version to retain before removing anything. Excel can identify matches according to your selected columns, but it cannot determine whether two similar records truly refer to the same real-world item.

Frequently Asked Questions

Does Remove Duplicates delete the whole row?

Yes, when the full table or range is selected, Excel removes later duplicate rows from that range according to the columns you check. If you select only one column and continue with that selection, other columns may not be removed with it, so expand the selection or select the entire table first.

Does Excel keep the first or last duplicate?

It keeps the first occurrence in the selected data and removes later matches. If you want a particular record to remain, arrange the rows so that record appears first, then verify the sort order before running Remove Duplicates.

Can I remove duplicates based on just one column?

Yes. In the Remove Duplicates dialog, check only that column while applying the command to the whole table. Excel then uses that field to decide which rows match, but removes the later matching rows as records. Make sure the field is a suitable identifier.

Can I undo Remove Duplicates?

Use Ctrl+Z immediately on Windows if the command is still in the Undo history. Do not rely on Undo as your only safeguard; save a copy first. If you are using a Mac, the undo shortcut is Command+Z.

Does Remove Duplicates work in Excel for Mac or the web?

Excel for Mac also provides a Remove Duplicates command on the Data tab, though the ribbon layout can differ slightly. Excel for the web includes data tools that may vary with the interface and account; if you do not see the command, use the desktop app or create a separate distinct list with a supported formula such as UNIQUE.

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.