Keep Only the Latest Record for Each ID in Excel (Without Deleting History)

A recurring export can contain several rows for the same customer, invoice, employee, or SKU. If you use Remove Duplicates, Excel keeps the first row it finds—not necessarily the newest one.

To keep the latest record for each ID without deleting history, flag the newest row with a helper formula and filter it. For recurring exports, use Power Query to create a refreshable current-record table.

Quick Fix: Flag the newest row, then filter it

Four-panel office cartoon showing an employee accidentally using Remove Duplicates, discovering it kept an old record, adding a Keep formula based on the latest timestamp, and filtering TRUE to show the current records while preserving history. — Keep the newest record without deleting the history behind it.

Convert your export to an Excel Table named History. Add a column named Keep, then enter this formula in its first data cell:

=AND([@ID]<>"",ISNUMBER([@Updated]),[@Updated]=MAXIFS(History[Updated],History[ID],[@ID]))

Filter Keep to TRUE. You will see the newest valid date or timestamp for each nonblank ID, while every historical row remains in the source table.

Best when: one date or timestamp defines the current record.
Blank IDs: the formula excludes them from the current-record view.
Ties: every row with the same newest timestamp is marked TRUE. Treat that as a review signal, not a random choice.

Why Remove Duplicates can keep the wrong row

Data > Remove Duplicates retains the first occurrence it encounters in the current row order. It does not compare dates or timestamps to decide which record is current.

You can sort newest to oldest before removing duplicates, but the removed history is then gone unless you work on a copy. A helper column or Power Query gives you a visible, repeatable retention rule instead. For a broader overview, see removing duplicates, finding unique values, and using Power Query.

Example: Keep the current customer status

Suppose your History table contains this export. The goal is one current status per customer while keeping all previous updates.

ID	Updated	Version	ImportSequence	Status	Account value
C-104	01/02/2025 09:15	1	101	Prospect	2,000
C-104	18/02/2025 14:30	2	118	Active	2,000
C-221	05/02/2025 11:00	1	109	Active	6,500
C-221	26/02/2025 08:45	2	126	On hold	6,500
C-318	12/02/2025 16:10	1	121	Prospect	1,200

1. Add the Keep column

Add Keep as the next Table header and use:

=AND([@ID]<>"",ISNUMBER([@Updated]),[@Updated]=MAXIFS(History[Updated],History[ID],[@ID]))

For C-104, the formula finds 18/02/2025 14:30 as the largest valid timestamp for that ID. Only the Active row is marked TRUE.

2. Filter to the current view

Select the filter arrow in the Keep header.
Clear FALSE.
Select OK.

The visible result is C-104 Active, C-221 On hold, and C-318 Prospect. Clear the filter whenever you need the complete audit history.

3. Create a separate latest-record output in Microsoft 365

For a live report area instead of a filtered table, enter this formula in an empty cell outside History:

=FILTER(History,History[Keep]=TRUE,"No current records")

The formula spills the rows marked TRUE into a separate list. Keep the spill area clear or Excel returns #SPILL!.

Check the data before trusting the result

Excel stores real dates and times as numbers. An imported date that looks correct but is stored as text will not pass the ISNUMBER test and should be fixed before reporting.

Use a timestamp, not only a date, if more than one update can happen in a day.
Check that Updated contains real date/time values, not text.
Keep IDs consistent. For example, C-104 and C-104 are different values.
Decide whether blank IDs are errors, then correct or exclude them before publishing a report.

When IDs are inconsistent, compare the cleaned list with the original before reporting. This guide on how to compare two Excel lists can help identify exceptions.

Handle ties with a documented rule

If two rows share the latest timestamp, the first helper formula marks both TRUE. That is intentional: the timestamp alone does not identify one current row.

Use a business-owned tie-breaker, such as:

highest version number;
largest import sequence number;
a timestamp with greater precision; or
manual review for conflicting updates.

Latest timestamp, then highest version

Add a KeepFinal column. If a higher numeric version wins when timestamps match, use:

=AND([@Keep],ISNUMBER([@Version]),[@Version]=MAXIFS(History[Version],History[ID],[@ID],History[Updated],[@Updated]))

Filter KeepFinal to TRUE. If two rows have the same ID, timestamp, and version, both remain visible. Add another documented tie-breaker rather than silently selecting one.

Method 2: Use Power Query for recurring exports

Power Query is the recommended option for weekly or monthly exports. It leaves the source history available, applies the same rule on refresh, and loads a separate current-record result.

Start with Data > From Table/Range. If you are new to Power Query, review this Excel Power Query basics guide.

Group each ID, sort inside the group, and keep the leading candidate

Do not rely on a sort performed before grouping. Instead, sort the rows inside each ID group. The query below uses Updated, then Version, then ImportSequence as the retention rule. It returns all rows tied on all three fields so unresolved ties remain visible.

In Power Query, open Home > Advanced Editor and adapt the column names if needed:

let
    Source = Excel.CurrentWorkbook(){[Name="History"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,
        {{"ID", type text}, {"Updated", type datetime}, {"Version", Int64.Type}, {"ImportSequence", Int64.Type}}),
    ValidIDs = Table.SelectRows(ChangedType, each [ID] <> null and Text.Trim([ID]) <> ""),
    Grouped = Table.Group(ValidIDs, {"ID"},
        {{"Latest", each
            let
                Sorted = Table.Sort(_, {{"Updated", Order.Descending}, {"Version", Order.Descending}, {"ImportSequence", Order.Descending}}),
                Top = Sorted{0},
                Candidates = Table.SelectRows(Sorted, each [Updated] = Top[Updated] and [Version] = Top[Version] and [ImportSequence] = Top[ImportSequence])
            in
                Candidates,
            type table}}),
    Expanded = Table.ExpandTableColumn(Grouped, "Latest",
        {"Updated", "Version", "ImportSequence", "Status", "Account value"})
in
    Expanded

The nested Table.Sort makes the selection rule explicit for every ID group. If Updated, Version, and ImportSequence are all tied, the query returns each tied row for review rather than choosing based on an unstated row order.

Load the result to a new worksheet table. After updating the source export, use Data > Refresh All. For the Power Query Group By command and its aggregation options, see Microsoft’s Power Query Group By reference.

Which method should you use?

Situation	Best method	Why
One-off review in an existing workbook	Helper column + filter	Fast, visible, and preserves all rows.
Microsoft 365 report area	Helper column + FILTER	Creates a separate live list.
Weekly or monthly export	Power Query	Refreshable and easier to standardize.
Source order is reliably meaningful	XLOOKUP reverse search	Returns the final matching row, but does not test dates.

If source order is reliably oldest to newest and you only need fields for one known ID, XLOOKUP can search last-to-first with search mode -1. See Microsoft’s XLOOKUP function documentation and this complete XLOOKUP guide. Do not use it as a replacement for a date-based rule when rows can be reordered.

Use AI to document the rule, not to choose the row

“Latest” may mean latest update, highest version, latest paid invoice, or most recent import. Use AI to turn a vague request into a testable rule, then apply that rule in Excel.

We receive a table with ID, Updated, Version, Status, and ImportSequence.
Write a retention rule for one current row per ID. Ask questions about ties,
blank timestamps, cancelled records, and late-arriving corrections. Do not
select records; return a rule I can implement and audit in Excel.

Treat the response as a draft specification. The Excel formula or Power Query query remains the auditable mechanism that selects the rows.

Troubleshooting

Issue	Likely cause	Solution
Two rows show TRUE	They share the latest timestamp.	Add a documented tie-breaker or review both rows.
No row is kept for an ID	The ID is blank, or Updated is text rather than a real date/time.	Correct the ID or convert the timestamp before reporting.
An old row is marked TRUE	IDs do not match exactly, or dates were imported incorrectly.	Clean IDs and check date/time values.
#SPILL! in the output list	Cells are blocking the FILTER result.	Clear the spill range or move the formula.
Power Query returns multiple rows for one ID	All stated tie-break fields are identical.	Add a unique business tie-breaker or send the records for review.

Final checklist

Keep the export as a source-history table.
Define which date or timestamp means “latest.”
Exclude or correct blank IDs and invalid timestamps.
Use a helper formula for an existing worksheet, or Power Query for recurring exports.
Document a tie-breaker and expose any unresolved ties.
Refresh or recalculate before using the current-record list in a report.

FAQ

Does this delete duplicate records?

No. The helper formula labels records, and filtering only changes the view. Power Query can load a separate latest-record output while the source table remains available.

Can I keep the oldest record instead?

Yes. Replace MAXIFS with MINIFS in the helper formula, then adjust your tie-break rule if needed.

Should I sort and use Remove Duplicates?

Only on a copy when you genuinely do not need the removed rows. For an audit trail, use a helper column, a dynamic output, or Power Query.

What is the safest tie-breaker?

Use a field owned by the business process, such as a version or import sequence. If no reliable field exists, keep tied records visible for review.