Excel Remove Duplicates: Keep the Last Matching Record

Excel’s Remove Duplicates command keeps the first matching row it encounters; it has no built-in option to keep the last one. To retain the last record, first put that record ahead of the others—by sorting on a timestamp or an original row number—then choose Data > Remove Duplicates and select the columns that define a duplicate.

What Remove Duplicates Actually Does

Remove Duplicates compares the columns you select in its dialog, then removes rows that match on those columns. It keeps the first match in the current row order. For example, if customer ID 104 appears in rows 2 and 5, and both rows are included in the comparison, Excel keeps whichever one appears higher after any sorting you performed.

The command changes the selected range or table in place. It does not create a separate list of survivors, and the rows it removes may contain other information that is not part of the duplicate check. This is why ordering the rows and choosing comparison columns carefully matter as much as clicking the command.

A duplicate is defined by your selected fields, not necessarily by every value in the row. If two records have the same customer ID but different dates or order amounts, selecting only Customer ID treats them as duplicates. Selecting Customer ID and Order Date instead keeps both if their dates differ.

Choose What “Last” Means

Before changing the sort order, decide what last means for your data. It could mean the record that appears lowest in the worksheet, the most recent timestamp, or the latest transaction according to a sequence number. These meanings can differ: a file may be sorted by name even though newer records were appended at the bottom.

If the intended rule is “keep the last row as the sheet currently stands,” add an original-position column before sorting. If it is “keep the most recent record,” use the date or time field instead. When two records share the same timestamp, use a second field, such as an import sequence number, to make the choice predictable.

Last row in the current order: record each row’s position, then sort that position from largest to smallest.
Newest record: sort the timestamp from newest to oldest.
Highest version or sequence: sort that number from largest to smallest.

Method 1: Sort, Then Remove Duplicates

This is the quickest method when there is a reliable date, sequence, or position field. In Excel for Microsoft 365 on Windows, sort the entire data range or table—not just one column—so values remain attached to their original records. Use the header row as headers if your data has one.

If you need to preserve the current bottom-most row, add a column such as OriginalOrder and number the data rows from top to bottom before sorting.
Select a cell in the data, then choose Data > Sort. In the sort dialog, select the timestamp, sequence, or OriginalOrder column and sort largest to smallest. For dates, this puts the latest date first.
Select a cell in the sorted data and choose Data > Remove Duplicates.
In the dialog, select only the column or combination of columns that defines a duplicate. Leave unrelated fields unchecked if differences in those fields should not make records distinct.
Choose OK and review Excel’s message showing how many duplicate values were removed and how many unique values remain.

For the original-last-row rule, sorting OriginalOrder from largest to smallest puts the former bottom row first within every duplicate group. Remove Duplicates therefore keeps that row. If you want the surviving records back in their original overall order, sort the remaining data by OriginalOrder from smallest to largest. Keep the index until you have checked the result.

For a newest-record rule, sort the timestamp newest to oldest instead. If timestamps tie and the later imported row should win, add OriginalOrder as a second sort level, descending. Sorting the full table is essential: sorting only the date column would detach dates from names, IDs, and other fields.

Method 2: Mark the Last Occurrence with a Formula

A helper column is useful when you want to inspect which rows will survive before deleting anything. It also avoids rearranging the data. Suppose IDs are in A2:A6, with repeated IDs in rows 2 and 5 and in rows 3 and 6. In B2, enter this formula and fill it down through B6:

With IDs in A2:A6, enter this in B2 and copy down: =COUNTIF($A2:$A$6,A2)=1

It returns FALSE for rows 2 and 3 because each ID appears again below, and TRUE for rows 4, 5, and 6 because those are the last occurrences of their respective IDs. Filter the helper column to TRUE to view the records to keep. You can copy those visible records to another range, or delete the FALSE rows after checking the results.

The expanding start of the range is intentional: as the formula is copied down, it checks from the current row through the last data row. A last occurrence has a count of one in that remaining section. Replace row 6 with your actual final data row, and adjust the ID column if your key is elsewhere.

Handling Duplicates Defined by Multiple Columns

Sometimes an ID alone is not enough to identify a duplicate. A customer might have several orders, so the correct key could be Customer ID plus Order Number. In Remove Duplicates, select both key columns. In a helper formula, count rows matching both fields from the current row downward.

With Customer ID in A2:A100 and Order Number in B2:B100, enter this in C2 and fill down: =COUNTIFS($A2:$A$100,A2,$B2:$B$100,B2)=1

The formula returns TRUE only on the last row where that exact pair appears. Filter for TRUE to retain one final record per customer-and-order combination. Add more criteria ranges and criteria pairs to COUNTIFS if the key has more fields. Make sure the ranges cover the same rows and that the chosen key reflects the real meaning of a duplicate.

Power Query and Repeatable Cleanup

For a recurring import, Power Query can make cleanup repeatable instead of requiring the same manual sort and deletion each time. A dependable pattern is to add an index to the source rows, identify the greatest index for each duplicate key, and use that index to retain the corresponding full record. The index represents source order, so the greatest value corresponds to the last source row.

In Excel for Windows, start with Data > From Table/Range to open the Power Query Editor. Add an index column using Add Column > Index Column. Then group by the key columns and calculate the maximum index for each group. To keep all the fields from each winning row, merge that grouped result with the indexed source using the key columns and index, and expand the matching source record. Load the result with Home > Close & Load.

This approach takes more setup than Remove Duplicates, but the steps can be refreshed when new source data arrives. Verify the merge keys and the maximum-index step on a small sample before relying on the output. Power Query’s result is a query output, not an instruction to delete records from the original worksheet.

Common Problems and Checks

The wrong version survives: check the sort direction and confirm you sorted the entire table. Descending order puts the largest sequence or latest date first.
Too many rows disappear: check which columns were selected in Remove Duplicates. Selecting only a broad field such as city can make unrelated people look like duplicates.
Expected duplicates remain: inspect the key values for extra spaces, inconsistent spellings, or different data types. For example, a number stored as text may not behave as expected alongside a numeric value. Clean or standardize the key before comparing.
Blank keys are present: decide whether blank-key rows should be treated as one group or retained separately. A helper formula can mark later blank cells as later occurrences, so filter or handle blank keys separately if that is not the intended rule.
The worksheet order changed: retain an original-position column before sorting. It lets you restore the order of the surviving rows afterward.

Before running an in-place removal, save a copy of the workbook or duplicate the worksheet. Ctrl+Z usually undoes an immediate Remove Duplicates action, but it is not a substitute for a backup—especially if you make additional changes afterward. Review both the surviving rows and Excel’s removal count before saving the cleaned version.

Which Method Should You Use?

Use sorting followed by Remove Duplicates for a one-time cleanup with a clear timestamp or sequence field. It is direct, and Excel reports the number of removed duplicates. Use a helper formula when you want to preview the specific rows that qualify, preserve the current worksheet order, or apply a rule based on multiple key fields. Use Power Query when the same source needs to be cleaned repeatedly and a refreshable process is worth the extra setup.

All three methods depend on defining the duplicate key correctly. A person can have many legitimate transactions, and two records with the same name may not represent the same entity. Confirm the key fields and the meaning of last before removing anything; a technically correct de-duplication can still discard valid data if the business rule is wrong.

Frequently Asked Questions

Does Excel have a Keep Last option in Remove Duplicates?
No. Remove Duplicates keeps the first matching row in the current order. Sort the desired last record to the top of each group first, or mark last occurrences with a helper formula.

How do I keep the newest date for each ID?
Sort the entire data range by date from newest to oldest, then choose Data > Remove Duplicates and select the ID column. If dates can tie, sort by a secondary sequence field so the preferred record is first.

Can I remove duplicates based on only one column?
Yes. In the Remove Duplicates dialog, select only that column. Excel removes the duplicate rows based on that value, even if other columns differ, so confirm that this is the rule you intend.

Will Remove Duplicates delete my whole row?
It removes duplicate rows from the selected range or table, not just the repeated key cell. Other columns in each removed row go with it, which is why making a backup is prudent.

Can I keep the last duplicate without sorting?
Yes. Add a helper column using COUNTIF for one key or COUNTIFS for multiple keys, with the count range starting at the current row and ending at the final data row. Filter for TRUE to identify last occurrences before deciding how to extract them.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.