Excel’s Remove Duplicates command keeps the first matching record in the current order and removes later records that match it in the columns you choose. So, if two rows match, the earlier one stays; sorting the data first can change which record counts as first.
The short answer: Excel keeps the first match
Remove Duplicates works from top to bottom through the selected list. When Excel encounters a row whose values match a row it has already seen in the comparison columns, it removes the later row. It does not choose the newest, most complete, or otherwise “best” record automatically. The order of the rows when you run the command determines which occurrence survives.
For example, if the same customer ID appears in rows 4 and 9, and Customer ID is one of the selected comparison columns, row 4 is retained and row 9 is removed, assuming there was no earlier match above row 4. The values in row 4 are not merged with row 9. Any information found only in the removed row is lost from the cleaned list.
What Excel considers a duplicate
A duplicate is determined by the columns checked in the Remove Duplicates dialog, not necessarily by every value in the worksheet. Excel compares the combination of values in those columns for each record. If all checked values match a previous record, the later record is removed. Values in unchecked columns do not affect the comparison.
Suppose a list contains Customer, City, and Email. If you check only Customer, two rows with the same customer name are considered duplicates even if the cities or email addresses differ. If you check Customer and Email, Excel removes a later row only when both the customer and email match an earlier row. The combination is what matters.
Cell formatting does not make otherwise matching entries distinct. Different colors, fonts, or number formats are not a reliable way to distinguish records for this task. On the other hand, extra spaces or inconsistent data entry can make entries that look alike behave differently. For example, a trailing space in a name may prevent it from matching the clean version. Mixed data types and nonprinting characters are also worth checking when results seem surprising.
How to remove duplicates in Microsoft 365
On Excel for Microsoft 365 on Windows, use the following process. Working on a copy is prudent if the list contains valuable or difficult-to-recreate data.
- Select a cell in the list, or select the full data range you want Excel to clean. Include all columns that belong to each record so information in different fields stays together.
- Choose Data > Remove Duplicates on the ribbon.
- In the dialog, confirm whether My data has headers is checked. If the first row contains field names, it should be treated as a header, not as a record to compare.
- Choose the columns that define a duplicate. Check only the fields that should establish whether two records represent the same item.
- Select OK. Excel reports how many duplicate values or rows it removed and how many unique records remain. Review the result before continuing with other edits.
If the data is formatted as an Excel table, select a cell in the table and use the same Data > Remove Duplicates command. The table’s rows are the records being cleaned. In a normal range, selecting the complete dataset is important; selecting only part of a record can leave related information outside the cleaned range. If the layout is unusual, save a copy and inspect the selection before confirming.
Excel for Mac also provides Remove Duplicates from the Data tab, though the ribbon layout can differ slightly by release. Excel for the web has a Remove Duplicates command on its Data tab as well; some surrounding controls and dialogs may look different from the Windows desktop version. There is no need to use a keyboard shortcut to open the command. To undo it immediately, use Ctrl+Z on Windows or Command+Z on Mac.
Why sorting changes which record survives
Because Excel retains the first occurrence, sorting is a way to control which of several matching records is kept. If you sort a date column from newest to oldest before removing duplicates, the newest row for each matching key will appear first and is therefore the one retained. If you sort oldest to newest, the oldest matching row will be retained instead.
Consider these entries for the same product code: a row dated March 2 with a price of 12.50, and a row dated March 8 with a price of 13.00. If Product Code is the only checked column, Excel treats the two rows as duplicates. Sort the date newest first and the March 8 row stays; sort oldest first and the March 2 row stays. Excel does not compare the dates to decide which price is preferable.
Before sorting, decide what “keep” means for the task. You might want the latest update, the earliest registration, or the record with a verified status. Sort using that rule, and consider adding a secondary sort field to settle ties consistently. If the source order itself is meaningful, do not sort casually: the original first occurrence may be the one you intend to preserve.
A worked example: choosing comparison columns
Imagine a contact list with columns for Name, Email, and Department. It contains these records in order: Ana, ana@example.com, Sales; Ben, ben@example.com, Support; Ana, ana@example.com, Marketing; and Ana, ana.alt@example.com, Sales.
- If you check Name only, the first Ana stays. Both later Ana rows are removed, even though one has a different email address.
- If you check Name and Email, the first two Ana records with ana@example.com count as duplicates, so the later one is removed. The record with ana.alt@example.com stays because the email differs.
- If you check all three columns, the two records with ana@example.com are not duplicates because their departments differ. All three Ana rows remain unless another row matches all three checked values.
This is why selecting every column is not always the right choice. It finds repeated full-row combinations, but it will not remove records that share a unique ID while differing in a note or status field. Conversely, choosing just a name can discard distinct people who happen to share that name. The correct columns depend on the meaning of a duplicate in your dataset.
Common problems and how to avoid them
The most consequential mistake is choosing the wrong comparison columns. A field such as first name is rarely a safe unique identifier by itself. A customer number, order ID, or a carefully chosen combination of fields is usually more meaningful. Check a few known records before applying the command to a large list.
Another frequent issue is an incorrect header setting. If Excel treats the header row as data, it can compare or remove it like any other row. If it assumes a header where there is none, the first real record can be excluded from the comparison. Confirm the checkbox in the dialog rather than relying on an assumption.
Hidden or filtered data deserves attention too. Do not assume that the visible rows alone define the scope of a cleanup. Clear filters or review the complete range before running a destructive operation, and check the resulting records afterward. Also look for blank rows, stray values beyond the apparent edge of the list, inconsistent spaces, and repeated identifiers with different details.
Remove Duplicates changes the data rather than merely highlighting likely matches. Although Ctrl+Z or Command+Z can undo the operation immediately, undo history may no longer be available after other actions or after closing the workbook. Save a backup copy first when the data matters, and use a separate worksheet for an exploratory cleanup.
When to use a formula or another approach
Use Remove Duplicates when you want to delete later matching records from a list and keep one occurrence. If you want to identify repeats without deleting anything, a helper formula gives you a reviewable flag. With IDs in A2:A7, enter this in B2 and fill it down:
=COUNTIF($A$2:A2,A2)>1
The formula returns FALSE for the first occurrence of an ID and TRUE for each later occurrence. For IDs A001, A002, A001, A003, A002, and A004 in that order, the results are FALSE, FALSE, TRUE, FALSE, TRUE, FALSE. You can filter the TRUE rows and inspect them before deciding what to do. This example checks one column; comparing several fields requires a formula designed for those fields.
For a separate, non-destructive list of distinct records, Microsoft 365 and Excel 2021 or later support the UNIQUE function. With complete records in A2:C7, enter =UNIQUE(A2:C7) in an empty area. Excel spills the distinct rows into the cells below and beside the formula, leaving the source range unchanged. This is useful for reports, but it does not delete or repair the original data. Check that the output area is empty so the results have room to spill.
Conditional formatting can also highlight repeated values for visual review, but highlighting is not the same as removing rows. The best choice depends on whether you need a permanent cleanup, an audit trail, or a refreshed distinct output as the source changes.
Frequently Asked Questions
Does Excel keep the first or last duplicate?
It keeps the first matching record in the current order and removes later matches. Sort the list first if you want a particular date or status to appear first.
Does Remove Duplicates compare every column?
Only the columns selected in the dialog determine whether records match. Checking every column finds repeated combinations across the full record; checking fewer columns can remove rows that differ in unchecked fields.
Can I remove duplicates but keep the original data?
Yes. Copy the range to another worksheet or workbook and run Remove Duplicates on the copy. Alternatively, use a helper formula or UNIQUE to identify distinct records without deleting the source list.
Can I undo Remove Duplicates?
Usually, you can undo it immediately with Ctrl+Z on Windows or Command+Z on Mac. Because later actions can affect undo history, make a copy before cleaning important data rather than relying on undo as your backup.
Why did Excel keep a row I did not expect?
Excel keeps the first match in the order it processed, based on the checked columns. Review the sort order, header setting, selected range, and comparison columns. Spaces, inconsistent values, or records that differ in a checked field can also explain the result.
Related
- Excel Protect Sheet: How to Change a Password
- Excel Protect Sheet: How to Edit Protected Cells Easily
- Excel Protect Sheet but Allow Grouping: What Works
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 Make Every Other Line a Color in Excel QuicklyTo make every other line (row) a color in Excel, select your data rows, then choose Home >…
- ISNUMBER in Excel: Check Whether a Cell Holds a NumberTo check whether a cell contains a number, enter =ISNUMBER(A2) in another cell, replacing A2 with the cell…
- Excel Convert German Number Format to English NumbersYes. To convert German-formatted text such as 1.234,56 into a real Excel number, put the text in A2…
- PV in Excel: How to Calculate Present Value and PaymentsIn Excel, calculate present value with the PV function: provide the interest rate per period, the number of…
- Styles in Word: What They Do and How to Use ThemYes. Microsoft Word includes styles: named sets of formatting you can apply to headings, paragraphs, lists, tables, and…
- Excel Page Break Lines: How to Hide Them in Normal ViewTo hide page break lines in Excel for Microsoft 365 on Windows, go to File > Options >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.