FILTER, UNIQUE, SORT and SORTBY

Data OrganizationAnalyticsDecision SupportModelling & Forecasting9 min readMicrosoft 365 for Windows

The problem

A table of net sales by market, product and channel can be asked many questions. Which rows belong to the UK hospital channel? Which markets appear in the file? Which rows are the three largest? Until recently each question meant a tool of its own: an AutoFilter that hides rows but changes the sheet, an Advanced Filter that must be run again after every change, a helper column, or a PivotTable that has to be refreshed.

Four functions answer the same questions with formulas that stay alive. FILTER extracts the rows that meet a condition, UNIQUE returns the distinct values, SORT and SORTBY return the data in another order. None of them modifies the source. Each produces a view of the data that updates when the source or a condition changes. They are dynamic array functions, so the result spills into the cells next to the formula, as described in the article on dynamic arrays. They are available in Microsoft 365, Excel 2021 and Excel 2024.

The examples use the companion file F16_Filter_Unique_Sort.xlsx. The Data sheet holds 24 rows of illustrative net sales, in thousands, in A1:D25, for three products in six markets and two channels.

FILTER

FILTER returns the rows of a range for which a test is true:

=FILTER(array, include, [if_empty])

The array is the range to return. Include is a Boolean array, a column of TRUE and FALSE values with the same height as the array. If_empty is the value to show when no row matches. On the FILTER sheet the conditions are driven by five yellow cells: the market in B1, the channel in B2, a second market in B3, a minimum net sales figure in B4 and a market without data in B5. The first block extracts the rows of one market:

=FILTER(Data!$A$2:$D$25,Data!$A$2:$A$25=$B$1,"No rows")

The test Data!$A$2:$A$25=$B$1 compares every market in the column with the value in B1 and returns 24 values, TRUE for the UK rows. FILTER keeps those rows. With B1 equal to UK the block has five rows and four columns, starting in A9. Type US in B1 and the block rebuilds itself on the five US rows. The comparison ignores upper and lower case.

Two cells next to the inputs read the result through the spill operator: =ROWS(A9#) returns 5 and =SUM(INDEX(A9#,0,4)) returns 1,028, the net sales of the UK. INDEX with a row number of 0 returns the whole of column 4.

B1 says UK, so the UK rows spill from the FILTER formula in A9; type another market and the block rebuilds itself.

More than one condition

AND and OR are written with arithmetic, as described for array logic. Multiplication means AND, since both tests must be TRUE. Addition means OR, since either one is enough. The second block asks for the UK and the hospital channel:

=FILTER(Data!$A$2:$D$25,(Data!$A$2:$A$25=$B$1)*(Data!$C$2:$C$25=$B$2),"No rows")

It returns three rows. The third block mixes the two operators: rows of the UK or of Germany, with net sales of at least 100:

=FILTER(Data!$A$2:$D$25,((Data!$A$2:$A$25=$B$1)+(Data!$A$2:$A$25=$B$3))*(Data!$D$2:$D$25>=$B$4),"No rows")

The addition in the inner parentheses is the OR between the two markets, and the multiplication outside it applies the threshold to both. Six rows remain: the three rows of each market that reach the threshold. Without the inner parentheses the multiplication would be done first, and the result would be a different, wrong, condition. This is the commonest source of unexpected rows.

Nothing found

The fourth block uses the market JP, which is not in the data. FILTER cannot return an empty array, so without the third argument the cell would show #CALC!. Microsoft's documentation says that Excel does not currently support empty arrays. The third argument replaces that error with a text, here "No rows", or with "" for a blank. Always give it a value when the condition comes from a cell that people change.

UNIQUE

UNIQUE returns the distinct items of a range:

=UNIQUE(array, [by_col], [exactly_once])

By default it compares rows and returns the first occurrence of each. With by_col set to TRUE it compares columns. With exactly_once set to TRUE it returns only the items that occur a single time, which is not the same as removing duplicates: an item that occurs twice disappears altogether. The UNIQUE sheet shows the three forms.

=UNIQUE(Data!$B$2:$C$25)              returns the five product and channel pairs
=UNIQUE(Data!$A$2:$A$25,,TRUE)        returns ES, the only market with a single row

The first formula works on two columns, so a pair counts as one item, and the list is the combination of product and channel in the order in which each pair first appears. The two commas skip by_col in the second.

The most common use is a list of dimension members. Combined with SORT it gives a sorted list of markets:

=SORT(UNIQUE(Data!$A$2:$A$25))

which returns DE, ES, FR, IT, UK and US. =ROWS(A2#) counts them. The list is not maintained by hand: when a new market appears in the data, it appears in the list. That list can feed other formulas. In J2, SUMIFS takes the whole spill as its criteria and returns one total for each market:

=SUMIFS(Data!$D$2:$D$25,Data!$A$2:$A$25,A2#)

The results are 1,295 for DE, 318 for ES, 824 for FR, 670 for IT, 1,028 for UK and 2,287 for US, in the same order as the list. A small summary table that builds its own row labels and its own totals is two formulas, and it never needs a refresh.

SORT and SORTBY

SORT orders an array by a column or row of that array:

=SORT(array, [sort_index], [sort_order], [by_col])

The sort index is the number of the column to sort on, the default being the first. The sort order is 1 for ascending, the default, and -1 for descending. By_col is FALSE for rows, the default. On the SORT and SORTBY sheet:

=SORT(Data!$A$2:$D$25,4,-1)

returns the whole table in descending order of net sales, the fourth column. The first row is the US, Oncavir, hospital, 1,240.

SORTBY sorts one array by another range, so the sort keys do not have to be part of the result:

=SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2], ...)

With two keys:

=SORTBY(Data!$A$2:$D$25,Data!$A$2:$A$25,1,Data!$D$2:$D$25,-1)

the table is ordered by market in ascending order and, within each market, by net sales in descending order. The first row is DE, Oncavir, hospital, 610. All the sort ranges must have the same number of rows as the array, and a sort order other than 1 or -1 returns #VALUE!.

Microsoft recommends SORTBY for data in the grid. The reason is practical: SORT refers to a column by number, so inserting a column in the source silently changes what is sorted. SORTBY refers to a range, which moves with the data. The two functions differ in that SORT sorts the array by one of its own columns, while SORTBY sorts by any range of the right size, even one that is not in the output.

The source is never altered. The original table remains in the order in which it was entered, available for other purposes, and every sorted view is a formula. This is a declarative way of building a report: the data sit in one place and each report is a different formula over them.

Two keys in one formula: markets A to Z, and within each market the largest net sales first.

The N largest: TAKE

A ranking is a sort followed by a cut. TAKE keeps the first rows of an array:

=TAKE(array, rows, [columns])

A negative number counts from the end. The Top N sheet reads N from B1 (3):

=TAKE(SORTBY(Data!$A$2:$D$25,Data!$D$2:$D$25,-1),$B$1)

Formulas are read from the inside out. SORTBY orders the table by net sales in descending order and TAKE keeps the first three rows: Oncavir in the US with 1,240, Oncavir in Germany with 610 and Oncavir in the UK with 530. Change B1 to 5 and the ranking has five rows.

TAKE is available in Microsoft 365 and Excel 2024 but not in Excel 2021. Where it is missing, INDEX with SEQUENCE does the same:

=INDEX(SORTBY(Data!$A$2:$D$25,Data!$D$2:$D$25,-1),SEQUENCE($B$1),{1,2,3,4})

SEQUENCE supplies the row numbers 1 to N, and the array constant lists the four columns to return.

The last block of the sheet nests the three functions: the largest rows within the market in B2.

=TAKE(SORTBY(FILTER(Data!$A$2:$D$25,Data!$A$2:$A$25=$B$2),FILTER(Data!$D$2:$D$25,Data!$A$2:$A$25=$B$2),-1),$B$1)

For the US, the top three are Oncavir with 1,240, Velorin retail with 482 and Cardexa retail with 351. The second FILTER supplies the sort key for the same rows as the first. The formula is long, and a later article on LET shows how to give the filtered rows a name and write them once. Remember also what a cut does with ties: if the third and fourth rows have the same value, TAKE returns only one of them.

One formula combines FILTER, SORTBY and TAKE to rank the US rows; N is read from B1.

Working with a table as the source

In the companion file the source is a plain range, so the references are fixed at row 25. If you add a row in row 26, the formulas do not see it. For real data, convert the source to an Excel table: the references to its columns grow with the table, and so do the results of the formulas. A table can be the source, but the formulas must be placed outside it, since spilled arrays are not supported inside tables.

Common mistakes

Forgetting the third argument of FILTER and getting #CALC! when no row matches.
Writing OR with a multiplication or AND with an addition. The first removes everything, the second adds rows you did not want.
Omitting the inner parentheses when mixing AND and OR.
Using an include array of a different height from the array. The function returns an error.
Placing the formula where the spill range is blocked, or inside a table, and getting #SPILL!.
Using SORT with a column number and then inserting a column in the source.
Expecting UNIQUE with exactly_once to behave like the Remove Duplicates command. It returns only the items that occur once.
Taking the top N from a sort and forgetting about ties at the cut.
Counting on TAKE in a workbook that will be opened in Excel 2021.