To delete duplicate rows in Excel for Microsoft 365 on Windows, select your data, go to Data > Remove Duplicates, choose the columns that define a duplicate, and select OK. Excel keeps the first matching row in the current order and removes later matches, so check your selection before confirming.
What Excel means by a duplicate
A duplicate is not necessarily a row that looks identical across every column. In Excel’s Remove Duplicates tool, you decide which columns count when comparing records. If you select every column, Excel removes only rows whose values match across all those columns. If you select just one column, such as Email, it treats rows with the same email address as duplicates even if their names, dates, or other details differ.
For example, imagine a customer list with Name, Email, and City. Two rows for Alex Lee with the same email but different cities are duplicates if you select only Email. They are not duplicates if you select all three columns and the city values differ. The right choice depends on what makes a record unique in your data: an email address, an order number, a combination of fields, or the entire row.
Remove Duplicates changes the selected data; it is not merely a highlight or filter. That makes it useful for cleaning a list, but it also means the choice of columns matters. A broad selection may retain unwanted repeats, while a narrow one may delete legitimate records.
Prepare the worksheet before removing rows
Make a copy of the workbook or duplicate the worksheet before a large cleanup. This gives you a fallback if the wrong columns are selected or if you later discover that two records should not have been treated as the same. The command can usually be undone immediately with Ctrl+Z on Windows or Command+Z on Mac, but undo history can be lost as you continue working or close the file.
Check that the data forms a clear rectangular range with one header row and consistent columns. Remove merged cells from the data area if possible, and avoid selecting unrelated notes or totals beside the list. If the range has blank rows or columns separating sections, Excel may not identify the intended data block automatically.
- Confirm the header names and check that each column contains the kind of information its heading describes.
- Decide what uniquely identifies a record before opening the tool.
- Sort or otherwise arrange the data first if you have a preferred record to keep. Excel retains the first occurrence in the current order.
- Save a backup when the data is important, shared, or difficult to recreate.
Formatting is not a reliable way to distinguish records for this command. Two rows that differ only in color or font may still be treated as duplicates if their selected cell values match. Review the actual contents of the columns you plan to use.
How to remove duplicates in Excel
In Excel for Microsoft 365 on Windows, use these steps:
- Click a cell inside the data range, or select the complete range you want to clean. Include all columns belonging to each record, not just the column you plan to compare.
- Open the Data tab and select Remove Duplicates in the Data Tools area.
- In the dialog box, confirm whether My data has headers is checked. Leave it checked when the first row contains column names; otherwise, Excel may treat a heading like ordinary data.
- Use the column checkboxes to define a duplicate. Select all columns to compare complete rows, or select only the field or combination of fields that identifies a repeated record.
- Select OK. Excel reports how many duplicate values were found and removed and how many unique values remain. Read that message, then inspect the resulting list.
If you start with a cell inside a contiguous block, Excel generally detects the surrounding data. If it asks whether to expand the selection, choose the option that includes the full records when you want to remove entire rows. Sorting only one column or selecting only one field can separate information that belongs together, so take care not to create a mismatched table.
The command is also available in Excel for Mac and Excel for the web under Data > Remove Duplicates, although ribbon layout and dialog appearance can vary. The basic choice—headers and which columns define duplicates—is the same. If the command is not visible, check that you are editing a worksheet range and that the Data tab is available in your version.
Choose the right columns to compare
Think of the checked columns as the rule for equality. Selecting one column means repeated values in that field are enough to remove a row. Selecting several columns means the combination must match. Selecting all columns means a row is removed only when the selected values match the corresponding values in another row.
- One identifier: Select Email to keep one record per email address, even if other fields vary.
- A combination: Select Product and Batch Number if a product can legitimately appear in more than one batch.
- Every column: Select all columns to remove exact repeated records while preserving rows that differ in any selected field.
Consider a sales list with Date, Customer, Product, and Amount. If you select Customer alone, Excel may delete separate purchases by the same person. If the purpose is to remove repeated transaction records, select the fields that together identify a transaction, perhaps Date, Customer, Product, and Amount—or, better, a unique transaction ID if one exists. The business meaning of a duplicate is a data decision; Excel cannot infer it.
Excel keeps the first matching record it encounters in the current order and removes later matching records. It does not automatically decide that the newest, most complete, or most accurate row is best. If you need to preserve the latest entry, sort by date first so the desired row comes first, then remove duplicates using the appropriate key. Verify the sort and key columns before deleting.
What changes, and what does not
Remove Duplicates deletes repeated rows from the selected range; it does not simply hide them. Other worksheet content outside that range is not intended to be cleaned by the command, but nearby formulas, references, charts, or summaries may be affected when their source rows change. If the data is part of an Excel table, the removed records disappear from that table as well.
The tool reports a count after it runs, which is a useful first check, not proof that the outcome is correct. Inspect a few known examples and confirm that expected unique records remain. If you selected too few columns, distinct records may have been lost. If you selected too many, duplicates according to your chosen business rule may still be present.
For a small list, Ctrl+Z on Windows or Command+Z on Mac can reverse the action if it is still the most recent undoable change. If you have performed other actions, closed the workbook, or need to preserve a reliable original, restore from your saved copy instead. Ctrl+Z is not a substitute for a backup when deleting important data.
Review duplicates without deleting them
If you want to examine repeats first, use conditional formatting. Select the cells to check, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Excel applies a visual highlight to repeated values in the selected range. This is useful for a quick review, but it does not identify repeated full records based on a multi-column rule as flexibly as Remove Duplicates.
Another option is to copy the data to a separate sheet and run Remove Duplicates on the copy. That lets you compare the result with the original and keep the source unchanged. For a larger review, filters or a helper column can help isolate records, but the comparison rule still needs to be defined carefully. A repeated surname, for instance, is not necessarily a repeated person.
Alternatives for repeatable or non-destructive cleanup
For a one-time cleanup, Remove Duplicates is usually the quickest built-in choice. If you need a list of distinct values without changing the source, Excel for Microsoft 365 also has the UNIQUE function. With names in cells A2:A6 containing Ana, Ben, Ana, Cara, and Ben, enter =UNIQUE(A2:A6) in an empty cell outside the source list. It returns Ana, Ben, and Cara once each, in first-seen order, while leaving the original values intact. The spilled result updates when the source range changes. UNIQUE is available in Microsoft 365 and Excel 2021 or later.
For repeated imports or recurring cleanup, Power Query can preserve the original data and apply repeatable steps to a query result. Select a cell in the data, choose Data > From Table/Range, and, in the Power Query Editor, select the relevant column or columns before choosing Home > Remove Rows > Remove Duplicates. Then choose Home > Close & Load to return the result to Excel. The exact editor layout can vary slightly by platform.
Power Query is useful when you will refresh the same cleanup process with new source data. It does not mean the source list has been permanently edited: the query produces a cleaned result that can be refreshed. You still need to select the right key columns, and you should check the output before using it. For a simple one-off task, its extra setup may not be worthwhile.
Common problems and misconceptions
- It removed rows I wanted to keep: The selected columns may have defined duplicates too broadly. Undo if possible or restore the backup, then choose a more specific key.
- Some repeats remain: You may have selected every column, including one that varies, or the values may contain differences such as extra spaces. Check the fields and normalize inconsistent text before trying again.
- The heading disappeared: Check the header setting in the dialog. If the first row is a heading, mark that the data has headers before running the command.
- It kept the wrong version: The first row wins, not necessarily the newest or most complete one. Put the record you want to retain first by sorting before removal.
- It removed repeated names that are different people: A name alone is often not a unique identifier. Compare a combination such as name and date of birth only where appropriate, or use a reliable ID.
Spaces and inconsistent text are frequent reasons a list appears to contain exact duplicates while Excel treats entries differently, or vice versa according to the selected fields. For example, a name with an extra trailing space may not match the same name without it. Clean inconsistent source values first and retain a copy of the original if the cleanup could change meaningful distinctions.
Frequently Asked Questions
Does Excel keep the first or last duplicate?
Remove Duplicates keeps the first matching row in the current order and removes later matches. Sort the data first if you want a particular version to be first.
Can I remove duplicates based on one column but keep the whole row?
Yes. In the dialog, select the one column that defines a duplicate, while applying the command to the full data range. Excel removes later matching rows, including their other column values.
Does Remove Duplicates distinguish uppercase and lowercase?
Do not rely on letter case to preserve otherwise matching entries. If case carries meaning in your data, check the result carefully and use a comparison method designed around that requirement.
Can I find duplicates without deleting them?
Yes. Use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values to highlight repeated values in a selected range, or make a copy and remove duplicates from the copy for comparison.
Can I undo Remove Duplicates?
Usually, immediately after the command, use Ctrl+Z on Windows or Command+Z on Mac. If you have continued working or closed the workbook, restore a saved copy or use the workbook’s available version history.
Related
- Cell in Microsoft Excel: Meaning, Types, and How to Use It
- Count Numbers vs Count in Excel PivotTables
- Copy Paste in Excel: Keep Column Widths When Pasting
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula: Capitalize Only the First Letter in TextTo capitalize the first character in a cell and make the remaining characters lowercase, use =UPPER(LEFT(A2,1))&LOWER(MID(A2,2,LEN(A2))), assuming the…
- Excel Formula for Age From Date of BirthThe simplest Excel formula for age from a date of birth is =DATEDIF(A2,TODAY(),"Y"), assuming the birth date is…
- Excel Formula for Month and YearIf the date is in A2, enter =TEXT(A2,"mmmm yyyy") in B2 to display its month and year, such…
- Concatenate Names in Excel With a Comma?To concatenate names in Excel with a comma and a space, use =TEXTJOIN(", ",TRUE,A2:A4) when the names are…
- ROUNDUP Excel Cell Reference: 10 Useful Formula PatternsWith 12.31 in A2, enter =ROUNDUP(A2,0) to return 13; the formula reads the number from a cell and…
- Excel: How to Hide Columns to the Right of Your DataTo hide every column to the right of your data in Excel for Microsoft 365 on Windows, select…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.