To remove duplicates in Excel for Microsoft 365 on Windows, select a cell in your data, choose Data > Remove Duplicates, select the columns Excel should compare, and click OK. Excel deletes repeated rows and keeps the first occurrence, so make a copy of the data first if you may need the original.
What Excel means by duplicates
Excel’s Remove Duplicates command works on rows of data. It checks the columns you select and removes a later row when its values match an earlier row in those columns. You can compare every column in a table, or only the fields that define a duplicate for your task.
For example, suppose a customer list has a customer ID, name, and city. If you select all three columns, two rows are duplicates only when the ID, name, and city match. If you select just the customer ID column, Excel treats rows with the same ID as duplicates even if their names or cities differ. That second choice can remove useful information, so decide what makes a record duplicate before running the command.
The command does not combine information from repeated rows or reconcile conflicting details. It keeps one occurrence and removes the others. If two rows share an ID but have different contact details, review them rather than assuming either row is safe to discard.
Prepare the data before deleting rows
Start by making sure the data is structured consistently. A single header row above a rectangular block of records makes it easier to identify the correct range and column names. Avoid selecting unrelated notes or totals alongside the list. If your data is already an Excel table, click inside it; the table’s headers will be available in the removal dialog.
- Save a recoverable copy. Duplicate the worksheet or workbook, or save a separate copy before making a destructive change.
- Check for blank rows and columns. Gaps can make Excel interpret the data as separate regions or complicate selection.
- Choose the rule first. Decide whether a duplicate means an identical whole row, a repeated email address, an order number, or another key.
- Consider sorting. If rows with the same key have different details, sort or filter the list so you can review those cases before deleting anything.
Excel keeps the first matching row it encounters in the selected range. If you want to keep the newest or most complete record, sort the data into the order you want before using Remove Duplicates. For example, sort by a date column from newest to oldest if the newest entry should be retained. Check that the sort includes the entire table, not just one column, so values remain attached to the correct records.
Remove duplicate rows step by step
- Click any cell inside the data, or select the full range you intend to clean.
- Open the Data tab and choose Remove Duplicates.
- If the first row contains headings, leave My data has headers selected. Confirm the displayed column names and check only the columns that define a duplicate.
- Click OK. Excel reports how many duplicate values were found and removed, and how many unique values remain.
- Review the remaining rows and the report. If the result is not what you intended, undo immediately or restore the saved copy and repeat with different columns selected.
If you select one cell in a contiguous block, Excel usually detects the surrounding data. If you select a specific range first, verify that the intended columns and all relevant rows are included. When Excel asks whether to expand a selection after you select only part of a table, choose the option that keeps each row’s fields together. Removing duplicates from one column alone can break the relationship between that column and the adjacent data.
In Excel for Mac, the command is also on the Data tab as Remove Duplicates, though the ribbon layout can differ slightly by release. In Excel for the web, look for Remove Duplicates on the Data tab; if the command is unavailable in your current interface, open the workbook in the desktop app. The desktop steps above describe Microsoft 365 for Windows.
Choose the columns that define a match
The checkboxes in the dialog are the most important decision. Selecting every column is appropriate when you want to remove rows that repeat exactly across the selected fields. Selecting a subset is useful when one field is a unique key, but it means differences in unchecked columns do not protect a row from removal.
Consider this example: two rows have the same product code, but one says the item is in the East warehouse and the other says it is in the West warehouse. Selecting only Product Code makes those rows duplicates. Selecting Product Code and Warehouse keeps both. Neither choice is universally right; the correct choice depends on whether the list represents products or product locations.
When a repeated key has conflicting details, do not use Remove Duplicates as a way to decide which information is correct. Filter or sort by the key, inspect the conflicting rows, and determine a rule for retaining or combining them. Excel’s command does not ask which version to keep, nor does it merge nonmatching fields.
What Excel compares—and what can surprise you
Excel checks cell contents in the selected columns, not visual formatting such as font color or fill color. Two cells that look alike may still contain different text if one has a leading or trailing space, a different punctuation mark, or an extra character. Conversely, identical displayed formatting does not prove that the underlying records match.
Numbers stored as numbers and numbers stored as text can behave differently in sorting, filtering, and other data operations. Imported data can also contain nonprinting characters or inconsistent spacing. If a row you expect to be a duplicate remains, inspect the values and clean inconsistent data before trying again. Functions such as TRIM can help with ordinary extra spaces, but they do not remove every kind of invisible character.
Case differences alone are generally not a reliable way to distinguish records in Excel’s duplicate-removal workflow. If capitalization carries meaning in your data, confirm the result on a copy and use a deliberately designed comparison instead of assuming the command will preserve case variants as separate records.
Preview duplicates without deleting them
If you want to inspect the likely duplicates first, use conditional formatting or a helper formula. On the Home tab, choose Conditional Formatting > Highlight Cells Rules > Duplicate Values to highlight repeated values in a selected range. This is useful for a single field such as email addresses, but highlighting a column does not by itself tell you whether whole rows match.
To flag repeated IDs after the first occurrence, with IDs in A2:A100, enter this in B2 and fill it down: =COUNTIF($A$2:$A2,A2)>1. The first time an ID appears, the result is FALSE; later appearances return TRUE. This checks only the ID column, so it flags repeated IDs even if other columns differ. Use it to review records before deleting, not as proof that every field in the rows is identical.
Another safe approach is to copy the data to a new worksheet and run Remove Duplicates on that copy. You can compare the cleaned result with the source, especially when the data is important or the duplicate rule is not obvious.
Alternatives for keeping the original data
For a non-destructive list of unique records, the UNIQUE function returns distinct values or rows in a separate area. It is available in Microsoft 365 and Excel 2021 or later. With records in A2:C6 and the output formula entered in E2, =UNIQUE(A2:C6) returns each distinct three-column row once. If the rows are 101, Ana, North; 102, Bo, South; 101, Ana, North; 103, Cy, West; and 102, Bo, South, the result contains three rows: 101/Ana/North, 102/Bo/South, and 103/Cy/West. The original records remain unchanged.
UNIQUE is useful when you need a refreshable result: changing the source data recalculates the spilled list. It returns unique rows based on the full range supplied. If you want distinct values from just one column, supply that column’s range instead. It is not available in older perpetual versions such as Excel 2019, so use Remove Duplicates on a copy or another compatible method when working in those versions.
Power Query is another option for repeatable cleanup, particularly when importing the same kind of file regularly. In Excel for Windows, load the range or table into Power Query, select the comparison columns, then use Home > Remove Rows > Remove Duplicates in the Power Query Editor. Load the result back to a worksheet. You can refresh the query when the source changes, but the columns selected in the query still determine what counts as a duplicate.
Common problems and how to avoid them
- Too many rows disappear: You may have selected a key column whose repeated values occur in records with different details. Restore the data and include the other fields needed to define a true duplicate.
- Expected duplicates remain: Check for spaces, hidden characters, inconsistent data types, or a column you forgot to include. Compare the actual cell contents rather than relying only on appearance.
- Headers are treated as data: Check the My data has headers setting in the dialog and confirm that the first row really is a header row.
- Data no longer lines up: Avoid processing one column separately when its values belong to complete records. Select the full table so Excel removes whole rows together.
- The wrong version was kept: Sort the full dataset into the desired order before running the command, because Excel retains the first occurrence it encounters.
Always look at the removal summary, but do not treat it as a complete audit. It tells you the number of duplicates removed and unique values remaining; it does not explain which conflicting records were discarded or whether your chosen columns reflected the right business rule.
Undoing a removal and protecting your work
Immediately after removing duplicates, use Ctrl+Z on Windows or Command+Z on Mac to undo the operation. Undo is most dependable before you make further edits or close the workbook. If you have continued working, saved over the file, or need a reliable record of the original, use a backup or version history where available rather than assuming the action can still be undone.
For valuable data, preserve the original sheet and perform cleanup on a copy. This simple precaution also makes it easier to compare before-and-after row counts, investigate a mistaken deletion, or rerun the process with a more appropriate set of columns.
Frequently Asked Questions
Does Remove Duplicates delete the whole row?
Yes. Excel removes the later row from the selected range when its values match an earlier row in the columns you selected. It does not delete only the matching cell.
Does Excel keep the first or last duplicate?
It keeps the first occurrence in the current order of the selected data and removes later matches. Sort the full range first if you want a particular record to appear first.
Can I remove duplicates based on just one column?
Yes. In the dialog, select only that column. Excel will then remove rows that repeat its value, even if other columns in those rows are different.
Can I find duplicates without deleting them?
Yes. Use Conditional Formatting > Highlight Cells Rules > Duplicate Values for a quick visual check, or use a helper formula such as COUNTIF to flag repeated values for review.
Why does Remove Duplicates keep rows that look identical?
The cells may contain differences that are not obvious, such as extra spaces, hidden characters, or values stored in different forms. Inspect and clean the data, then review the selected columns before trying again.
Related
- Excel Text to Columns: Split at the Last Space
- Excel Text to Columns: Split at Line Breaks
- Excel Text to Columns Greyed Out? Causes 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
- How to Find Duplicate Lines in Excel Without Deleting ThemTo find duplicate lines (rows) in Excel without deleting anything, select the data rows and create a formula-based…
- How to Make Two Columns in a Word Document QuicklyTo make two columns in Microsoft Word for Microsoft 365 on Windows, select the text you want to…
- How to Clear Track Changes in WordTo clear tracked edits in Word, review them and choose Review > Accept > Accept All Changes to…
- Create a Filter View in Google Sheets: Steps & TipsTo create a filter view in Google Sheets, open the spreadsheet in a desktop web browser, select a…
- Insert Rows and Columns in Excel: Shortcuts & StepsIn Excel for Microsoft 365 on Windows, select a row with Shift+Space or a column with Ctrl+Space, then…
- How to Add a Header in Excel for All PagesTo add a header to every printed page in an Excel worksheet, select the sheet, choose Insert >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.