Excel Remove Duplicates and Keep the Newest Date

To remove duplicate records while keeping the newest date in Excel, sort the entire table by its date column from newest to oldest, then choose Data > Remove Duplicates and select only the columns that define a duplicate. Excel keeps the first matching row it encounters, so sorting first makes the newest record the one that remains.

Why sorting first matters

Excel’s Remove Duplicates command does not compare dates to decide which record is newest. It scans the selected range from top to bottom, identifies rows that match on the columns you specify, and retains the first occurrence of each match. Later matching rows are removed. The order of the rows before you run the command therefore determines which record survives.

For example, a customer may appear several times because each row records a different visit. If the rows are sorted newest to oldest, the most recent visit appears first. Removing duplicates based on Customer ID then preserves that visit and removes the older visits for the same customer.

The columns you select in the Remove Duplicates dialog are just as important as the sort. Select the identifier columns that define a duplicate, not automatically every column in the table. If you select every column, Excel removes only rows where every selected value matches; records with the same customer but different dates will remain.

Use Sort and Remove Duplicates

Make a backup. Save a copy of the workbook or duplicate the worksheet. Removing duplicates changes the data, and the command is not a good substitute for keeping an untouched source.
Select a cell in the table. If the data is a normal range, make sure the range includes all columns belonging to each record. If Excel asks whether to expand the selection when you sort, choose to expand it so that dates and the rest of each row stay together.
Sort by the date column. Go to Data > Sort. Select the date field as the sort column and choose Newest to Oldest. If you use the quick sort buttons, first confirm that Excel is sorting the whole table, not just the date column.
Remove duplicates using the key. Go to Data > Remove Duplicates. If the first row contains column names, leave My data has headers selected. Clear the checkboxes for columns that should not define a duplicate, and leave selected the identifier field or combination of fields that identifies the record.
Review the result. Excel reports how many duplicate values were removed and how many unique values remain. Check several records against the original data, especially records with several dates or repeated dates.

In Excel for Microsoft 365 on Windows, these commands are on the Data tab. The Mac version also provides sorting and Remove Duplicates from the Data tab, although the ribbon layout can vary slightly by release. In Excel for the web, use the Data tab if the commands are available in your workbook; for a large or important cleanup, verify the result carefully or open the file in the desktop app.

Choose the right duplicate columns

A duplicate means whatever the selected columns say it means. If a company can have multiple contacts, selecting Company Name alone would collapse those contacts into one row. If a person can have multiple transactions, selecting Person alone would keep only one transaction. Choose fields that together identify the entity whose latest record you want.

One key: Select Order ID if each order should appear once, or Employee ID if you want one row per employee.
A combined key: Select both Account ID and Product if the goal is one latest record for each account-product pair.
All columns: Use this only when you want to remove exact duplicate rows and keep distinct records even when they share an ID.

For instance, if a table has Customer ID, Date, Status, and Notes, selecting Customer ID alone keeps one latest row for each customer. Selecting Customer ID and Status instead keeps one latest row for each customer-status combination. Including Date in the duplicate key usually defeats the purpose: different dates make otherwise similar rows distinct, so older records may not be removed.

Worked example

Suppose the table contains these records, with one row per visit:

C104, March 3, 2025, Follow-up
C219, February 15, 2025, New inquiry
C104, April 18, 2025, Renewal
C219, May 2, 2025, Follow-up

Sort the complete table by Date, newest to oldest. C219’s May 2 record moves ahead of its February 15 record, and C104’s April 18 record moves ahead of its March 3 record. In Remove Duplicates, select only Customer ID. The retained rows are C219 on May 2 and C104 on April 18. If you selected all four columns instead, all four rows would remain because their dates or other values differ.

Check that Excel recognizes your dates

Sorting works as intended only when Excel recognizes the values as dates. Real Excel dates are stored as numbers and displayed using a date format. Dates imported from a text file or copied from a website may look like dates but be stored as text, which can lead to an unexpected sort order or incorrect formula results.

Look for clues such as dates that do not respond to date formatting, values aligned differently from nearby dates, or a sort that places a later year between earlier months. You can test a cell by changing its number format to General: an actual date displays as a serial number. Do this as a check, then restore a suitable date format. If the values are text, convert them to real dates before sorting; the appropriate conversion depends on their original format and regional settings.

Also check for blanks and inconsistent identifiers. A blank date may sort to one end of the list, but it does not represent a known newest record. Decide whether rows with missing dates should be excluded or reviewed separately. IDs with accidental spaces or inconsistent spelling can also be treated as different keys, so clean those values before removing duplicates.

Handle records with the same newest date

If two rows for the same key share the newest date, sorting by date alone cannot express which row should win. Excel will keep whichever of those tied rows comes first in the sorted range. If the rows differ in status, amount, or another field, decide on a tie-break rule before removing anything. You could sort by Date newest to oldest and then by a second field, such as revision number or timestamp, in the preferred order.

If the tie-break field is not meaningful, do not treat one tied row as more current by assumption. Keep both for review, or use a rule agreed by the people responsible for the data. A date without a time cannot distinguish two updates made on the same day. If time matters, store a genuine date-and-time value or add a reliable sequence or revision field.

Formula option for a reviewable result

For a repeatable process, you can flag the newest row for each key before deleting or copying anything. Assume Customer ID is in A2:A100, Date is in B2:B100, and row 1 contains headers. Enter this formula in C2 and fill it down through C100:

=AND(B2=MAXIFS($B$2:$B$100,$A$2:$A$100,A2),COUNTIFS($A$2:A2,A2,$B$2:B2,B2)=1)

The formula returns TRUE for the first row it finds that has the maximum date for that customer, and FALSE for the other rows. MAXIFS finds the latest date for the ID in A2; COUNTIFS makes only the first occurrence of that ID-and-date pair TRUE. If two records tie for the latest date, this formula keeps the first tied record in the existing row order. Filter column C to TRUE and review or copy those rows to a separate results area. MAXIFS is available in Microsoft 365 and newer Excel versions; if a customer has no valid date, handle that case separately rather than assuming a blank date is a usable latest value.

This approach leaves the source rows in place until you choose what to do with them, which can make review safer than immediately deleting rows. It also assumes the date cells are real Excel dates and that the specified ranges cover all records. Adjust the row limits when your data grows, or use an Excel table so the data and formulas can expand together.

When to use Power Query instead

If you regularly receive a fresh export and need to repeat the cleanup, Power Query can make the steps refreshable. In Excel for Microsoft 365, select a cell in the data and choose Data > From Table/Range to open the Power Query Editor. A robust pattern is to group the records by the key and calculate the maximum date, then merge that result back with the original table to retrieve the rest of the fields.

Be aware that merging on key and maximum date can return more than one row when a key has multiple records on its newest date. Add a tie-break rule or review those ties if only one row is required. Power Query is useful for repeatable imports, while sorting and Remove Duplicates is usually quicker for a one-time cleanup. In either case, retain the original data until you have checked the output.

Frequently Asked Questions

Does Remove Duplicates keep the first or last row?
It keeps the first matching row in the current row order and removes later matches, based on the columns selected in the dialog. Sort newest to oldest first if you want the newest dated record to be first.

Should I include the date column in Remove Duplicates?
Usually not when the goal is one latest record per ID. Select the ID or other fields that define a duplicate. Including Date makes rows with different dates count as different records.

Can I keep the newest row without deleting the older records?
Yes. Use the helper formula above to flag the first row with each ID’s maximum date, then filter for TRUE and copy those records elsewhere. Keep the original range intact until you have checked the result.

What if two rows have the same latest date?
Date sorting alone cannot choose a meaningful winner between tied records. Add a secondary sort field if it provides a valid rule, or keep the tied rows for review. A date-only field cannot show which same-day update happened later.

Why did Excel keep an older date?
Check that you sorted the entire table, sorted in Newest to Oldest order, and selected the intended key columns in Remove Duplicates. Also confirm the dates are real Excel dates rather than text and that no blank or invalid dates affect the order.

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.