In Excel for Microsoft 365 on Windows, select the data you want to clean, then go to Data > Remove Duplicates. Choose the columns that define a duplicate and confirm; Excel keeps the first matching record and removes later rows that match it.
Where to find Remove Duplicates
The command is on Excel’s Data tab, in the Data Tools group. If the ribbon is condensed or the Excel window is narrow, the group may appear as a smaller set of icons or a menu. Selecting your data first makes it easier to check that Excel is working on the intended range. You can select a cell inside a continuous block, but inspect the selected columns in the dialog before proceeding.
On a worksheet, you can also convert a range to an Excel table before cleaning it: select a cell in the data and choose Insert > Table, then confirm whether the first row contains headers. The Remove Duplicates command remains on the Data tab. Tables make it easier to identify field names and keep a growing dataset organized, but they do not change the basic duplicate-removal rules.
On Excel for Mac, select the data and use the Data tab’s Remove Duplicates command; the ribbon arrangement and dialog appearance can vary slightly. In Excel for the web, available commands and ribbon layout can depend on the current interface. If you cannot find the command there, open the workbook in the desktop app.
How to remove duplicates step by step
- Make a backup or copy of the original data, especially if it is important or difficult to recreate.
- Select the complete data range, including every column that belongs to each record. Include the header row if there is one.
- Choose Data > Remove Duplicates.
- In the dialog, confirm whether My data has headers should be checked. Then select the columns Excel should compare.
- Click OK. Excel reports how many duplicate values were removed and how many unique values remain. Review the resulting rows and save the workbook if they look right.
The key decision is the column selection. Excel compares values in the columns you check, row by row. It does not ask you to pick one row to delete from each duplicate group: it preserves the first matching row in the current order and removes the later matches. If you need a particular record to survive, sort the data into the desired order before running the command.
Choose columns that define a duplicate
Think about what makes two records the same in your dataset. For a contact list, email address may be a better identifier than a person’s name, since two people can share a name. For an order table, an order number might identify an order, while a customer name alone would not. Selecting too few columns can merge records that are different in an important way; selecting too many can leave apparent duplicates untouched.
For example, consider these rows:
- Jordan Lee | jordan@example.com | Portland
- Jordan Lee | jordan@example.com | Portland
- Jordan Lee | jordan@example.com | Salem
If you select only the email column, Excel treats all three rows as duplicates because the email matches, and keeps the first one. That would discard the Salem record. If you select all three columns, only the first two rows match, so Excel removes the repeated Portland record and keeps the Salem record. This is why choosing the right comparison fields matters more than simply selecting every column by habit.
When you check several columns, Excel evaluates their combination. A match means the values in all checked columns match for that row; it does not mean that any one of the selected values can match independently. If your rule is that an email address must be unique but you want to retain the complete original row, select the email column as the duplicate criterion while including the complete dataset in the range.
What happens to rows and which record stays
Remove Duplicates removes entire rows from the selected data range, not just repeated cell contents. If you check one column as the criterion, a repeated value in that column can cause a later row to be removed along with its other fields. The data in unrelated columns outside the selected range is not a safe place to store information that must remain attached to those records: selecting only part of a table can separate values from their corresponding rows.
The first row in each matching group is retained according to the order of the data when the command runs. Excel does not automatically keep the newest date, the largest amount, or the most complete entry. If you want to keep the latest record, sort by the relevant date from newest to oldest before removing duplicates, and confirm that this sort order puts the preferred record first. Sorting can affect the whole table, so sort the complete dataset rather than a single column on its own.
The command changes the worksheet data, but a removal can usually be reversed immediately with Ctrl+Z on Windows or Command+Z on Mac. Undo is not a substitute for a backup: after additional edits, closing the workbook, or other actions, restoring the earlier state may be inconvenient or unavailable.
Prepare the data before cleaning it
Two entries that look alike may not be identical for the comparison you intend. Inconsistent spaces, different spellings, abbreviations, and mixed data types can all affect the outcome. For example, a customer name with an accidental trailing space may not behave like the same name without that space. Standardize the relevant fields before deduplicating, and check a sample of suspicious values rather than assuming that visual similarity proves they are equivalent.
- Check headers: Make sure the first row contains field names, not an ordinary record. Set the header option accordingly so a heading is not treated as data.
- Check blanks: If you compare only an ID column, multiple records with a blank ID may be treated as duplicates, even when their other fields differ. Decide whether blank identifiers should be fixed, excluded, or reviewed separately.
- Check the range: Include every field that belongs to the records and avoid including unrelated notes or totals below the table.
- Check sort order: Put the record you want to retain first within each duplicate group.
- Save a copy: Preserve the original before applying a destructive cleanup to business, research, or reporting data.
There is also an important distinction between duplicate rows and duplicate values. A repeated city in a City column is not necessarily a duplicate record: many different customers can live in the same city. The tool follows the columns you select, so define a meaningful record key rather than treating every repeated word as an error.
Common problems and how to avoid them
Excel removed rows I wanted to keep. The selected columns may have defined duplicates too broadly, or the first row in a group may not have been the preferred one. Undo immediately, inspect the criteria, and sort the full dataset so the correct record comes first before trying again.
Duplicates remain after cleanup. You may have checked too many columns, so a difference in an unimportant field prevented rows from matching. Conversely, inconsistent spaces or spelling can make entries that look alike behave differently. Review which fields define a duplicate and standardize those fields before rerunning the tool.
Only one column changed or the data no longer lines up. This can happen when you select only one column from a wider range and choose to continue with that selection instead of expanding it. For a dataset of records, select the entire range first and then use the dialog’s column checkboxes to decide which fields establish a duplicate. Do not sort just one column independently of the rest of the data.
The header was included as a record. Reopen the command after undoing and confirm the header setting. A heading such as Email or Date should be treated as a header if it labels the data, rather than as a value to compare against the rows below it.
For text comparisons where capitalization or other normalization details matter, do not assume the button offers a custom matching rule. Normalize the data first or use a method that lets you inspect the results before replacing the source. A helper column, a carefully designed formula, or a Power Query transformation can make the matching logic clearer.
Remove Duplicates compared with other methods
Use the Data tab command when you want a one-time cleanup of a worksheet and are ready to remove redundant rows. It is quick, but it changes the source range. If you want to mark possible repeats before deciding what to delete, use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values on a selected field. That highlights repeated cell values; it does not decide whether complete records are duplicates or remove any data.
For a separate, automatically updating list of distinct rows, Excel 2021 and Microsoft 365 include the UNIQUE function. With headers in row 1 and example records in A2:B6—Mia | North, Omar | South, Mia | North, Mia | West, and Omar | South—enter =UNIQUE(A2:B6) in an empty cell. The result spills as three rows: Mia | North, Omar | South, and Mia | West. This compares complete rows in the supplied range and leaves the source data in place. The output area must be clear for the results to spill; otherwise Excel can return a spill error.
Power Query is a better fit for repeatable imports or larger cleanup routines. In the Power Query Editor, select the fields that define a duplicate and use Home > Remove Rows > Remove Duplicates. You can then refresh the query when the source data changes. As with the worksheet command, choosing the fields and deciding which record should be retained are important; inspect the result before loading it back to the workbook.
These approaches serve different purposes: Conditional Formatting flags values, UNIQUE creates a distinct output without deleting the source, Power Query records a repeatable transformation, and Data > Remove Duplicates directly removes matching rows from the selected worksheet range.
Frequently Asked Questions
Does Remove Duplicates delete the first occurrence?
Usually, no. Excel keeps the first matching row in the current order and removes later matching rows. Sort the full dataset first if you need a particular record to be kept.
Can I remove duplicates based on one column but keep the other columns?
Yes. Select the full data range, open Remove Duplicates, and check only the column that defines a duplicate. Excel removes later matching rows from the range, including their values in the other columns.
Can I undo Remove Duplicates?
Use Ctrl+Z on Windows or Command+Z on Mac immediately after the operation. Keep a backup when the original data is important, since undo may not be available after later actions or after closing the workbook.
Does Excel remove duplicate values or duplicate rows?
The command removes rows from the selected range. It decides which rows match by comparing the columns you checked, so selecting one column can remove a whole row based on a repeated value in that field.
How can I find duplicates without deleting them?
Use Conditional Formatting to highlight repeated values in a selected field, or use UNIQUE in Excel 2021 or Microsoft 365 to create a distinct list in another area. Both let you inspect results without immediately removing the source rows.
Related
- Multiple Text to Columns in Excel: Methods That Work
- Excel Split Cell Into Columns: Methods and Tips
- Excel Set Print Area: Steps, Tips, and Fixes
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Cash Flow Excel Sheet: Build a Reliable Monthly ForecastA cash flow Excel sheet tracks when money comes in and goes out, then calculates the cash left…
- How to Add Multiple Footnotes in Word and Manage ThemTo add multiple footnotes in Microsoft Word, place the cursor where a note marker should appear, choose References…
- Excel Data Validation Drop-Down Lists: Setup and FixesTo create a drop-down list in Excel for Microsoft 365 on Windows, select the cells, choose Data >…
- How to Copy a Line in Excel: Rows, Cells, and ShortcutsTo copy a line in Excel, select the row by clicking its number, press Ctrl+C, click the number…
- Creating Rules in Outlook: Setup and TroubleshootingTo create a rule in Outlook, open the Rules controls, choose a condition such as a sender or…
- Conditional Formatting for Duplicates in ExcelIn Excel for Microsoft 365 on Windows, select the cells, then choose Home > Conditional Formatting > Highlight…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.