Filter Excel Remove Duplicates in a List

Formulas & functions · Updated November 20, 2026

Excel’s Filter command hides rows; it does not remove duplicate entries. To delete duplicates, select the full data range and choose Data > Remove Duplicates; to create a separate list of distinct values in Microsoft 365, use the UNIQUE function.

What filtering does—and does not do

Filtering is a way to display only records that meet chosen conditions. For example, a filter can show orders from one city or products with a particular status. Rows that do not match are hidden temporarily, not deleted. Clearing the filter makes those rows visible again.

A filter can help you inspect a list, but it is not a duplicate-removal tool. The filter’s dropdown may show each distinct value as a selectable criterion, yet choosing one value displays its matching rows; it does not reduce repeated rows to a single record. If a list contains “Mira” three times, filtering for Mira still shows all three matching rows.

Choose a method based on what you need to keep:

  • Delete repeated records from the original data: use Remove Duplicates.
  • Show one copy of each record in a separate output: use Advanced Filter or a UNIQUE formula.
  • Find repeated entries without deleting anything: highlight duplicates with Conditional Formatting or use a helper formula.

Remove duplicates from the original list

In Excel for Microsoft 365 on Windows, select a cell in the data and choose Data > Remove Duplicates. In the dialog box, select the columns that define a duplicate, then click OK. Excel reports how many duplicate values it removed and how many unique values remain. The operation removes matching rows from the selected data; it is not just a display filter.

  1. Make a copy of the worksheet or workbook if the original records may be needed later. Removing duplicates changes the data, and Ctrl+Z may not be a dependable recovery plan after subsequent actions.
  2. Arrange the data as a clear table-like range, with one header row and one record per row. Avoid selecting only one column if the other columns contain details that belong to each record.
  3. Click within the data, then choose Data > Remove Duplicates.
  4. In the dialog, confirm that My data has headers is selected when the first row contains column names. Check the columns Excel should use when deciding whether records repeat.
  5. Click OK and review Excel’s summary. Check the remaining rows and the surrounding columns before continuing with other work.

On Excel for Mac, the command is also on the Data tab as Remove Duplicates; the dialog and available choices are similar. Ribbon layout can vary with window size and Excel version. Excel for the web also provides duplicate-removal options in supported current versions, though the ribbon arrangement may differ.

Choose which columns define a duplicate

The column checkboxes determine whether Excel compares an entire record or only selected fields. Suppose a table has Name, Department, and Location columns. If all three are checked, two rows count as duplicates only when the values match across all three checked columns. If only Name is checked, a second row with the same name can be removed even if its department or location differs. That may discard a legitimate record.

Before removing data, ask what “duplicate” means for the task. Two sales rows for the same customer are not necessarily duplicates if they represent separate orders. Two rows with the same email address might be duplicates in a contact list, but perhaps not if each row records a different subscription or date. Select only the fields that together define a repeated record.

Excel keeps one occurrence and removes the other matching rows. It does not combine information from those rows. If repeated records contain different notes or dates, decide how to reconcile that information before using this command.

Extract distinct values with Advanced Filter

Advanced Filter can copy unique records to another location while leaving the original list intact. It is useful when you want a static result—a separate list that will not automatically change when the source data changes.

  1. Make sure the list has a header row and select a cell in it.
  2. Choose Data > Advanced in the Sort & Filter group.
  3. Choose Copy to another location.
  4. Check the List range and specify the destination in Copy to. Include the header cell when setting the destination.
  5. Select Unique records only, then click OK.

For example, to copy distinct department-and-location combinations, select both of those columns as the list range. Advanced Filter treats rows as records, so the combination of selected fields determines uniqueness. If you select only Department, rows with the same department are considered duplicates even when their locations differ.

Advanced Filter is different from the ordinary dropdown filters on a table. The ordinary filters show or hide records according to criteria; Advanced Filter has a specific Unique records only option. If the dialog’s ranges are wrong, the output may be incomplete, so verify the copied headers and first few results.

Use UNIQUE for a live distinct list

In Excel for Microsoft 365, UNIQUE returns distinct values or rows in a separate output area. Unlike Remove Duplicates, it leaves the source list untouched. The result spills into cells below or beside the formula, and it can update when source values change.

With names in A2:A10—Mira, Devon, Mira, Sam, Devon, Lee, Mira, Sam, and Noor—enter =UNIQUE(A2:A10) in an empty cell. It returns Mira, Devon, Sam, Lee, and Noor once each, in the order of their first appearance. Leave enough empty cells below the formula for the results to spill; occupied cells can cause a #SPILL! error.

To return unique full records, select the whole set of columns in the formula’s range rather than just one field. For instance, with a table’s records in A2:C100, =UNIQUE(A2:C100) returns distinct rows based on the combination of values in all three columns. A person’s name alone may repeat while the complete name, department, and location row is unique.

UNIQUE is available in Microsoft 365 and Excel 2021 or later. It is not available in older perpetual versions such as Excel 2019. In those versions, use Remove Duplicates, Advanced Filter, or another compatible method. A UNIQUE formula is also not the same as a permanent cleanup: it creates a result elsewhere and does not delete source rows.

Common problems and how to avoid them

  • Selecting just one column: Excel can remove rows based on that column while other fields contain different information. Select the full record range, then check the intended duplicate-defining columns.
  • Including or omitting the header incorrectly: Mark My data has headers when appropriate. A header treated as ordinary data can appear in the results or be handled incorrectly.
  • Whitespace and inconsistent entries: “North” and “North ” can look alike while containing different text because one has a trailing space. Standardize imported or manually entered text before deduplicating if near-matches should count as the same.
  • Confusing blanks with repeated records: Several blank cells in one field do not necessarily mean whole rows are duplicates. Decide whether blank fields are meaningful, and inspect the other columns before removing records.
  • Sorting or filtering first and assuming it deletes data: Sorting changes order, and filtering hides rows. Neither permanently removes duplicates; use the dedicated command or a distinct-list method.
  • Trying to undo after many later changes: Remove Duplicates changes the selected data. Save a backup first if the removed rows may matter, particularly when the workbook is shared or feeds another process.

Which method should you use?

Use Remove Duplicates when the goal is to clean the original list and you have confirmed which columns define a duplicate. It is direct, but destructive: removed rows are not merged with the rows that remain. Keep a copy when source history matters.

Use Advanced Filter when you need a distinct snapshot while preserving the source. Use UNIQUE when you want a formula-driven output that refreshes with the source list and your Excel version supports dynamic arrays. Use Conditional Formatting when the goal is to locate repeated entries for review rather than remove them. These methods answer different questions, so a filter alone is not a substitute for any of them.

Frequently Asked Questions

Can Excel’s filter remove duplicate values?
No. Standard filters hide rows that do not match the selected criteria; they do not delete repeated rows. Use Data > Remove Duplicates to remove them, or Advanced Filter or UNIQUE to create a distinct list.

How do I remove duplicates but keep the original list?
Use Advanced Filter and select Copy to another location and Unique records only, or enter a UNIQUE formula in an empty area in Microsoft 365 or Excel 2021 and later. Both leave the source records in place.

Does Remove Duplicates compare every column?
It compares the columns you check in its dialog. Check all record fields to find identical rows, or check only the fields that define a duplicate for your particular task. A too-narrow selection can remove records that contain useful differences.

Does Excel keep the first or last duplicate?
Remove Duplicates keeps one occurrence and removes matching ones; it does not combine their data. If which record survives matters—for example, you need the newest date—sort or otherwise decide which record should be retained before removing duplicates, and keep a backup.

Why does my UNIQUE formula show #SPILL!?
The formula’s output needs empty cells in which to spill. Clear the cells blocking the results, or move the formula to an area with enough space. Also confirm that the Excel version supports UNIQUE.

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.