Excel Filter No Duplicates? Show Unique Values

Formulas & functions · Updated November 19, 2026

Excel’s regular AutoFilter does not have a one-click option to show only unique values. To hide duplicate records without deleting them, use Data > Sort & Filter > Advanced and select Unique records only; for a live list that updates as the source changes, use the UNIQUE function.

What Excel means by no duplicates

The phrase no duplicates can describe two different results. You might want one copy of each value, such as a list of the different product names in a column. Or you might want values that occur exactly once, excluding anything that appears two or more times. Excel treats these as separate tasks, so choosing the right method matters.

There is another distinction: a duplicate can mean a repeated value in one column or a repeated complete row. If two records have the same customer but different order dates, they are duplicates when you look only at the customer column, but they are different records when you compare the whole table. Before filtering, decide which columns define a duplicate.

AutoFilter, the arrows that appear beside column headings, is useful for narrowing a list by values or conditions. Its dropdown lets you select and clear individual values, but it does not automatically remove repeated entries from the displayed list. Advanced Filter and the UNIQUE formula provide that kind of distinct result.

Use Advanced Filter to hide duplicate records

Advanced Filter is the built-in choice when you want to filter the existing list and leave its data in place. In Excel for Microsoft 365 on Windows, first make sure the list has a header row and no completely blank row splitting the data. Click a cell in the list, then choose Data > Sort & Filter > Advanced. You can also select the full range yourself if Excel does not identify it correctly.

  1. In the Advanced Filter dialog, choose Filter the list, in-place.
  2. Check that the List range includes the column headings and all records you want to compare.
  3. Leave Criteria range empty if you are not applying a separate condition.
  4. Select Unique records only, then choose OK.

Excel hides records that it considers duplicates and leaves one copy visible. The rows are not deleted, so you can restore the full list by clearing the filter, using Data > Sort & Filter > Clear, or reopening Advanced Filter and filtering again as needed. If you choose Copy to another location instead, specify the destination in Copy to; this creates a separate result and keeps the source list visible.

Advanced Filter compares the fields in the selected list range. If the range contains Name, Department, and Location, a row is a duplicate only when the values across those fields match another row. If you select only the Name column, repeated names are treated as duplicates even if their departments or locations differ. Include the heading row, because Excel uses it to identify the fields.

Get a concrete result with a sample list

Suppose A1 is headed Region and A2:A8 contains North, South, North, East, South, West, and East. With the list range set to A1:A8, Advanced Filter with Unique records only leaves one visible entry for each region: North, South, East, and West. The first occurrence is retained in the displayed list; later copies are hidden. The underlying repeated values remain in the worksheet.

This is handy for checking which categories occur in a larger table without changing the source. It can also help prepare a short list for a validation dropdown or a summary. If you need to send the result to someone else, copying the unique records to another location is often clearer than leaving some source rows hidden. Check the copied range before using it, especially if the source includes formulas or extra columns that should not be part of the comparison.

Use UNIQUE for a live list

For a results list that recalculates when the source values change, use the dynamic-array function UNIQUE. With regions in A2:A8 as in the example above, enter =UNIQUE(A2:A8) in an empty cell. The result spills into cells below it as North, South, East, and West, in the order their first instances appear. Leave enough empty cells below the formula, or Excel may show a spill error.

UNIQUE can also return distinct rows across several columns. With customer names in A2:A6 and departments in B2:B6, and rows such as Ana/Sales, Ben/Support, Ana/Sales, Ana/Finance, and Ben/Support, enter =UNIQUE(A2:B6) in an empty cell. It returns three distinct name-and-department pairs: Ana/Sales, Ben/Support, and Ana/Finance. Repeated complete pairs collapse, but Ana’s two different departments remain separate.

UNIQUE is available in Microsoft 365 and Excel 2021 or later, including supported current versions of Excel for the web and Mac. The result is formula-driven rather than a fixed copy: edits to the source range update the spilled result. If the source range grows beyond the range referenced in the formula, however, you must expand the reference or use a suitable Excel table reference so new records are included.

Unique values versus values that occur once

By default, UNIQUE returns one instance of every distinct item, including items that occur repeatedly. That is usually what people mean by a list with no duplicates. It does not mean that every returned item appeared only once in the source.

To return only items whose source value occurs exactly once, use UNIQUE’s exactly_once argument. With the same region values in A2:A8, enter =UNIQUE(A2:A8,FALSE,TRUE) in an empty cell. The result is West, because it is the only region in that example that appears once. North, South, and East are excluded because each is repeated. This option is useful for identifying one-off entries, but it is not a substitute for a normal distinct list.

The second argument, FALSE, tells UNIQUE to compare records by row rather than by column; that is the usual choice for a vertical list. For most everyday tasks, the simpler =UNIQUE(A2:A8) is easier to read and does exactly what is needed.

Advanced Filter, UNIQUE, or Remove Duplicates?

These options solve related but different problems. Advanced Filter is a non-destructive way to hide repeated records in the source list or copy distinct records elsewhere. UNIQUE creates a formula result that can update as its source changes. Remove Duplicates edits the selected data by deleting repeated rows, so it is the option that changes the source itself.

To use Remove Duplicates, select a cell in the data and choose Data > Data Tools > Remove Duplicates. Confirm whether the data has headers, select the columns Excel should use to identify duplicates, and choose OK. Excel keeps one occurrence and removes later matching rows from the selected range. Make a copy of the data first if you may need the removed records; this operation is not the same as temporarily filtering rows.

  • Choose Advanced Filter when you want to inspect distinct records without deleting source rows.
  • Choose UNIQUE when you want a separate, automatically updating list and have a supported Excel version.
  • Choose Remove Duplicates only when you intend to delete repeated records from the selected data.

For either Advanced Filter or Remove Duplicates, the selected columns determine what counts as a match. If you select just Order ID, rows with the same ID are treated as duplicates even if other fields differ. If you select every column, only rows matching across all selected fields are considered duplicates. That makes column selection a data decision, not just a technical setting.

Common problems and checks

If the result still appears to contain duplicates, first check whether the values are truly identical. Extra spaces, inconsistent spelling, different punctuation, or a number stored as text can make entries look alike while Excel treats them differently. For example, a category with a trailing space may not match the same category without it. Clean or standardize the source values before filtering if those differences are accidental.

Also check the selected range. A range that omits some records cannot compare them, and leaving out a relevant column can collapse records that should remain distinct. A missing header, a blank row within the list, or selecting only one column when you mean to compare complete records can produce an unexpected result. For a table with several fields, confirm that the entire intended table is included.

With UNIQUE, a #SPILL! error usually means that one or more cells in the expected output area are occupied, merged, or otherwise unavailable. Clear the cells below or beside the formula where the result needs to appear. Also verify that the formula’s source range includes new rows; a fixed reference such as A2:A100 will not automatically cover data entered in A101.

Finally, decide how blank cells should appear in the output. A source range containing blanks can produce a blank item in a distinct list. If blanks are not meaningful in your report, remove them from the source or use an appropriate filtering approach rather than assuming that deduplication will discard them automatically.

Choosing the right method

For a quick review of a worksheet where the original rows must remain intact, Advanced Filter is the most direct answer to Excel filter no duplicates. For a reusable report or a distinct list that should refresh after edits, UNIQUE is usually more convenient. If the goal is to clean a dataset permanently, Remove Duplicates can do that, but only after confirming the comparison columns and keeping a backup if the original records matter.

In Excel for Mac, the same general methods are available, although ribbon group labels and dialog layout can vary slightly by release. Look on the Data tab for Advanced Filter or Remove Duplicates. In Excel for the web, UNIQUE is available in supported current versions, while the desktop Advanced Filter dialog and some data-management commands may not have the same interface or availability. If a command is missing, use the desktop app or the formula method available in your version.

Frequently Asked Questions

Can I filter duplicates directly from the AutoFilter dropdown?
The standard dropdown filters values you select, but it does not automatically retain one copy of each value. Use Advanced Filter with Unique records only, or create a distinct formula list with UNIQUE.

Does Advanced Filter delete duplicate rows?
No. Filter the list, in-place hides matching repeated records; it does not remove them from the worksheet. Use Remove Duplicates if you deliberately want to delete repeated rows, and copy the data first if you need a safeguard.

How do I show unique rows instead of unique values?
Select the full table range in Advanced Filter so Excel compares the selected fields together, or use UNIQUE on the full multi-column range, such as =UNIQUE(A2:C100). Each distinct combination of values is returned once.

Why does UNIQUE return a blank?
If the referenced range contains blank cells, the distinct result can include a blank entry. Remove blanks from the source or adjust the method if a blank should not appear in the final list.

Can I use UNIQUE in older Excel versions?
UNIQUE requires Microsoft 365 or Excel 2021 and later. In older versions, use Advanced Filter with Unique records only, or remove duplicates from a copy of the data if a formula-based dynamic result is not available.

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.