Filter Unique Values in Google Sheets With a Formula

If the values are in A2:A and their statuses are in B2:B, enter =UNIQUE(FILTER(A2:A7,B2:B7="Paid",A2:A7<>"")) to return each nonblank value marked Paid once. In Google Sheets, FILTER selects matching data and UNIQUE removes repeated results; together, they create a live list of distinct values that meet your criteria.

What “filter unique” means in Google Sheets

The phrase can describe two different jobs. You might want to filter a list so it shows only rows that meet a condition, or you might want a list of the distinct values that meet that condition. Google Sheets’ FILTER function handles the first job; UNIQUE handles duplicates. Combining them does both.

For example, imagine A2:A7 contains Maya, Leo, Maya, Ana, Leo, and Ana, while B2:B7 contains Paid, Due, Paid, Paid, Paid, and Due. The formula in the opening paragraph returns Maya, Ana, and Leo, in the order each first appears among the matching rows. It excludes the unpaid entries and the blank cells. The displayed list is a formula result, not a change to the source data.

This distinction matters because a regular filter can hide rows without removing duplicates. Conversely, UNIQUE by itself can remove duplicates but does not decide which records qualify, such as paid orders or entries from a particular region. Use both functions when you need a distinct list from a filtered subset.

How FILTER and UNIQUE work together

FILTER takes a range and one or more conditions, then returns the rows that satisfy them. The condition range needs to line up with the data range: if you filter A2:A7 using statuses in B2:B7, both ranges cover the same six rows. A mismatched range size is a common reason for an error.

UNIQUE receives that filtered result and keeps one instance of each distinct value or row. In the opening example, FILTER first selects the four Paid entries. UNIQUE then reduces those matches to Maya, Ana, and Leo. The order is based on the first qualifying occurrence, not an alphabetical sort.

For more than one condition, FILTER can take additional condition arguments. Every condition must be true for a row to be included. You can also combine conditions in an expression: multiplication represents AND, while addition can represent OR. Keep condition ranges aligned with the data range, and use parentheses to make the intended logic clear.

Filter by a different condition

Replace the example condition with the test that matches your sheet. A condition can check text, numbers, dates, or whether a cell is nonblank. For example, you might select customers with an Open status, amounts above a threshold, or dates in a chosen period, then pass the matching results to UNIQUE.

For a simple text condition, use an exact comparison such as a status cell equaling “Open.” For a numerical threshold, use a comparison such as greater than or equal to a value. If the criterion itself is stored in a cell, refer to that cell rather than typing the criterion into the formula; this makes it easier to change the selection later. Remember that text spelling, extra spaces, and inconsistent data entry can affect which records match.

If you want distinct results only when they occur exactly once in the filtered data, that is a different request from removing duplicates. With the same A2:A7 names and B2:B7 statuses from the opening example, enter =UNIQUE(FILTER(A2:A7,B2:B7="Paid",A2:A7<>""),FALSE,TRUE). This returns Ana and Leo: Maya appears twice among the paid names, so the exactly-once option excludes her. The third argument is TRUE for that purpose; the default behavior is to return one copy of every distinct value, including values that appeared multiple times.

Return unique records, not just unique values

Sometimes the goal is to produce distinct complete records rather than a list of names. UNIQUE compares rows across the range you give it. If any included column differs, the rows are different records, even when one column, such as Customer, repeats.

Suppose A2:C6 contains these Customer, Item, and Region records: Mina–Tea–East, Noah–Coffee–West, Mina–Tea–East, Mina–Coffee–East, and Noah–Coffee–West. D2:D6 contains the corresponding statuses Open, Open, Closed, Open, and Open. With those columns and rows, enter =UNIQUE(FILTER(A2:C6,D2:D6="Open")). It returns three rows: Mina–Tea–East, Noah–Coffee–West, and Mina–Coffee–East. The duplicate open records appear once, while the closed Mina–Tea–East row does not affect the result.

Choose the range carefully. If you include a timestamp or a unique order number in the range, otherwise similar records may no longer count as duplicates. If you want one row per customer regardless of item or region, filter and return only the customer column instead of asking UNIQUE to compare all three columns.

Sort the unique results

UNIQUE preserves the order of first appearance; it does not sort the results alphabetically. To return an alphabetized list, wrap the combined formula in SORT. With names in A2:A7 and statuses in B2:B7 as in the first example, enter =SORT(UNIQUE(FILTER(A2:A7,B2:B7="Paid",A2:A7<>""))). The result is Ana, Leo, Maya. SORT changes the display order of the formula output, not the order of the source rows.

For a multi-column result, SORT can order the rows by a chosen column. This is helpful for a distinct list of records that should be arranged by customer, date, or another field. Check the column index against the range supplied to SORT: it refers to a column within that range, not necessarily the same numbered column on the sheet.

Where the results appear and how to use them

Google Sheets expands an array formula into the cells below it, or across them if the result has multiple columns. Put the formula in one cell, usually outside the source data, and leave enough empty space for its output. The result updates as matching source values change, so it can serve as a live list for a report or summary.

Keep the formula output area clear. Existing text, merged cells, or other content can prevent the result from expanding.
Consider using bounded ranges such as A2:A1000 instead of whole-column references when a sheet is large or calculations become slow.
Use a separate output tab if you want to keep the source table and the generated list visually distinct.
When sharing the sheet, remember that people with permission to edit the source data can change the formula’s results.

A formula-generated list is different from copied values: it recalculates when its inputs change. If you need a one-time snapshot, copy the result and use Edit > Paste special > Values only to paste fixed values. That pasted list will not update when the source changes.

Formula versus the built-in filter tools

For a temporary view of source rows, select the data and use Data > Create a filter. The filter controls can hide rows that do not meet a condition, and filtering by values lets you select which values to display. This is useful for inspecting or working with the original table, but it does not create a separate de-duplicated list. Repeated matching rows can remain visible.

For a reusable list of distinct matches, the FILTER-plus-UNIQUE formula is generally the better fit. If you actually want to delete duplicate rows from the source, Google Sheets provides Data > Data cleanup > Remove duplicates. That operation changes the source data, so review the selected columns and make a copy before using it. A formula is safer when you want to preserve every original record.

Excel also has functions named FILTER and UNIQUE in current versions, but its formula behavior and interface are not identical in every version. This page uses Google Sheets in a web browser; follow Google Sheets’ menu paths and syntax here rather than assuming that an Excel menu or older Excel version works the same way.

Common errors and practical fixes

If FILTER finds no matching rows, Google Sheets can return a #N/A error. You can display a helpful message instead. With names in A2:A7 and statuses in B2:B7, and assuming no row has a Refunded status, enter =IFNA(UNIQUE(FILTER(A2:A7,B2:B7="Refunded",A2:A7<>"")),"No matches"). The displayed result is No matches. IFNA catches the no-match result; it does not fix unrelated problems such as mismatched range sizes.

A #REF! message can mean that the array result cannot expand because cells in its destination area are occupied. Clear those cells or move the formula to a larger empty area. If the formula returns unexpected duplicates, check whether the values only look identical: leading or trailing spaces, different spellings, or numbers stored as text can make entries distinct to the sheet.

In some spreadsheet locales, formulas use semicolons rather than commas between arguments. If a formula copied from another sheet is rejected, check the spreadsheet’s locale settings and adapt the separators if needed. Do not remove quotation marks around text criteria; they tell the formula that the criterion is text.

Frequently Asked Questions

How do I filter unique values in Google Sheets?
Use UNIQUE around FILTER, such as =UNIQUE(FILTER(A2:A,B2:B="Paid")), changing the ranges and condition to match your data. FILTER chooses qualifying rows, and UNIQUE removes repeated results.

How do I get unique values without a condition?
Use =UNIQUE(A2:A) when the values are in A2:A and you want one copy of each distinct entry. It returns a live result and preserves first-appearance order; wrap it in SORT if you want an ordered list.

Does Google Sheets FILTER remove duplicates?
No. FILTER returns every row that meets its condition, including repeated rows. Add UNIQUE around the FILTER result when you want distinct values or distinct complete rows.

How can I return values that appear only once?
Use UNIQUE’s exactly-once option, set to TRUE as its third argument, on the filtered range. This excludes values that occur more than once in that filtered result; it is not the same as returning one copy of every distinct value.

Why does my formula show #N/A?
FILTER returns #N/A when no rows match its conditions. Check the criteria and ranges, or wrap the result in IFNA to show a message such as “No matches.”

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.