To remove duplicates in Microsoft Excel for Microsoft 365 on Windows, select your data, then choose Data > Remove Duplicates. In the dialog, choose the columns that define a duplicate and select OK; Excel keeps the first matching record and removes later ones from the selected range.
What Remove Duplicates Does
Excel’s Remove Duplicates command permanently deletes repeated rows from the selected data range. It does not just mark duplicates or hide them. For each group of records that match according to the columns you selected, Excel keeps the first occurrence in the current row order and removes the rest. If you need a different record to survive, sort the data first.
The columns you check determine what counts as a match. Checking every column means two rows are duplicates only when their values match across all those columns. Checking just one column, such as Email, means records with the same email count as duplicates even if their names, dates, or other details differ. The first matching row stays, so choosing the right columns matters as much as choosing the command.
For example, a customer list might contain two rows for the same email address, one with an older phone number and one with a newer one. Removing duplicates based on Email alone removes one entire row; it does not combine the phone numbers or keep the newest value automatically. Sort by the date you trust before removing duplicates if the newest record should be retained.
Prepare the Data Before Removing Anything
Make a copy of important data or confirm that the workbook can be restored before you run the command. Excel’s Undo button or Ctrl+Z can usually reverse the change immediately, but later edits and saves can make recovery less straightforward. Remove Duplicates changes the data itself, so treat it as a cleanup operation rather than a preview.
- Make sure the selected range includes every column belonging to each record. If you select only a few columns, Excel can remove cells in that range while leaving adjacent information outside it behind, which may disconnect details from their original rows.
- Check whether the first row contains column names. In the dialog, select My data has headers when it does; otherwise Excel may treat the header as an ordinary record.
- Decide which fields define a duplicate. A repeated invoice number may be enough, while a contact list may require matching both name and email.
- If the order of records matters, sort the full table before removing duplicates. Sorting only one column can scramble the relationship between values in different columns.
- Check for hidden rows, filters, and blank cells. Clear filters or confirm what is selected so the operation applies to the records you intend to clean.
For a repeatable workflow, convert a contiguous data range to an Excel table with Insert > Table and confirm that the header option is correct. Tables make column names easier to identify and typically expand when new rows are added. Still, verify the table boundaries and selected columns before removing records.
Step-by-Step Instructions in Excel for Windows
- Click a cell inside the data. If the records form a continuous block, Excel can recognize the surrounding region; for more control, select the full range, including all record columns.
- Open the Data tab on the ribbon and choose Remove Duplicates in the Data Tools group.
- In the dialog, check My data has headers if the first row contains field names. Review the column list, then check only the columns that should determine whether records are duplicates.
- Select OK. Excel reports how many duplicate values were removed and how many unique values remain. Review the resulting rows, especially when the chosen fields were not unique identifiers.
If Excel detects that your selection excludes neighboring data, it may offer to expand the selection or continue with the current selection. Choose Expand the selection when the neighboring columns belong to the same records. Continuing with the current selection can leave those other columns untouched while rows in the selected area are removed, producing mismatched data.
There is no need to select only the columns used to identify duplicates. You can select the complete record range, then choose a smaller set of comparison columns in the dialog. This is usually the safer approach: Excel uses the chosen fields to decide which records match, while removing the corresponding duplicate rows from the full selected range.
Choosing the Columns That Define a Duplicate
Think of the checked columns as a key. Excel compares the values in those fields for each row. If every checked value matches another row, Excel treats the later row as a duplicate. Multiple checked fields work together: a row must match on all selected fields to count as a duplicate.
- One identifier: Use a genuinely unique field such as a product ID when repeated IDs always represent the same record.
- A combination of fields: Choose fields such as First Name and Last Name, or Order Number and Item Code, when no single column identifies a record.
- All columns: Use this when the goal is to remove only completely identical rows, not records that share one value but differ elsewhere.
Names alone are often a poor key: two different people can share a name, and the same person may appear with spelling variations. Similarly, a repeated company name may represent separate branches. Before deleting records, consider whether a match in the selected fields really means the rows refer to the same thing.
When you need to retain a preferred version, sort the complete data range by the relevant field first, such as newest update date first. Remove Duplicates keeps the first occurrence it encounters; it does not inspect dates to decide which row is best. Check the sort order and confirm that the first matching row is the one you want to preserve.
What Excel Counts as the Same
Remove Duplicates compares cell values in the selected columns, not just what the cells look like. Two dates displayed in the same format may have different underlying date-and-time values, and numbers displayed with rounded formatting can still differ in their stored values. Conversely, the same value displayed with different number formats can still match. A displayed similarity is not proof that the underlying values are identical.
Lookalike text can also differ. Leading or trailing spaces, nonbreaking spaces, and other invisible characters can make apparently identical entries distinct. Differences in capitalization are generally not treated as separate values by Excel’s duplicate-removal process. If records you expect to match remain, inspect the source values and standardize the text before trying again. If values you thought were distinct disappear, check whether the selected key was too broad or whether the values differ only in formatting.
Blank cells need attention too. If the selected comparison columns contain blank values in multiple rows, Excel can treat those repeated blank keys as duplicates and remove later rows. A blank in one checked field does not necessarily make two records duplicates if other checked fields differ. Check the full combination of selected columns and decide whether incomplete records should be handled separately.
Formula cells are judged by their resulting values for this purpose, not by whether the formulas were typed the same way. Two different formulas that return the same value can therefore match if the relevant columns are selected. If formulas are part of the record, consider whether matching their results is appropriate before deleting rows.
Common Problems and How to Avoid Them
- More rows disappear than expected: You may have checked only a common field, such as city or surname. Undo immediately with Ctrl+Z if appropriate, then run the command again using a more specific key.
- Some duplicates remain: The entries may contain extra spaces, hidden characters, different date/time values, or other differences in the selected fields. Standardize or inspect the data before rerunning the operation.
- Columns no longer line up: The selection may have excluded columns that belong to the same records. Undo if possible, select the full record range, and use the dialog to choose the comparison fields.
- The header was changed or removed: Check My data has headers in the dialog when the first row contains headings. Undo and repeat with the correct setting.
- The wrong version of a record remains: Sort the complete range so the preferred version comes first, then remove duplicates again.
After cleaning, verify a few records and compare the number of rows before and after. The confirmation message is useful, but it cannot tell whether your chosen definition of a duplicate was correct. Save the workbook after you have checked the result.
Remove Duplicates Compared With Other Methods
Use Remove Duplicates when you want to delete repeated records from the working range and keep one occurrence. If you only want to locate duplicates first, use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. This highlights repeated cell values but does not delete rows; it is useful for inspection, though highlighting individual cells does not by itself establish whether whole records are duplicates.
For a separate, formula-driven list that leaves the source data intact, Microsoft 365 includes the UNIQUE function. With records in A2:D100 and the output formula entered in an empty cell outside that range, use =UNIQUE(A2:D100). It returns one copy of each distinct row in the selected range and spills the results into neighboring cells. It is a dynamic result, not deletion of source rows; ensure the spill area is empty. UNIQUE is available in Microsoft 365 and Excel 2021 or later, but the Remove Duplicates ribbon command remains useful when you want to change the original range.
Another option is to copy the data to a separate location and use Data > Advanced in the Sort & Filter group, then select Unique records only. That approach can produce a unique copy without removing rows from the original data. Choose a method based on whether you want to inspect, extract, or permanently clean the source.
Excel on Mac and Excel for the Web
In current Excel for Mac, select the data and use Data > Remove Duplicates; the dialog also lets you choose whether the data has headers and which columns to compare. The command is available from the Data tab in Excel for the web as well, although its layout can differ from the Windows desktop ribbon. On either platform, apply the same precautions: select the full record range, choose the correct comparison fields, and check which occurrence will be kept. Keyboard shortcuts and ribbon placement can vary by platform and version, so the ribbon command is the clearest route.
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 full data range first if you want a particular record to appear first and be retained.
Can I remove duplicates based on one column but keep all the other columns?
Yes. Select the full record range, then check only the identifying column in the Remove Duplicates dialog. Excel removes later matching rows from the selected range, including their other field values; it does not merge information from those rows.
Will Remove Duplicates delete rows from the original data?
Yes. It removes duplicate records from the selected range. Use Undo promptly if you made a mistake, or use a copy, Conditional Formatting, Advanced Filter, or UNIQUE when you want to preserve the original.
Why did Excel not find entries that look identical?
They may differ in stored values, hidden spaces, nonprinting characters, or date/time details even if their displayed text looks alike. Formatting alone can also make equal values look different. Inspect and standardize the data, then try again.
Can I undo Remove Duplicates?
Usually, Ctrl+Z on Windows or Command+Z on Mac reverses the most recent operation. Undo can stop being available after later actions or changes in the workbook, so keep a copy of important data before cleaning.
Related
- How to Run Scenario Manager in Excel
- How to Run Goal Seek in Excel to Find a Target Value
- How to Run Goal Seek Automatically in Excel
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Average in Excel: Formula, Examples, and Common FixesTo calculate an average in Excel, use the AVERAGE function: with numbers in B2:B10, enter =AVERAGE(B2:B10). Excel returns…
- Cash Flow Statement in Excel: Formulas and SetupYes. In Excel, record cash receipts and payments in a transaction log, use SUMIFS to total them by…
- XLOOKUP Excel Conditional Search: Formulas and ExamplesTo use XLOOKUP for a conditional search in Excel, put the condition in its lookup array; for multiple…
- Insert a Horizontal Page Break in Excel the Right WayTo insert a horizontal page break in Excel for Microsoft 365 on Windows, select the row directly below…
- Excel Sort Numbers Low to High: Steps, Tips, and FixesTo sort numbers from low to high in Excel for Microsoft 365 on Windows, click a cell in…
- VLOOKUP Examples Using Two SpreadsheetsTo look up a value in a separate Excel workbook, put the lookup key in your current sheet…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.