UNIQUE and FILTER in Excel Tables: A Practical Guide

Formulas & functions · Updated November 20, 2026

To return a sorted list of distinct items from an Excel Table that match a condition, enter =SORT(UNIQUE(FILTER(Sales[Item],(Sales[Region]=G2)*(Sales[Item]<>"")))) in a blank cell outside the table. Here, Sales is the table name, Item and Region are column headers, and G2 contains the region to match; the results spill into nearby cells and update when the table changes.

What UNIQUE and FILTER do together

UNIQUE and FILTER solve two related but different problems. FILTER returns the records that meet a condition, such as sales rows for the West region. UNIQUE removes repeated values from a list or array. Combining them lets you ask a more useful question: which different items, customers, or categories occur among the records that meet my condition?

For example, imagine a Sales table with Item, Category, Region, and Qty columns. The West rows contain Notebook, Pen, Notebook, and Folder. Filtering by West returns all four matching rows; applying UNIQUE to the Item values leaves Folder, Notebook, and Pen if the result is sorted alphabetically. Without SORT, UNIQUE normally keeps the order in which each value first appears in the source.

These are dynamic array functions, available in Microsoft 365 and Excel 2021 or later, including Excel 2024. They are also available in Excel for the web. Excel 2019 and earlier do not support these functions, so readers using those versions need another method.

Set up an Excel Table first

Tables make formulas easier to read and maintain than fixed cell ranges. They also expand as you add records, so a formula referring to a table column can include new rows without manually editing its range.

  1. Arrange the source data in columns with one header per column, such as Item, Category, Region, and Qty. Avoid blank header cells and merged cells in the data area.
  2. Select a cell in the data and choose Insert > Table. Confirm that the range is right and that My table has headers is selected.
  3. Select a cell in the new table, open the Table Design tab, and set the Table Name box to Sales. Table names cannot contain spaces.
  4. Put the value you want to filter by in a cell outside the table. In the examples below, G2 contains West.

Column references such as Sales[Item] are structured references: they mean the Item column in the Sales table. If your actual table or column uses a different name, replace those names in the formula. The column header must match the table header, including spaces and punctuation.

Return all distinct values from a table column

If you want every distinct item, without applying a region or category condition, use UNIQUE on the table column. With the table named Sales and its item names in the Item column, enter this formula in a blank cell outside the table:

=SORT(UNIQUE(FILTER(Sales[Item],Sales[Item]<>"")))

The formula filters out empty cells, removes duplicate item names, and sorts the result alphabetically. For the example data, it returns Folder, Notebook, and Pen. If first-seen order is more useful, remove SORT and use =UNIQUE(FILTER(Sales[Item],Sales[Item]<>"")). That version would return Notebook, Pen, and Folder if Notebook is the first item in the table.

Filtering blanks out is useful because empty source cells can otherwise appear as an empty-looking result. It also makes the intended output explicit: the list contains actual item names, not blank entries.

Filter distinct values by one condition

To return distinct items for a selected region, place the region name in G2 and use a condition that checks each value in Sales[Region] against that cell. With the table named Sales, the Item and Region columns present, and G2 containing West, enter this formula outside the table:

=SORT(UNIQUE(FILTER(Sales[Item],(Sales[Region]=G2)*(Sales[Item]<>""))))

FILTER tests each row. The first test keeps rows where Region equals the value in G2; the second excludes blank item names. The asterisk between the tests acts like AND: both conditions must be true for a row to be included. In the example, the sorted result is Folder, Notebook, and Pen. Notebook appears once even though it is listed twice in the West records.

Change G2 to East and the formula recalculates for that region. Because the condition points to a cell rather than containing the word West directly, the same formula can serve as a small interactive report. You can use a data-validation drop-down in G2 if you want users to choose from permitted region names, but the formula itself does not require a drop-down.

Use more than one criterion

You can combine criteria when a report needs to narrow its results further. Suppose you want distinct items for one region and one category. With Sales[Item], Sales[Region], and Sales[Category] as table columns, G2 containing West, and H2 containing Stationery, enter this formula outside the table:

=SORT(UNIQUE(FILTER(Sales[Item],(Sales[Region]=G2)*(Sales[Category]=H2)*(Sales[Item]<>""))))

Each parenthesized comparison creates a row-by-row true-or-false test. Multiplying the tests combines them as AND, so a row must be in West, belong to Stationery, and have a nonblank item to qualify. For the example data, this returns Notebook and Pen, alphabetically. If there are no matching rows, FILTER returns an error because no if-empty result was supplied. You can handle that deliberately with an error-handling formula rather than leave an unexpected error in a report.

For OR logic, addition can combine tests: a row qualifies if either test is true. For instance, a category test for Stationery plus one for Filing can include both categories. Be careful with mixed logic: use parentheses around each comparison, and test the formula with a small set of records where you know which rows should match.

Understand the spill range

UNIQUE and FILTER return arrays, not just one value. Excel places the first result in the formula cell and spills the remaining values into neighboring cells, usually downward. This output range is called the spill range. When the source table gains or loses records, the results can change size automatically.

Enter a dynamic array formula in a regular worksheet cell outside the table. Excel Tables do not support spilled array results inside their data body. If you enter the formula in a table cell, Excel may show a spill-related error or treat the entry as a calculated-column formula instead of allowing the list to expand.

Keep the cells below the formula clear. A value, another formula, or a merged cell in the required output area can block the spill and cause #SPILL!. Clear or move the blocking content, and the results should be able to expand. A formula that returns only one result may appear to work until a later edit makes the output longer, so leave room for growth.

In Excel for Microsoft 365 on Windows, clicking a spilled result can help identify the formula cell at the top of the range. Excel for Mac and Excel for the web also support dynamic arrays, though ribbon layout and selection details can vary. The formula structure and table references are generally the same.

Table filters are not the same as FILTER formulas

An Excel Table can show drop-down arrows in its headers. Choosing a value from one of those arrows hides rows that do not match; it changes which records are visible on screen. The FILTER function, by contrast, returns a new array based on conditions written in the formula. These are separate features, despite their similar names.

A common misconception is that UNIQUE automatically considers only rows currently visible after someone uses a table’s filter arrows. A formula such as UNIQUE(Sales[Item]) evaluates the referenced column’s values; it does not automatically treat the table’s visible rows as its input. To make a formula respond to a region selection, specify that condition in FILTER, as in the examples above. If a report must respond specifically to manually hidden rows, it needs a visibility-aware approach rather than assuming the ordinary formula follows the table’s on-screen filter.

For a quick one-time cleanup, the ribbon command Data > Remove Duplicates can remove repeated records from a selected range or table. That command changes the source data, while UNIQUE creates a formula-driven result and leaves the original records intact. Choose based on whether you need a live list or want to edit the data itself.

Common errors and how to fix them

  • #SPILL!: Check whether cells in the output area contain data or whether the formula was placed inside a table. Move the formula outside the table and clear the spill area.
  • #CALC!: FILTER may have found no qualifying rows. Check the condition values for spelling, extra spaces, or mismatched data types. If an empty result is expected, use FILTER’s optional if_empty argument or handle the no-match case explicitly.
  • #NAME?: The Excel version may not support UNIQUE or FILTER, or a table or column name may be misspelled. Confirm the version and inspect the structured references.
  • Unexpected duplicates: Values that look the same may differ because of trailing spaces, nonprinting characters, or inconsistent spelling. Clean the source data if those differences should count as the same item.
  • Unexpected blank results: Source cells may be empty, or a formula in the source may return an empty string. Add a nonblank condition such as Sales[Item]<>"" when blanks should be excluded.
  • Formula separator errors: The formulas here use commas, as requested for standard English-language Excel syntax. Some regional settings display semicolons as argument separators; use the separator required by that installation if Excel rejects a pasted formula.

Also check whether values have different data types. For example, a number stored as text may not behave exactly like a numeric value in comparisons. If results still look wrong, inspect a few source cells directly and compare their contents, not only their displayed formatting.

Choosing between formulas and other methods

Use UNIQUE with FILTER when you want a reusable output that updates as table data or selection criteria change. It is particularly suitable for report lists, dependent choices, and quick summaries that should leave the original records untouched. Adding SORT makes the output easier to scan, but sorting is optional and changes the order from first-seen order to sorted order.

Use a PivotTable when the goal is a broader summary with counts, sums, or several fields grouped together. A PivotTable can summarize items by region and calculate totals, but it is not the same as a simple spilled distinct-value list, and it may require refreshing depending on how it is set up. Use Remove Duplicates for a one-time edit to the underlying data, not when you need the result to update automatically.

For Excel 2019 or an earlier release without these dynamic array functions, a PivotTable may provide a practical distinct list, or Power Query can load and transform data with duplicate removal. The best alternative depends on whether the result must refresh, whether the source should remain unchanged, and how comfortable the workbook’s users are with those tools.

Frequently Asked Questions

Can I use UNIQUE with an Excel Table column?
Yes. A reference such as Sales[Item] points to the Item column in the Sales table. Put the formula in a worksheet cell outside the table so Excel has room to spill the result.

Does UNIQUE keep the first occurrence or sort the results?
UNIQUE preserves first-occurrence order by default; it does not alphabetize automatically. Wrap it in SORT when you want sorted output.

Will the list update when I add rows to the table?
In supported Excel versions, a structured reference such as Sales[Item] expands with the table, and the dynamic array formula recalculates. The spill area must still have enough unobstructed cells.

Why does my formula show #SPILL!?
Excel cannot place every result in the spill range. Clear cells blocking the output, unmerge any obstructing cells, or move the formula outside the table.

Does UNIQUE ignore rows hidden by a table filter?
No, not by itself. A table’s filter arrows hide rows on screen, but a regular UNIQUE formula does not automatically use only those visible rows. Put the desired filter condition into FILTER if the result should depend on a field such as Region.

Can I use this in older Excel versions?
UNIQUE, FILTER, and SORT require Excel 2021 or later or Microsoft 365. In older versions, consider a PivotTable, Power Query, or Remove Duplicates, depending on whether you need a live result or a one-time data change.

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.