To remove duplicates in Excel for Microsoft 365 on Windows, select your full data range, choose Data > Remove Duplicates, select the columns that define a duplicate, and click OK. Excel keeps the first matching record and removes later ones, so make a copy of the data first if you may need to restore it.
What You’ll Need
- An Excel workbook with the records you want to clean up.
- A clear idea of what counts as a duplicate: an identical full row, or a repeated value in one or more particular columns.
- A backup copy of the worksheet or workbook if the original records matter. Removing duplicates changes the data rather than merely hiding repeated entries.
Step-by-Step
- Make a safe copy. If the data is important, save a separate copy of the workbook using File > Save As, or duplicate the worksheet before making changes. You can also copy the data to another sheet. Excel’s Undo command can reverse the removal immediately, but a saved copy is safer if you continue working or close the file.
- Check how your records are arranged. Each record should occupy one row, with field names such as Customer, Email, or Order ID in the first row. Avoid blank rows or columns inside the data block, because they can make it harder to select the complete range. If the range is already an Excel Table, you can use the same removal command on it.
- Select the complete range of records. Include every column that belongs to each record, including the header row if there is one. For example, when a row contains a name, email address, and status, select all three columns rather than just the name column. This keeps the remaining fields together when Excel removes a row. If your data is a table, click a cell in it and confirm the table contains the records you intend to clean.
- Open the removal dialog. On the ribbon, choose Data > Remove Duplicates. If you selected a range with column headings, the dialog includes the My data has headers checkbox. Confirm that it is checked when the first row contains labels; otherwise, Excel may treat a heading as a record. If the range has no headings, leave the box unchecked.
- Choose the columns Excel should compare. The dialog lists the selected columns. When every field must match for two rows to count as duplicates, leave all relevant columns checked. To find repeated email addresses regardless of differences in other fields, select only the email column. Excel will then remove later rows that repeat an email, even if their names or status values differ. This choice determines what is deleted, so review it before continuing.
- Run the command and read the result. Click OK. Excel keeps the first row it encounters for each matching set and removes later matching rows from the selected range. A message reports how many duplicate values were removed and how many unique values remain. Read that message to make sure the number looks reasonable. Click OK again to close it.
- Inspect the cleaned data. Check the first and last records, several examples that used to repeat, and any columns that should have stayed attached to their original rows. If the wrong record was kept, immediately press Ctrl+Z on Windows to undo the removal, then sort the data so the preferred record appears first and repeat the process. On Mac, use Command+Z.
On Excel for Mac, the command is also available from the Data tab as Remove Duplicates, although the ribbon layout and dialog appearance can vary by release. The same key decision applies: select the full record range and choose which fields determine whether rows match.
Common Mistakes
Selecting only one field from a multi-column list. If each row is a complete record, removing duplicates from just one selected column risks separating that field from related information or removing a row based on a match you did not intend. Select the whole data range, then use the dialog to specify which columns should be compared.
Checking every column when only one field should define a match. With all columns checked, two rows are duplicates only when their values match across all those selected columns. If one row has a different status, note, or date, Excel can keep both even when the email address is repeated. Uncheck fields that should not affect the duplicate decision.
Forgetting the header setting. If My data has headers is incorrectly set, Excel can include the heading in its comparison or treat a real first record as a heading. Confirm the setting against the first row of the selected range, not against the worksheet’s appearance elsewhere.
Assuming Excel keeps the newest or best record. The command keeps the first matching row in the current order. It does not decide which entry is most complete or most recent. Sort by a date or quality indicator first if you want a particular row to be retained.
Expecting near-matches to count as duplicates. Values with extra spaces, different punctuation, or slightly different spellings may not match. Remove Duplicates does not automatically decide that two similar-looking entries mean the same thing.
Tips
- Decide the matching rule before opening the dialog. For a contact list, you might compare email addresses; for transactions, you might compare an order number and line number together. Write down the rule if the data is complex. A good rule prevents accidentally deleting valid records that share only one common detail.
- Control which matching row survives. Sort the full range first so the record you want to retain appears before its duplicates. For example, sort a date column with the newest date first if you want to keep the latest entry. Sort the entire table or range, not just the date column, so values remain with their records.
- Use filters or conditional formatting for review before deletion. If you are uncertain about the matching rule, first highlight or filter likely repeats and inspect them. This lets you check whether rows are truly redundant before using a command that removes data.
- Keep an audit copy when cleaning shared or operational data. Save the original range on a separate sheet or in a dated workbook. This is especially useful when a teammate needs to verify which records were removed or when the rules may change later.
- Consider a formula when you need a separate, refreshable result. In Microsoft 365 or Excel 2021, UNIQUE can return distinct rows without deleting the source. With complete records in A2:C4, enter
=UNIQUE(A2:C4)in an empty cell; if the rows are Sam, North, 12; Lee, South, 8; and Sam, North, 12, the spill result contains the first two distinct rows. This compares complete rows, not just one chosen field. Leave enough empty cells below and to the right for the results.
Troubleshooting
The result says no duplicates were found. First check the selected columns. If all fields are checked, rows that differ in even one included field will remain. If you intended to deduplicate by email, for example, select the whole data range but leave only the email column checked in the dialog. Also look for leading or trailing spaces, inconsistent punctuation, and values stored differently, such as a number in one row and text in another. Correct or standardize the source data, then run the command again.
Excel removed more rows than expected. Undo immediately with Ctrl+Z on Windows or Command+Z on Mac. Review the checked columns and confirm that the comparison rule was not broader than intended. If the data was sorted, verify that you sorted the full range and not a single column by itself. Restore from the backup if Undo is no longer available, then repeat with the right selection and settings.
The wrong version of a repeated record remained. Undo the removal, sort the full table by the field that determines which version is preferred, and make sure the preferred record comes first. Then rerun Remove Duplicates using the same comparison columns. Excel retains the first match in the order it sees; it does not automatically prioritize a newer date or a more complete row.
The command is unavailable or the dialog shows unexpected columns. Click inside the intended table or select the range again, including all needed columns. Check whether a sheet is protected, since protection can prevent edits; if appropriate, use Review > Unprotect Sheet and provide the required password. If the selected range has blank headings or multiple separated areas, make a clean, contiguous copy with one header row and try again.
Some apparent duplicates remain after cleanup. Compare the cells directly and look for invisible spaces or inconsistent characters. You can edit the affected values or use Excel’s Find and Replace to standardize known variations before running the removal again. Always review a sample after cleanup: similar-looking text may represent genuinely different records, and automatic deletion is only as reliable as the rule and source data.
Frequently Asked Questions
Does Remove Duplicates delete the whole row?
It removes duplicate records from the selected range, retaining the first matching row. Select all columns that belong to each record so the remaining data stays together. The checked columns decide which rows are considered a match.
Can I remove duplicates based on one column but keep other columns?
Yes. Select the complete data range, open Data > Remove Duplicates, and check only the column that defines a duplicate. Excel removes later rows with a repeated value in that field, including their other selected column values.
Can I choose which duplicate Excel keeps?
Yes, indirectly. Sort the full data range so the preferred record comes first, then run Remove Duplicates. Excel retains the first matching row and removes subsequent matches.
Can I undo removing duplicates?
Press Ctrl+Z on Windows or Command+Z on Mac right after the operation. Undo may no longer be available after later actions or after closing the workbook, so save a backup before removing records.
Does Remove Duplicates change the original data permanently?
It deletes later matching rows from the selected range, rather than producing a separate list. Save a copy first if you need the original records. For a distinct result that leaves the source in place, use UNIQUE in Microsoft 365 or Excel 2021.
Why does Excel keep rows that look identical?
They may differ in a checked column, or contain spaces, punctuation, or other hidden differences. Check the cell contents and the selected comparison columns, standardize values where appropriate, and run the command again.
Related
- Monthly Excel Budget Format: Build a Simple Template
- Year Calendar in Excel: Create a 12-Month View
- When Was Excel Created? The 1985 Launch Explained
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Table List: Find and Manage Your TablesTo see the tables in an Excel workbook, select a table and open the Table Design tab to…
- CAGR Return in Excel: Calculate Investment GrowthTo calculate CAGR in Excel, divide the ending value by the beginning value, raise the result to the…
- How to Make an A2 Poster in PowerPoint, Step by StepTo make an A2 poster in PowerPoint, set the slide to 42 × 59.4 cm (portrait) or 59.4…
- Excel Checkmark Keyboard Shortcuts: Windows, Mac, and WebIn Excel for Microsoft 365 on Windows, the quickest keyboard method is to select a cell, choose the…
- How to Highlight the Highest Number in Excel AutomaticallyTo highlight the highest number in an Excel range and keep the highlight updated when values change, select…
- Google Sheets Dropdown Chips: How to Convert ThemTo convert an existing Google Sheets dropdown into a chip, select a cell in the dropdown, open Data…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.