Remove Duplicates in Excel and Keep the Latest Record

Define what newest means before deleting rows

These steps use the worksheet Remove Duplicates command in desktop Excel 2016 and later. They do not require dynamic array functions. Work on a copy named Working and keep an untouched Original sheet, because this operation changes the selected dataset.

The sample rule is one row per customer_id, greatest updated_date first, and greatest revision when dates tie. Revision is an authoritative numeric version number in this invented dataset. It is not the current row number or a number added after sorting. If your data lacks an approved tie rule, keep tied records for review rather than inventing which one is newer.

Prepare real dates and preserve incomplete records

Enter the headers in A1:D1 and seven records in A2:D8. For a repeatable manual sample, use the DATE formulas below in column B instead of relying on Excel to interpret pasted date text. Format those cells as yyyy-mm-dd. Revision values in C2:C8 should be numbers.

Copy rows with a missing ID or missing date to an Exceptions sheet with their headers. For this sample, those are the last two records. Remove those two records from Working only after checking the copies; do not delete them from Original. The eligible working range is now A1:D6. In real data, also separate unparseable dates, error cells, and unreviewed whitespace-only IDs.

B2: =DATE(2026,9,1)
B3: =DATE(2026,9,8)
B4: =DATE(2026,9,8)
B5: =DATE(2026,9,8)
B6: =DATE(2026,9,2)
B7: =DATE(2026,9,9)
B8: leave empty

=ISNUMBER(B2)
Expected for each eligible date cell: TRUE

Sort the whole record using three levels

Select A1:D6, including all columns of the eligible records. Open Data > Sort and confirm that the selection has headers. Sorting only column B would detach dates from customers and owners; choose Expand the selection if Excel asks.

Use the three levels below in order. After sorting, the first two C102 rows should be Dan/revision 304 followed by Cam/revision 303. C101 should begin with Bea/revision 302. Check the associated owner values as well as the dates: keeping a date while mixing up the rest of the row would produce a false current record.

1. Sort by customer_id, Cell Values, A to Z.
2. Choose Add Level; then by updated_date, Cell Values, Newest to Oldest.
3. Choose Add Level; then by revision, Cell Values, Largest to Smallest.
4. Apply the sort and inspect the C101 and C102 groups before removing anything.

Remove duplicates using the identity columns

With the full working range selected, choose Data > Remove Duplicates. Confirm headers, clear the column checkboxes, and select customer_id only. Date, revision, and owner remain part of each record, but they are not part of the duplicate key. Apply the command.

Excel retains the first occurrence of each selected key in the current worksheet order. The sorted C101 winner is Bea; the tied-date C102 winner is Dan because revision 304 outranks 303. Selecting every column would leave both versions because their complete rows differ.

customer_id  updated_date  revision  owner
C101         2026-09-08    302       Bea
C102         2026-09-08    304       Dan
C103         2026-09-02    305       Eli

Eligible before: 5
Retained: 3
Removed: 2
Exceptions retained separately: 2

Verify the retained rows against the original

Confirm three retained records and two exceptions. Check that each eligible customer appears once, and compare every retained row with Original. For this tiny sample, inspect all three winners; for a larger file, reconcile unique-key counts and check every group that had tied dates or suspicious values.

Use the formula below beside each retained row as a simple uniqueness check, adapting the range to the final last row. It should return 1 for each sample ID. This alone does not prove you chose the correct version: also confirm that no original eligible row has a later date or a higher approved revision at the same date.

=SUMPRODUCT(--($A$2:$A$4=A2))
Fill down through row 4.
Expected: 1, 1, 1.

Keep the matching and refresh rules explicit

Choose all fields that together define an entity. If a customer has separate contracts by region, customer_id alone may discard a valid regional record. Use both identity columns in Remove Duplicates. Conversely, selecting the date as an identity field prevents old versions with different dates from being removed.

Use consistently stored and formatted IDs, and do not rely on this workflow for case-sensitive identifiers without a separately validated comparison. Dates with times need a common time basis before sorting. A date-only field cannot tell you which of two same-day edits happened last.

New exports require repeating preparation, sorting, removal, and checks. Do not transplant the worksheet sequence unchanged into Power Query: Microsoft documents that Table.Distinct does not generally guarantee which duplicate survives. A repeatable query needs an explicit selection design and its own verification.

Related help: Fix dates that sort as text · Choose the correct identity before combining records