FILTER, SORT and UNIQUE in Google Sheets and Excel

The short answer

FILTER, SORT and UNIQUE are dynamic array formulas. You type the formula in one cell and the result "spills" across as many rows and columns as needed. When new rows appear in the source table, the report recalculates automatically.

• FILTER keeps rows that meet your conditions.
• SORT orders a range by one or more columns.
• UNIQUE returns a list without duplicates.

Google Sheets has had these functions for a long time; in Excel they are available in Microsoft 365 and Excel 2021 or later. Depending on your regional settings, arguments may be separated by semicolons instead of commas.

All examples below use an orders sheet: A is date, B client, C city, D manager, E amount, F status.

FILTER: a live report by condition

All paid orders:

=FILTER(A2:F100, F2:F100="Paid")

Several AND conditions. Multiply the conditions together; this works in both Excel and Google Sheets. The minimum amount sits in cell I1:

=FILTER(A2:F100, (F2:F100="Paid")*(E2:E100>=I1))

In Google Sheets you can also list conditions as separate arguments: =FILTER(A2:F, F2:F="Paid", E2:E>=I1).

OR conditions. Add the conditions:

=FILTER(A2:F100, (C2:C100="Tashkent")+(C2:C100="Samarkand"))

Empty result. If nothing matches, Excel returns #CALC! and Google Sheets returns #N/A. In Excel use the third argument; in Sheets use IFNA:

Excel:   =FILTER(A2:F100, F2:F100="Paid", "No data")
Sheets:  =IFNA(FILTER(A2:F, F2:F="Paid"), "No data")

To let a manager choose the condition, reference a cell with a dropdown instead of hardcoded text.

SORT: order without manual sorting

The syntax differs: Google Sheets sets the direction with TRUE or FALSE, Excel with 1 or -1.

Sheets:  =SORT(A2:F100, 5, FALSE)
Excel:   =SORT(A2:F100, 5, -1)

Both formulas sort orders by amount (the fifth column of the range) in descending order.

Sorting by several columns: city alphabetically first, then amount descending:

Sheets:  =SORT(A2:F100, 3, TRUE, 5, FALSE)
Excel:   =SORTBY(A2:F100, C2:C100, 1, E2:E100, -1)

For multiple keys in Excel, SORTBY is the clearer option: after the range come pairs of "sort column, direction".

UNIQUE: a client list without duplicates

=UNIQUE(B2:B100)

If the range contains blank cells, the result will include an empty value (shown as 0 in Excel). Remove blanks with a filter:

=UNIQUE(FILTER(B2:B100, B2:B100<>""))

The number of unique clients is =COUNTA(UNIQUE(FILTER(B2:B100, B2:B100<>""))).

Setting the third UNIQUE argument to TRUE keeps only values that appear exactly once, for example clients with a single order:

=UNIQUE(B2:B100, FALSE, TRUE)

Combining the functions

A sorted list of clients from Tashkent who have paid orders:

=SORT(UNIQUE(FILTER(B2:B100, (C2:C100="Tashkent")*(F2:F100="Paid"))))

Read the formula from the inside out: filter, remove duplicates, sort.

A "client and total" summary in Excel: put the client list in H2 with =SORT(UNIQUE(B2:B100)), and the total per client in I2 with =SUMIF(B2:B100, H2#, E2:E100). The # sign refers to the whole spilled range, so the summary grows with the list.

Common mistakes

• #SPILL! in Excel or #REF! in Google Sheets: the cells where the result should spill are occupied. Clear the space below the formula.
• Mismatched range sizes. The data range and condition ranges must start and end on the same rows.
• Numbers stored as text. The condition E2:E100>=I1 fails on text amounts, so convert the data to numbers first.
• Manual edits inside the result. Do not type anything inside a spilled range; it breaks the formula.
• Whole columns in Excel. References like B:B work but slow the workbook down; format the data as a table instead.