To remove duplicate records in Excel while keeping the oldest date for each item, sort the entire table by the item’s identifying column and then by date from oldest to newest; next choose Data > Remove Duplicates and select only the identifying column. Excel keeps the first row in each duplicate group, so sorting first puts the oldest record in the position it will retain.
Why sorting matters before removing duplicates
Excel’s Remove Duplicates command does not look for the oldest date or decide which row is best. It compares values in the columns you select, keeps the first matching row it encounters, and removes later matches. The order of the rows therefore determines which record survives.
For example, suppose a customer ID appears several times with different dates and order details. If you remove duplicates using only the customer ID, Excel retains whichever row currently appears first, even if that row has the newest date. Sorting by customer ID and then date ascending changes that: each customer’s records are grouped together, and the earliest date comes first.
“Oldest” here means the earliest date in time. In Excel’s sort options, that is usually described as Oldest to Newest, not Newest to Oldest. Sorting dates in the wrong direction will keep the latest record instead.
Remove duplicates and keep the oldest date
These steps apply to Excel for Microsoft 365 on Windows. Work on a copy if the original data matters, because removing duplicates deletes rows from the selected range.
- Select a cell in the data. If Excel does not recognize the full range as a table, select the entire range, including every column that belongs to each record.
- Go to Data > Sort. In the Sort dialog, select the identifying field, such as Customer ID, as the first sort level. Choose a consistent order, such as A to Z.
- Choose Add Level. Select the date column as the next level and set its order to Oldest to Newest. If records are identified by more than one field, add all those fields before the date level.
- Choose OK and check that each group is together and its earliest date is at the top.
- Go to Data > Remove Duplicates. If the first row contains column names, make sure My data has headers is selected.
- In the column list, select only the field or fields that define a duplicate. Choose OK and review Excel’s message reporting how many duplicate values and unique values were found.
For example, if Customer ID is the key, select Customer ID alone in the Remove Duplicates dialog. Do not select the date or order description just because you want to keep those columns. Selecting extra columns changes the comparison: two rows are considered duplicates only when their values match across every selected column.
Choose the right columns to define a duplicate
The most important decision is what “duplicate” means in your data. A product code may identify a product, while a combination of customer ID and invoice number may identify a transaction. If the key is incomplete, Excel may collapse records that should remain separate; if it includes too many columns, records that represent the same entity may not match.
- One field: Use a single column when each value should appear only once, such as an employee ID in a current-employee list.
- Several fields: Select all fields needed to make a record unique, such as Account ID and Contact Email. Sort by those same fields first, followed by the date.
- Not the descriptive columns: Names, notes, prices, and status fields often differ between repeated records. Include them in the key only if those differences mean the rows are genuinely different records.
Suppose an inventory list has SKU, warehouse, date checked, and quantity. If a SKU can appear once per warehouse, use SKU and warehouse as the key, then sort by date checked. Using SKU alone would keep only one warehouse’s record. Using every column would often fail to remove anything because different dates or quantities make the rows distinct.
Example: keeping one earliest record per ID
Imagine this small table, with columns Customer ID, Order Date, and Note:
- C104 — September 12, 2023 — Renewal
- P220 — June 15, 2022 — First order
- C104 — April 3, 2021 — Original signup
- P220 — October 8, 2024 — Follow-up
Sort first by Customer ID and then by Order Date, oldest to newest. The C104 row dated April 3, 2021 and the P220 row dated June 15, 2022 will each be first in their group. Then remove duplicates based on Customer ID only. The result contains one row for each ID, with those earliest dates and the corresponding notes. Excel keeps the whole surviving row; it does not combine the oldest date from one row with other fields from a different row.
Use a helper formula when you want to review rows first
A helper column can mark the earliest row for each ID without immediately deleting anything. This is useful when you want to filter and inspect the retained records before making a permanent change. The example assumes IDs are in A2:A100 and real Excel dates are in B2:B100, with a helper column in D. Enter this in D2 and fill it down through D100:
=AND(B2=MINIFS($B$2:$B$100,$A$2:$A$100,A2),COUNTIFS($A$2:A2,A2,$B$2:B2,B2)=1)
MINIFS finds the earliest date for the ID in the current row. COUNTIFS checks whether that same ID-and-date combination has appeared earlier in the list. The formula returns TRUE for the first row with the earliest date and FALSE for the other rows. Filter column D to TRUE to view the rows to keep. For example, if C104 has dates April 3, 2021 and September 12, 2023, the formula marks the April 3 row TRUE and the later row FALSE.
If two rows for the same ID share the earliest date, the formula keeps the first of those rows in the current row order and marks the later tied row FALSE. If you need a specific one to win, add a tie-breaker—such as a timestamp or sequence number—and sort by it, or adjust your process to define that choice explicitly. This formula assumes IDs are populated and dates are valid Excel dates; clean blank IDs and missing or text-stored dates before relying on the result. MINIFS is available in Excel for Microsoft 365 and recent Excel versions, including Excel 2019 and later.
Check date values and sorting details
A date that looks correct on screen may be stored as text. Text dates can sort alphabetically rather than chronologically, and date-based formulas may not treat them as actual dates. If the sort looks wrong, check a few cells by changing their number format to a date or General, and confirm the values behave like dates. Convert text dates to real dates before sorting; changing the display format alone does not convert text into a date value.
Also check for times attached to dates. Excel stores date-times as numbers with a fractional time component. A record at 9:00 a.m. sorts before one at 3:00 p.m. on the same day. That is appropriate if the earliest timestamp should win. If the requirement is to treat all entries on a calendar day as tied, decide how you want to handle that before removing records.
For a multi-column key, sort by every key field first and the date after those fields. For instance, sort by Account ID, then Region, then Date. The same combination of Account ID and Region will then form a group, and the earliest date in that group will come first.
Protect the original data and verify the result
Remove Duplicates changes the selected data rather than producing a separate result. Save a copy of the workbook or duplicate the worksheet before proceeding, especially when other people depend on the source list. You can also add a helper column first, filter to the proposed rows, and copy those rows to a new sheet.
Before confirming removal, check that the full record range is included and that the dialog’s selected columns match your intended key. Afterward, verify a few IDs that had several records: each should appear once, and its retained date should be the earliest date that was present for that key. Excel’s duplicate-removal result message is useful, but it does not confirm that the right columns or sort order were chosen.
If you remove the wrong rows, Ctrl+Z usually undoes the change immediately on Windows. On Mac, use Command+Z. Undo may no longer be available after closing the workbook or performing other actions, so a backup is the safer protection.
When Power Query is a better alternative
For a list that you import and refresh regularly, Power Query can make the process repeatable. In Excel for Microsoft 365 on Windows, select a cell in the source and choose Data > From Table/Range. In Power Query Editor, group the data by the identifying field or fields and calculate the minimum date for each group. To retain the other columns from the original earliest-date row, merge that grouped result back with the source using both the key fields and the date, then expand the matching original columns.
A merge on the key and minimum date can return more than one row when several records tie for the earliest date. Decide whether to keep all ties or choose a tie-breaker before using the result. Power Query is useful for repeatable imports, but it requires more setup than sorting and removing duplicates once. Menu wording and availability can vary by platform; Excel for the web and Mac may not expose every Power Query capability available in Windows desktop Excel.
Frequently Asked Questions
Does Remove Duplicates automatically keep the oldest date?
No. It keeps the first matching row in the current order. Sort by the key and then by date from oldest to newest before using the command.
Should I select the date column in Remove Duplicates?
Usually not if you want one row per ID regardless of date. Select the ID field, or all fields that together define the key. Including the date makes rows with different dates distinct, so Excel may keep several rows for one ID.
Can Excel keep the oldest date and its entire row?
Yes. When you sort the complete dataset and remove duplicates using the key columns, Excel keeps the first full row in each group, including the date and the details from that same record.
What if two records have the same oldest date?
The sort may leave both tied records adjacent, and Remove Duplicates will keep the first one encountered when the key columns are selected. Add a secondary sort field if one tied record should take priority, or use a helper formula that marks the first tied row.
Why did my dates sort in the wrong order?
The cells may contain text rather than real Excel dates, or they may include times that affect the order. Convert text dates to date values and decide whether timestamps should count when selecting the oldest record.
Related
- Excel Printer Not Responding? Troubleshoot the Problem
- Excel Problem Solver: How to Set Up Solver
- Excel Print Area Locked? Causes, Fixes, and Workarounds Explained
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Group Rows in Excel by the Same NameTo group rows that share a name, first sort the entire data range by the Name column so…
- Excel Find and Replace: What It Returns and How It WorksExcel’s Find and Replace command does not return a value to a worksheet cell: Find locates matching content,…
- Excel Redo Shortcut: How to Reapply an ActionIn Excel for Microsoft 365 on Windows, press Ctrl+Y to redo the last action you undid. On a…
- How to Paste in Excel While Skipping RowsTo place a list into Excel with blank rows between entries, put a formula in the first destination…
- Does Excel Have a Profit and Loss Template? How to Use ItYes. Microsoft Excel for Microsoft 365 includes a template gallery where you can search for profit and loss…
- Data Validation Shortcut Key in Excel: Windows and MacIn Excel for Microsoft 365 on Windows, press Alt, then A, then V, then V to open the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.