Excel Filter Duplicates: Keep the First Record

Formulas & functions · Updated November 19, 2026

To keep the first occurrence of each value in Excel, select the full data range and choose Data > Data Tools > Remove Duplicates, then choose the columns that define a duplicate. Excel keeps the first matching row in the order currently shown and removes later matches. The ordinary Filter command only hides rows; it does not remove duplicates by itself.

What “keep the first” means in Excel

Excel considers a row a duplicate according to the columns you select in the Remove Duplicates dialog. If you select just an email-address column, rows with the same email are treated as duplicates even if their names or other details differ. If you select every column, only rows matching across all those selected fields are removed. Choosing the right columns is therefore as important as choosing the right command.

“First” means the first matching row in the range’s current order, not necessarily the oldest, newest, or most complete record. If the rows are in their original import order, Excel keeps whichever matching record appears earliest in that order. If you want to keep a specific record, sort the entire dataset into the desired order first—for example, by date from oldest to newest—then remove duplicates.

Also distinguish removing duplicates from filtering them. Remove Duplicates deletes later matching rows from the selected data. A filter can instead show only rows that meet a condition, while leaving the other rows in the workbook. Use a copy of the data or a helper-column method if you need to inspect or preserve the records that would be removed.

Remove duplicates and keep the first row

In Excel for Microsoft 365 on Windows, use these steps:

  1. Make a backup or copy of the worksheet if the original records may be needed later.
  2. Click inside the data or select the complete dataset, including all columns belonging to each record. If you select only one column, Excel may offer to expand the selection; choose the option that includes the full table when the other fields must stay attached to their rows.
  3. Go to Data > Data Tools > Remove Duplicates.
  4. If the first row contains field names, check My data has headers. Confirm the checkboxes for the columns Excel should use to identify duplicates.
  5. Choose OK. Excel reports how many duplicate values were removed and how many unique values remain.

For a list of customers where an email address is the unique identifier, select only Email in the dialog, even if the table also has Name, City, and Account Status columns. Excel keeps the first row for each email and removes later rows with that email, carrying the retained row’s other details with it. If instead you select Email, Name, City, and Account Status, two rows with the same email but different cities are not duplicates under that selection.

Excel tables are convenient for this task because selecting a cell in a table makes it easier to work with the complete set of fields and headers. On Excel for Mac, the command is also available from the Data tab, although the ribbon layout can differ slightly by version. Excel for the web has data-cleaning features too, but if a command or dialog is unavailable in your account, open the workbook in the desktop app and use the steps above.

Choose which record should count as first

Before removing anything, decide what “keep” should mean for your data. Suppose an order export contains repeated order IDs because details were imported more than once. If every copy is equivalent, the current order may be sufficient. But if repeated customer records have different update dates, keeping the first row could preserve an outdated address simply because it appears earlier.

To keep the most recent record, sort the entire dataset by the update-date column from newest to oldest before removing duplicates based on the customer ID. To keep the earliest, sort oldest to newest. Make sure the sort applies to all columns, not just the date column; otherwise, values from different records can become misaligned. If ties in the date matter, add a second sort field, such as a status or source priority, so the preferred row appears first.

Sorting changes row order, so consider adding an original row number if you need to restore the original sequence later. You can also copy the data to a separate worksheet and clean the copy. Removing duplicates is easy to undo immediately with Ctrl+Z on Windows or Command+Z on Mac, but undo history is not a reliable long-term backup after additional edits or closing the workbook.

Mark duplicates with a formula instead of deleting them

A helper column is useful when you want to see which occurrence is first, filter to the retained rows, or review later occurrences before deciding what to do. With entries in A2:A6, where the values are Oak, Pine, Oak, Maple, and Pine in that order, enter this in B2 and fill it down:

=IF(A2="","",IF(COUNTIF($A$2:A2,A2)=1,"Keep","Duplicate"))

The results are Keep, Keep, Duplicate, Keep, and Duplicate. The expanding range starts at A2 and ends at the current row, so each value is compared only with the rows above it and itself. The first appearance receives Keep; later appearances receive Duplicate. The outer IF leaves blank source cells blank rather than labelling every blank after the first as a duplicate.

Once the helper column is filled, turn on filters with Data > Filter and choose Keep in that column to display the first occurrences. This does not delete the other rows; clear the filter to see them again. If you need a permanent clean list, copy the visible records to another location, or use Remove Duplicates on a copy of the complete data.

Check duplicates using more than one column

Some records are duplicates only when a combination of fields matches. For example, a person may legitimately have two invoices, so a repeated customer name alone is not enough; perhaps Customer ID plus Invoice Number is the intended key. In Remove Duplicates, select both of those columns. Excel then identifies a duplicate when the selected values match across the selected columns.

You can also label first occurrences of a two-column key with a helper formula. With customer IDs in A2:A6 and invoice numbers in B2:B6, enter the following in C2 and fill down:

=IF(OR(A2="",B2=""),"",IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2)=1,"Keep","Duplicate"))

This leaves rows blank if either key field is blank; otherwise, the first appearance of each ID-and-invoice combination is Keep, and later matching combinations are Duplicate. For instance, the same customer with invoice numbers 104 and 105 is kept twice, while a repeated pair of that customer ID and invoice 104 is marked Duplicate. Adjust the blank rule if incomplete records should also be evaluated rather than left unlabelled.

Use Advanced Filter for a separate unique list

Advanced Filter is a useful alternative when you want a unique result without immediately deleting rows from the source. Select a cell in the list, then choose Data > Sort & Filter > Advanced. In the dialog, choose Copy to another location, confirm the List range and Copy to destination, and select Unique records only. The copied result contains unique records based on the full set of columns in the list; it is not a way to deduplicate by one chosen key while preserving arbitrary values from the other columns.

Advanced Filter can also filter the list in place with Unique records only, which hides rows that do not appear in the unique result rather than deleting them. That is different from Remove Duplicates, which removes rows from the selected data. For a repeatable, reviewable output, copying the unique result to a new area or sheet is usually clearer. Check that the list range includes the header row and all columns that belong together before running the filter.

Common causes of unexpected results

  • Only one column was selected. If the other fields were not included, Excel may not keep the complete record you expected. Work with the full table and select the intended key columns in the dialog.
  • The wrong columns were checked. Selecting every field requires complete-row matches; selecting only an ID treats repeated IDs as duplicates even when other fields differ. Review the checkboxes before confirming.
  • The wrong row was kept. Excel preserves the first row in current order. Sort the full dataset by the field that defines priority before deduplicating.
  • Some apparent matches remain. Values that look alike may differ because of spaces, punctuation, data type, or other hidden differences. Inspect the underlying cells and standardize the data before running the operation.
  • Headers were treated as data. Check My data has headers when the first row contains column names. Otherwise, a header could be included in the duplicate comparison.
  • Rows were permanently removed from the working list. Remove Duplicates is destructive to that range. Keep an untouched copy if the removed entries may be needed for reconciliation or auditing.

Formatting alone is not a dependable way to define a duplicate. Two cells may look the same while containing different underlying values, and different-looking formats do not necessarily make different values. If a comparison has business rules—such as ignoring capitalization, trimming spaces, or treating two date formats as equivalent—clean or normalize the fields first and verify the result on a sample.

Remove Duplicates, filters, and Advanced Filter compared

Choose Remove Duplicates when the goal is to clean the selected data immediately and you have decided which columns define a repeated record. Choose a helper formula when you want an explicit Keep or Duplicate label, need to filter the result, or want to review rows before deletion. Choose Advanced Filter when you want a separate list of unique complete records or want to hide repeated rows without deleting them.

These methods answer slightly different questions. A normal AutoFilter displays records based on criteria you choose; it does not automatically identify the first copy of each value. Conditional formatting can highlight repeated values for visual inspection, but highlighting is not the same as retaining one row. When the rows contain important details, a helper label or a copied result makes the decision more visible and easier to check.

Frequently Asked Questions

Does Excel Remove Duplicates keep the first occurrence?
Yes. It keeps the first matching row in the current order of the selected range and removes later matches, based on the columns checked in the dialog.

Can I remove duplicates based on one column but keep the whole row?
Yes. Select the complete dataset, then check only the identifying column in the Remove Duplicates dialog. Excel uses that column to find repeats and retains the first full row for each value.

Does Excel’s Filter command remove duplicate rows?
No. Data > Filter lets you show or hide rows according to criteria. It does not delete later copies automatically. Use Remove Duplicates, a helper column, or Advanced Filter’s unique-record option for that purpose.

How do I keep the newest duplicate instead of the first one?
Sort the entire dataset by its date or timestamp from newest to oldest, then run Remove Duplicates using the relevant ID columns. The first row for each ID will then be the newest one, provided the sort is correct and all columns move together.

Can I recover rows removed by Remove Duplicates?
Use Ctrl+Z on Windows or Command+Z on Mac immediately after the operation, if it is still in the undo history. For dependable recovery, make a copy of the worksheet or workbook before removing duplicates.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.