To remove duplicate rows in Excel for Microsoft 365 on Windows, select the full data range, then choose Data > Remove Duplicates. In the dialog, select the columns that define a duplicate and click OK; Excel keeps the first matching record and removes later ones from the selected range.
What Excel means by duplicate rows
A duplicate row is a record that matches another record according to the columns you choose. If you select every column in a table, Excel looks for rows whose values match across all those columns. If you select only some columns, Excel treats records with matching values in those columns as duplicates—even if other columns contain different information.
For example, a list might have columns for Order ID, Customer, Date, and Amount. Selecting all four columns removes only repeated records that match in every field. Selecting Customer alone instead treats every later row for the same customer as a duplicate, regardless of order date or amount. That second choice may discard valid orders, so decide what makes a record unique before removing anything.
Excel keeps the first occurrence it encounters in the selected range and removes subsequent matching rows. The first occurrence is based on the current row order, not on which entry is newest or most complete. Sort the data first if you need a particular record to survive.
Prepare the data before removing anything
Make a copy of the worksheet or workbook before a large cleanup. Removing duplicates changes the selected data, and a backup gives you a reliable way to recover if you choose the wrong columns or range. You can also copy the source data to another sheet and work on the copy.
- Check that the data is arranged in a continuous block, with one header row and no blank rows splitting the table.
- Include every column that belongs to each record. If you remove rows from a partial range, values in columns outside that range may no longer stay aligned with the records.
- Decide whether a header row is present. The dialog has a My data has headers checkbox; set it correctly so a heading is not treated as a record.
- Look for inconsistent entries such as extra spaces, different spellings, or values stored in different formats. These can make records that look alike fail to match.
For a quick safety check, note the current number of records or duplicate the sheet. Excel reports how many duplicate values it removes and how many unique values remain after the operation.
Remove duplicate rows step by step
- Click a cell inside the data, or select the entire data range. For a table with headers, include the headers and all related record columns.
- On the ribbon, choose Data > Remove Duplicates.
- In the dialog, confirm whether My data has headers should be selected.
- Use the column checkboxes to define the match. Select all columns to find fully identical rows, or select only the fields that together identify one record.
- Click OK. Read Excel’s summary, then review the remaining data before saving or sharing the workbook.
If you select a cell inside a contiguous data block, Excel generally identifies the surrounding range. Still, inspect the selected range and the dialog’s column names, especially when the sheet contains nearby notes or other tables. Selecting the intended range yourself is safer when the layout is irregular.
On Excel for Mac, the command is also available from Data > Remove Duplicates; the dialog may look slightly different. The same principle applies: choose the header setting and the columns that determine a match. In Excel for the web, check the Data tab for Remove Duplicates; availability and layout can vary with the web interface and workbook.
Choose the right columns for the match
The column checkboxes are the most important part of the operation. Think of the selected columns as a rule that identifies one record. A name by itself is often not unique, while a combination such as email address and account number may be. The right rule depends on the data, not on how many columns happen to be in the worksheet.
- All columns selected: removes rows only when every selected field matches. Use this when you want to clear repeated copies of complete records.
- One identifier selected: keeps one row per identifier. Use this only when that field is meant to be unique and you are comfortable discarding other rows with the same identifier.
- A combination selected: treats the chosen fields together as a key. For example, selecting Product and Store keeps one record for each product-store pair, even if the table also has a quantity column.
Suppose two rows have the same Customer ID but different invoice numbers. Selecting Customer ID alone removes one of the invoices; selecting all columns keeps both because the complete records differ. If the intended task is to retain one invoice per customer, decide which invoice should remain and sort the rows accordingly before removing duplicates. The command does not automatically choose the newest, largest, or best-quality record.
Concrete example: repeated customer records
Imagine a table with Name, Email, and City. Two rows contain the same name and email, but one lists Portland and the other lists Salem. If all three columns are selected, Excel keeps both rows because City differs. If Name and Email are selected, it treats the second row as a duplicate and keeps whichever of the two appears first.
That result may be correct if email identifies the customer and City is an outdated detail. It may be wrong if two people share a name and email was entered incorrectly. Before deleting, inspect the conflicting fields and determine which row should represent the record. A cleanup rule is only as reliable as the definition of a duplicate behind it.
When you want to retain the latest record, sort by a reliable date column with the preferred record first, then run Remove Duplicates using the identifying columns. Confirm that the sort included the full table; sorting only one column can scramble which values belong together.
Alternatives that preserve the source data
If you want a separate list of unique rows rather than deleting records, use the UNIQUE function in Excel for Microsoft 365 or Excel 2021 and later. With the complete records in A2:D100, enter =UNIQUE(A2:D100) in an empty cell. Excel spills a list containing one copy of each distinct row across those four columns, while leaving the original range unchanged. The result updates when the source range changes, subject to the range included in the formula.
UNIQUE compares whole rows when its array argument contains multiple columns and the optional by-column argument is omitted. It does not provide the same interactive review as Remove Duplicates, and the spill area must have room for the results. If the data is in an Excel table, structured references can make formulas easier to maintain, but choose a source range that excludes headers.
For a visual check before editing, select the field you want to inspect and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. This highlights repeated cell values in the selected area; it does not decide whether entire records match or remove any rows. Use it as a review aid, not as a substitute for choosing the correct row-level rule.
Power Query is useful for repeatable cleanup. Select a cell in the data and choose Data > From Table/Range to open the Power Query Editor. Select the columns that define duplicates, then choose Home > Remove Rows > Remove Duplicates. When you load the results and later refresh the query, Excel reapplies the recorded steps to the updated source. This is useful for recurring imports, but check which columns are selected and review the output before relying on the query.
Common problems and how to avoid them
- Rows that look identical remain: check for leading or trailing spaces, hidden characters, inconsistent punctuation, and numbers stored as text in one row but as numbers in another. Clean or standardize the data, then run the operation again.
- Different records disappear: too few columns may have been selected. Undo immediately with Ctrl+Z on Windows, if the action is still available, then repeat with a more specific set of columns. On Mac, use Command+Z.
- The wrong row survives: Excel keeps the first matching record in the current order. Sort the full dataset so the desired record comes first, then remove duplicates again.
- A heading is treated as data: correct the My data has headers setting in the dialog and check that the first row really contains headings.
- Related columns no longer line up: select the complete record range before removing duplicates. Avoid operating on a single column when other columns beside it belong to the same rows.
Excel’s matching is based on cell values, not on visual formatting such as fill color or font. A date displayed in a different format can still represent the same underlying value, while text that looks similar may differ because of spaces or characters. When a match matters, inspect the actual values and standardize them instead of relying only on appearance.
Frequently Asked Questions
Does Remove Duplicates delete the first occurrence?
Usually, no. Excel keeps the first occurrence in the selected range and removes later matches. Sort the full data range first if you want a particular version of a record to be kept.
How do I remove only completely identical rows?
Select the full range and keep every relevant column checked in the Remove Duplicates dialog. Excel will remove a later row only when its values match across those selected columns.
Can I remove duplicates based on just one column?
Yes. Select the full record range, then check only the column that defines a duplicate. Excel will remove later rows matching that field, so use this only when one row per value is genuinely what you want.
Can I undo Remove Duplicates?
Use Ctrl+Z on Windows or Command+Z on Mac immediately after the operation, if the action remains in the undo history. For important data, keep a copy because later actions or closing the workbook may make recovery harder.
Does Remove Duplicates distinguish uppercase and lowercase text?
Do not rely on capitalization alone to identify separate records. Standardize the text and test the rule on a copy when capitalization could affect the result; spaces and other hidden differences can also explain unexpected matches.
Related
- One-Column Data Table in Excel: How to Create One
- One-Dimensional Data Table in Excel: How It Works
- Numerical Solver in Excel: How It Works and When to Use It
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Timesheet Template in Word: Create and Use OneA timesheet template in Word is a document with a table for recording work dates, start and end…
- Excel Find and Replace: Steps, Options, and Common FixesTo find or replace content in Excel for Microsoft 365 on Windows, press Ctrl+H to open Find and…
- Excel Text Split: Stop at the First DelimiterYes. In Microsoft 365, use TEXTBEFORE to return the text before the first delimiter and TEXTAFTER to keep…
- Add a Confidential Watermark in Microsoft WordTo add a confidential watermark in Microsoft Word for Microsoft 365 on Windows, open the document and select…
- Excel Consolidate Worksheets Into One: Methods & TipsYes. To combine rows from several worksheets into one list, use Power Query’s Append feature, or use the…
- How to Create and Print Number Labels in Microsoft WordTo create sequential number labels in Microsoft Word, put one number per row in an Excel list, then…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.