In an Excel table, open a column’s filter arrow to see its distinct values, then select the value or values whose rows you want to display. That filters the table; it does not create a separate list with one copy of each value. To make such a list in Microsoft 365, enter =UNIQUE(Sales[Region]) in a cell outside the table, replacing the table and column names with yours.
What “unique values” means in a table filter
The phrase can refer to two different tasks. A table’s filter menu lists the different values found in that column so you can choose which matching rows to show. A distinct-value list, by contrast, is a new output containing one instance of each value. Excel’s filter menu helps with the first task; the UNIQUE function or Advanced Filter is more suitable for the second.
For example, a Region column might contain West, East, West, and North. Its filter menu offers West, East, and North as choices. Selecting West shows both West rows; it does not keep just one West row. This distinction is important when preparing summaries, lookup lists, or reports: hiding rows is not the same as removing repeated entries.
Filter a table to show selected values
Excel tables include filter arrows in their header row. If they are not visible, click a cell in the table and choose Table Design > Filter Button if that option is available in the ribbon, or use Data > Filter to turn filtering on. Ribbon labels can vary slightly with window size, but the Filter command is on the Data tab in Microsoft 365 for Windows.
- Click the arrow in the header of the column you want to filter, such as Region.
- In the checklist, clear (Select All), then select the value or values to keep. You can also use the search box to find a value in a long list.
- Choose OK. Excel hides rows that do not meet the selection; it does not delete them.
- To show all rows again, reopen that column’s menu and select Clear Filter From the column, or use Data > Clear to clear filters.
Each checkbox in the menu represents a value currently found in the filtered column, rather than every row containing that value. If a customer name appears 20 times, the name normally appears once in the checklist, and selecting it displays all 20 matching records. Filters on different columns combine: choosing West in Region and Active in Status shows only rows meeting both conditions.
Create a distinct list with UNIQUE
For a reusable list that updates when the source table changes, put a dynamic-array formula in a blank cell outside the table. In this example, the table is named Sales and has a column named Region. With region data in Sales[Region], enter =UNIQUE(Sales[Region]). Excel returns each different region once and spills the results into cells below the formula.
UNIQUE is available in Microsoft 365 and Excel 2021 or later, including supported versions of Excel for Mac and Excel for the web. It is not available in older perpetual versions such as Excel 2019. The formula uses a structured table reference, so it includes the table’s data column as the table grows. Keep the cells below the formula clear; an existing value in its spill area causes a #SPILL! error. A dynamic array formula cannot spill from within an Excel table, so place it in the worksheet grid outside the table.
The basic result is not necessarily alphabetized. With the same Sales table and Region column, enter =SORT(UNIQUE(Sales[Region])) outside the table to return the distinct regions in sorted order. SORT and UNIQUE are both available in Microsoft 365 and Excel 2021 or later. If blanks occur in the source column, the result may include a blank entry; clean or account for blank source cells if that is not wanted.
Distinct values versus values that occur once
“Unique” can also mean values that appear exactly one time. That is different from a distinct list. In the example West, East, West, North, the distinct list is West, East, North, while the values occurring exactly once are East and North.
With region data in the Sales table’s Region column, enter =UNIQUE(Sales[Region],FALSE,TRUE) in a blank cell outside the table to return only items that occur exactly once. The second argument says to compare down the column, and the third requests values occurring one time only. This option can be useful for spotting one-off entries, but it is not the right choice when you want one representative of every category.
Case differences are generally not treated as separate values by UNIQUE; for instance, it does not normally distinguish West from WEST as two categories. If capitalization, extra spaces, or inconsistent spelling matters to the result, standardize the source data before relying on a distinct list. A value with a trailing space can look identical on screen while still being a different text entry.
Use Advanced Filter for a copied result
Advanced Filter is an alternative when you need a one-time copied list and do not have a version of Excel with dynamic-array functions. On Windows, select the source column including its header, then choose Data > Advanced in the Sort & Filter group. In the dialog, verify the List range, choose Copy to another location, specify a destination cell, and check Unique records only before clicking OK.
When the selected list range contains just one field, Advanced Filter copies distinct values from that field. If you select an entire multi-column table instead, “Unique records only” means distinct rows across all selected columns. Two rows with the same Region but different sales amounts are different records, so both may remain. The copied output is a snapshot: it does not automatically update when the source table changes. On Mac, Advanced Filter availability and dialog placement can depend on the Excel release; if the command is not present, use UNIQUE in a supported release or copy the data and use Remove Duplicates.
Filtering rows by a particular value
If your goal is to examine all records for a category, use the table filter rather than extracting distinct values. For instance, selecting West in the Region filter shows every West record with its related date, customer, and amount. The other columns stay connected to each row, which is essential when reviewing complete records.
For a separate, formula-driven view of matching rows, FILTER can return entire matching rows outside the table. With the table named Sales, a Region column, and a blank output area outside the table, enter =FILTER(Sales,Sales[Region]="West"). This returns rows whose Region is West and updates as the table data changes. FILTER requires Microsoft 365 or Excel 2021 or later. Make sure the destination area has room for the returned rows and columns; otherwise Excel may show #SPILL!.
Remove Duplicates is not the same as filtering
Use Data > Remove Duplicates only when you intend to delete repeated records from the selected data. Excel asks which columns define a duplicate. If you select only Region, it retains one row per region and removes other rows with the same region, even if those rows have different customers or amounts. If you select all columns, only rows identical across all selected fields count as duplicates.
Unlike a filter, removal changes the data itself and can discard information. Make a copy of the worksheet or workbook first if the original records matter. If your goal is simply to inspect one value at a time, filter the table. If you need a separate current list, use UNIQUE. If you need to permanently clean duplicate records, review the selected columns carefully before using Remove Duplicates.
Common problems and practical checks
- The filter menu does not show a value: Clear other column filters first. A value can be absent from the checklist because another active filter has hidden every row containing it.
- A value appears more than once in the result: Check for spaces, inconsistent spelling, or other text differences. The filter checklist groups identical cell values, but visually similar text is not always identical.
- The UNIQUE formula returns #SPILL!: Clear cells in the intended output area, including cells that appear blank but contain spaces or formulas. Also move the formula outside the table.
- The formula is not recognized: Check your Excel version. UNIQUE, SORT, and FILTER require Microsoft 365 or Excel 2021 or later. Older Excel versions can use Advanced Filter or a PivotTable as alternatives.
- The distinct list contains a blank: Inspect the source column for empty cells. A blank can be a legitimate item in the result; remove or filter blanks from the source if they should not appear.
In Excel for the web, table filter arrows and dynamic-array formulas are generally available, though the ribbon layout can differ from Windows desktop. Excel for Mac also supports table filtering and dynamic-array functions in current Microsoft 365 releases. If a menu command is missing, check the application version and update status rather than assuming the workbook is damaged.
Choosing the right method
Use the table dropdown when you want to hide unrelated rows temporarily and keep the full data intact. Use UNIQUE when you want a live, separate list of categories that can feed a chart, validation list, or summary. Choose Advanced Filter when you need a copied, static set of distinct values, particularly in an older Excel version. Choose Remove Duplicates only for an intentional, permanent cleanup.
A useful workflow is to keep the source table unchanged, create any distinct list in a separate area or worksheet, and label the output clearly. For example, name a heading “Distinct regions” above the UNIQUE result. That makes it apparent that the output is a derived list, not the full transaction data. For analysis that needs counts as well as categories, a PivotTable can be a better fit: place Region in Rows and a field such as Region in Values, then summarize by count.
Frequently Asked Questions
Does a table filter remove duplicate values?
No. It hides rows that do not match the filter selection. If a selected value occurs on many rows, those rows all remain visible. Use UNIQUE to create a distinct list or Remove Duplicates to delete repeated records.
How do I show each value only once in Excel?
With values in a table column named Region in a table named Sales, enter =UNIQUE(Sales[Region]) in an empty cell outside the table. Excel spills a list containing one instance of each distinct value.
Can I filter a table to show only values that occur once?
The regular filter checklist does not directly mean “occurs exactly once.” Use a formula such as =UNIQUE(Sales[Region],FALSE,TRUE) to list values occurring one time, or use a count-based helper column if you need to filter the original records themselves.
Why does UNIQUE return a blank?
The referenced column may contain blank cells. The function can include a blank in its result. Check for empty rows or blank entries in the table column, and remove or exclude them if your list should contain only populated values.
Can UNIQUE be used inside an Excel table?
A dynamic array cannot spill within a table’s rows. Enter the formula in a normal worksheet cell outside the table, leaving enough empty space for the returned list.
Related
- Excel Unmerge Cells Shortcut: Steps, Limits, and Fixes
- Excel Shortcut for Selecting a Column in One Step
- Excel Shortcut for Filtering a Table: Keys and Tips
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Let Text Overflow? What Happens to Cell TextYes. In Excel for Microsoft 365 on Windows, text can extend visually past a cell’s right edge when…
- Excel Shortcut for Clearing a Filter in a WorksheetIn Excel for Microsoft 365 on Windows, press Alt, then A, then C to clear the active filter…
- Excel Calculation for Hours Worked: Formulas and TipsFor a shift with start time in A2, end time in B2, and unpaid break duration in C2,…
- Excel Formula for Holiday Dates: Fixed and Moving DaysFor a holiday that falls on the same month and day each year, use =DATE($B$1,1,1), assuming the year…
- Revit IF Formula Examples for Family ParametersIn a Revit family, an IF formula tests a condition and returns one value when it is true…
- Excel IFS Statement Examples and How to Use the FunctionExcel’s IFS function checks conditions in order and returns the result paired with the first condition that is…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.