To remove duplicates in Excel for Microsoft 365 on Windows, select your data, then choose Data > Remove Duplicates, select the columns Excel should compare, and click OK. Excel keeps the first matching row and permanently removes later matching rows from the selected range, so make a copy first if you may need the original data.
What Excel Means by a Duplicate
Excel does not decide that a row is a duplicate just because it looks similar to another row. The result depends on which columns you tell it to compare. If you select every column, Excel removes rows only when the values match across all those columns. If you select just one or two columns, rows that match in those columns count as duplicates even if other fields differ.
For example, consider a customer list with Name, Email, and City columns. If you select only Email in the Remove Duplicates dialog, two rows with the same email are treated as duplicates even if their names or cities differ. Excel deletes one whole row, not just the repeated email value. If you select all three columns instead, those rows are duplicates only if the name, email, and city all match.
This distinction matters when cleaning records. A repeated email might indicate the same customer, or it might be a shared address used by different people. Likewise, two identical product names might refer to different items if their product IDs differ. Decide what makes a record unique in your data before running the command.
Remove Duplicates Step by Step
- Save a copy of the workbook or duplicate the worksheet. Remove Duplicates changes the data directly.
- Click a cell inside the data, or select the full range you want to clean. Include all columns belonging to each record so Excel can remove complete rows rather than leaving fields out of sync.
- Choose Data > Remove Duplicates on the ribbon.
- In the dialog, check My data has headers if the first row contains column names. Then select the columns that define a duplicate. Use Select All if every column must match, or clear it and choose only the relevant fields.
- Click OK. Excel reports how many duplicate values it removed and how many unique values remain. Review the resulting rows and the message before continuing with other edits.
If Excel asks whether to expand the selection or continue with the current selection, choose carefully. Expanding can include neighboring columns so the entire record moves together. Continuing with only a partial selection can leave related data misaligned—for example, a customer name beside the wrong phone number. If the surrounding cells are unrelated, keep the selection limited to the intended data.
In an Excel table, click a cell in the table and use the same Data tab command. In Excel for Mac and Excel for the web, the ribbon layout can differ slightly, but the Remove Duplicates command is available from the Data tab in current versions. If you cannot see it, widen the app window or look for commands grouped or collapsed on the ribbon.
Choose the Columns That Define a Match
The most important decision is not whether to remove duplicates, but which fields should count as a match. A useful rule is to choose the smallest set of columns that identifies one real-world record reliably. A customer ID may be sufficient; a person’s name alone often is not. For sales data, an order number and line-item number may together identify a row more accurately than the customer name.
- Use all columns to remove only fully repeated rows, such as a row accidentally pasted twice with every field unchanged.
- Use an ID column when each record has a dependable unique identifier and repeated IDs indicate redundant records.
- Use multiple columns when uniqueness depends on a combination, such as First Name plus Last Name plus Date of Birth, or Product ID plus Warehouse.
- Do not use a descriptive field by itself without checking when different records can share it, such as a city, company name, or product description.
Excel retains the first occurrence it encounters in the selected data and removes later matches. If you want to retain a particular version, put it first before removing duplicates. For instance, to keep the newest customer record for each email address, sort the full data range by the update-date column with newest dates first, then run Remove Duplicates using Email only. Sort the entire range or table, not just the date column, so each row stays intact.
Prepare the Data Before Cleaning
Many apparent duplicates are not exact matches because the underlying text differs. A trailing space, a nonbreaking space copied from a web page, a different punctuation mark, or a hidden character can make two entries distinct. “Avery Lee” and “Avery Lee ” may look alike on screen but contain different text. Conversely, differences in capitalization may not be a dependable way to distinguish records; do not rely on letter case alone to preserve separate entries.
Before removing rows, consider standardizing the fields used for matching. Check for extra spaces, inconsistent abbreviations, and mixed formats. For example, “St.” and “Street” may refer to the same address but are different text. Dates can also be confusing when they are stored as text in some rows and as actual date values in others. Formatting changes how a value appears, but does not necessarily make the underlying values equal.
Filters can help you inspect likely duplicates, but filtering is not the same as deleting them. If a filter hides rows, make sure you understand whether the command will operate on the intended full data. A safer routine is to clear filters, confirm the table boundaries, and work on a copy before removing records.
Common Problems and How to Avoid Them
- Rows with different details disappear. You probably selected too few comparison columns. Undo immediately with Ctrl+Z on Windows or Command+Z on Mac, then run the command again with the correct fields.
- Only part of a record gets cleaned. The selection may not have included every related column. Restore the change if possible and select the complete dataset before retrying.
- Some obvious repeats remain. Check for spaces, inconsistent text, different date representations, or values that only look identical because of cell formatting. Standardize the fields and inspect the results.
- The wrong version of a record remains. Excel keeps the first matching row, not necessarily the newest or most complete one. Sort the full dataset into the desired order before removing duplicates.
- A row seems to vanish unexpectedly. Review the selected columns and the duplicate-removal summary. If you have not made more changes since the operation, use Undo; otherwise restore your saved copy.
Undo is useful for an immediate correction, but it is not a substitute for a backup. Once other actions have been performed, the undo history may no longer take you back to the original data. Keeping a source worksheet or a separate copy is especially important when cleaning business, research, or contact records.
Alternatives That Preserve the Original Data
If you want to inspect unique records without changing the source list, use the UNIQUE function in Microsoft 365 or Excel 2021 and later. For example, with a three-column dataset in A2:C7 that contains six rows, including one exact repeated row, enter =UNIQUE(A2:C7) in an empty cell. The formula spills a list of five distinct rows, leaving the original range untouched. Make sure the spill area is empty, or Excel may show a spill error.
UNIQUE compares complete rows when given a multi-column range. If you instead provide a single column, it returns the distinct values from that column. The formula result updates when the source data changes, which is useful for a live summary, but it does not delete or repair records in the source. UNIQUE is available in Microsoft 365 and Excel 2021 or later; older perpetual versions may not support it.
For repeatable cleanup of larger datasets, Power Query is another option. Select a cell in the data and choose Data > From Table/Range to open the Power Query Editor. Select the column or columns that define duplicates, then use Home > Remove Rows > Remove Duplicates. Load the result back to Excel when it is ready. A query can be refreshed when the source changes, but review which columns are selected: removing duplicates in Power Query also depends on the fields used to identify a match.
Use the ribbon command for a quick, one-time cleanup when you are comfortable changing the data. Prefer UNIQUE when you need a separate live list, and consider Power Query when you need a repeatable import-and-clean process. Neither alternative removes the need to define what a duplicate means for the task.
Frequently Asked Questions
Does Remove Duplicates delete the whole row?
Yes. Excel removes later rows that match on the columns you selected; it does not erase only the repeated cell. Include all fields belonging to a record in the selected range so the remaining rows stay together.
Can I remove duplicates based on one column only?
Yes. In the dialog, clear the other column selections and keep the one you want Excel to compare. Excel still removes the entire later row when that field matches, even if other columns contain different information.
How do I keep the newest duplicate record?
Sort the full table or range by its date field with the newest record first, then remove duplicates using the field or fields that identify a record. Excel keeps the first match it encounters, so sorting determines which version remains.
Can I undo Remove Duplicates?
Use Ctrl+Z on Windows or Command+Z on Mac immediately after the operation. If you have made later changes or closed the workbook, undo may not be available, which is why a copy is the safer precaution.
Does Remove Duplicates change the original data when I use UNIQUE?
No. UNIQUE returns a separate formula result and leaves the source range in place. The Data tab’s Remove Duplicates command, by contrast, changes the selected data directly.
Related
- Data Validation in Excel: Lists, Rules, and Fixes
- Excel Data Validation: Lists, Rules, and Setup
- Remove Data Validation Restrictions in Excel
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Add a Cover Page in Microsoft Word 365To add a cover page in Word 365 for Windows, open the document and choose Insert > Pages…
- Protect a Sheet in Excel: Steps, Options, and LimitsIn Excel for Microsoft 365 on Windows, protect a sheet by opening the worksheet, choosing Review > Protect…
- Excel Shortcut to Repeat the Last Command or ActionIn Excel for Microsoft 365 on Windows, press F4 or Ctrl+Y to repeat the last action when that…
- Excel Auto Insert Row When Data Changes?No: Excel does not automatically insert a new worksheet row every time data changes. For a list that…
- Schedule an Email in Outlook on PC: A Step-by-Step GuideTo schedule an email in Outlook on a Windows PC, compose your message, choose a future send time,…
- How to Create Inbox Rules in Outlook on Windows and WebTo create an inbox rule in Outlook, open the rules settings, choose a condition such as who sent…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.