Google Sheets Filter Unique Values: Formula and Steps

Yes. To list each nonblank value once in Google Sheets, enter =UNIQUE(FILTER(A2:A, A2:A<>"")) in an empty cell, assuming the source values are in column A from row 2 down. The formula filters out blank cells, then returns the remaining values without duplicates; the results update when the source data changes.

What “filter unique values” can mean

The phrase can describe two different tasks. You might want a separate list containing each value only once, or you might want to hide duplicate rows in the original table while looking at it. These call for different tools. The UNIQUE and FILTER formula makes a separate result list. A sheet filter changes which existing rows are visible, but it does not create a deduplicated list in another location.

For example, if a column contains “North,” “South,” “North,” and “East,” a unique list contains North, South, and East. It preserves the order of first appearance unless you sort the result separately. If your goal is to keep only records that meet a condition, such as orders marked Active, you can filter the rows first and then return the distinct values from the relevant column.

How the UNIQUE and FILTER formula works

In the opening formula, FILTER selects cells that meet a condition, and UNIQUE removes repeated values from that filtered result. The condition A2:A<>"" means “the corresponding cell in column A is not blank.” It is useful because a range such as A2:A extends down the column and can otherwise contribute empty results.

UNIQUE returns values in the order in which they first occur. It does not sort them alphabetically. This can be helpful when the source order has meaning, such as the sequence in which products or locations were first entered. If you prefer alphabetical order, use the sorted version described below.

The formula’s output is an array: Sheets places the results in the formula cell and the cells below it. You do not need to copy the formula down. Leave enough empty cells below the formula for the list to expand, and avoid placing it inside the source range if that would create a circular reference.

Build a unique list step by step

Identify the source column and its first data row. If the heading is in A1 and values begin in A2, use A2:A as the source range.
Select an empty cell outside the source data, such as C2, where the results can spill downward.
With data in A2:A, enter =UNIQUE(FILTER(A2:A, A2:A<>"")) and press Enter.
Check that the list contains the values you expect. Add a heading such as “Unique values” in C1 if that will make the output clearer.

If the sheet is large, you can use a bounded range instead of a whole-column range. For example, with values in A2:A5000, the formula becomes =UNIQUE(FILTER(A2:A5000, A2:A5000<>"")). Bounded ranges can make a workbook easier to understand and avoid evaluating many unused cells. Keep the source and condition ranges the same size whenever you use FILTER.

Filter unique values using another column

A common variation is to return distinct names, products, or categories only for rows that match a condition. Suppose names are in A2:A and status values are in B2:B. To list each nonblank name whose status is Active, enter =UNIQUE(FILTER(A2:A, B2:B="Active", A2:A<>"")) in an empty cell. FILTER applies both conditions: the status must be Active, and the name must not be blank.

The ranges should correspond row by row. A2:A500 and B2:B500 work together; pairing A2:A500 with B2:B100 would not describe matching records and may produce a range-size error. You can add more conditions by including more condition ranges in FILTER. For instance, with names in A2:A500, status in B2:B500, and region in C2:C500, you could require both Active status and West region: =UNIQUE(FILTER(A2:A500, B2:B500="Active", C2:C500="West", A2:A500<>"")).

Conditions in one FILTER call are generally combined so that each included row must satisfy the supplied conditions. For an “either this or that” condition, such as Active or Pending, use an OR expression in the condition rather than adding the two status tests as separate conditions. With names in A2:A500 and status in B2:B500, an example is =UNIQUE(FILTER(A2:A500, ((B2:B500="Active")+(B2:B500="Pending"))>0, A2:A500<>"")). The addition treats a true test as 1, so a row matching either status passes.

Sort the results or return only one-time values

To sort the unique list, nest the result inside SORT. With source values in A2:A, use =SORT(UNIQUE(FILTER(A2:A, A2:A<>""))). This returns the filtered, deduplicated list in ascending order. Sorting changes the presentation order; it does not change which values qualify as unique.

There is an important distinction between “show each distinct value once” and “show values that occur exactly once in the source.” The ordinary UNIQUE formula does the first: if “North” appears five times, it still appears once in the result. To return only values that appear exactly one time, set UNIQUE’s third argument to TRUE. With values in A2:A, use =UNIQUE(FILTER(A2:A, A2:A<>""), FALSE, TRUE). Here, FALSE specifies that the data is arranged by row, while TRUE requests values that occur only once. This is useful for finding one-off entries, but it is not the usual meaning of a deduplicated list.

Formula list versus a sheet filter or Remove duplicates

Choose the method that fits what you need to do next:

Use UNIQUE with FILTER when you need a live list in a separate area. The result recalculates as source data changes, and you can use it in summaries, dropdown sources, or other formulas.
Use a regular filter when you want to inspect or temporarily hide rows in the original table. Select the data, choose Data > Create a filter, then use a column’s filter icon and its value options. This hides rows from view; duplicate records remain in the sheet.
Use a filter view when you want a saved view of filtered rows without changing how other collaborators see the sheet. In Google Sheets, look under Data > Filter views to create or manage filter views.
Use Remove duplicates when you intend to delete duplicate records from the data itself. The command is under Data > Data cleanup > Remove duplicates. Review the selected range and column choices carefully first, because removing duplicates changes the source data rather than generating a separate result.

For a quick one-time list, copying a column and using Remove duplicates may seem convenient, but do it on a copy if the original records matter. A formula is usually the safer option when the source should remain intact or the list needs to stay current.

Common problems and how to fix them

If FILTER finds no rows that satisfy its conditions, Sheets returns a no-match error rather than an empty list. If that is an expected possibility, you can provide a more helpful message. With data in A2:A100, enter =IFNA(UNIQUE(FILTER(A2:A100, A2:A100<>"")), "No matches"). IFNA replaces the no-match result with the text shown, though it can also catch another #N/A error in the evaluated expression.

An array expansion message usually means the output cannot occupy the cells it needs. Clear any existing values beneath the formula, unmerge obstructing cells, and check that the result is not blocked by another formula or content. Also make sure the formula is not placed where its output would overwrite the source data.

Blank-looking cells are not always truly blank. A cell containing spaces, or a formula that returns a space, is not the same as an empty cell and can remain in the output. If entries may contain extra spaces, clean the source data or use a separate cleanup step; do not assume the nonblank test removes whitespace. Similarly, inconsistent spelling such as “East ” and “East” can lead to results that look like duplicates to a person but are different text entries. Trimming and standardizing source values can prevent that.

When FILTER reports a range-size problem, verify that every condition range has the same number of rows as the range being filtered. For example, filtering A2:A100 against B2:B99 is mismatched. If your data has a header row, keep it out of the ranges unless you deliberately want the heading included.

Google Sheets and Excel are not identical

Google Sheets supports both UNIQUE and FILTER, so the combined formula works directly in a browser-based Sheets spreadsheet. Excel for Microsoft 365 also has functions named UNIQUE and FILTER, but formulas and behavior can differ by application, version, and regional settings. Instructions for Excel menus should not be assumed to match the Google Sheets menus above.

In Google Sheets, some spreadsheet locales use semicolons instead of commas to separate formula arguments. If a formula copied from another locale produces a parse error, check the spreadsheet’s locale settings and replace argument-separating commas with the separator used by that sheet. Do not replace commas that are part of text values. Also confirm that the formula begins with an equals sign and that quotation marks around text criteria are straight quotes.

Frequently Asked Questions

How do I filter unique values in Google Sheets without blanks?
With source values in A2:A, use =UNIQUE(FILTER(A2:A, A2:A<>"")). FILTER excludes blank cells before UNIQUE removes repeated values.

Can Google Sheets show unique values in alphabetical order?
Yes. With the source list in A2:A, use =SORT(UNIQUE(FILTER(A2:A, A2:A<>""))). SORT orders the resulting list while UNIQUE removes duplicates.

How do I get unique values based on a status?
If names are in A2:A and statuses are in B2:B, use =UNIQUE(FILTER(A2:A, B2:B="Active", A2:A<>"")) to return nonblank names from rows marked Active.

Does Google Sheets UNIQUE delete duplicates from my data?
No. UNIQUE returns a formula result in a separate output area and leaves the source data unchanged. To delete duplicate rows, use Data > Data cleanup > Remove duplicates, after checking the selected range.

Why does my unique formula show an error?
Common causes include no rows matching FILTER, mismatched condition-range sizes, and cells blocking the output array. Check the criteria and ranges, and clear space below the formula. If no matches are normal, wrap the expression in IFNA to show a message instead.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.