Skip to content
Tutorials Center
My learning

Topics

Lesson 47 of 93 ยท Dynamic Arrays

FILTER, SORT, SORTBY and UNIQUE

Combine the FILTER, SORT, SORTBY and UNIQUE functions in Excel to build live reports, dropdown lists and top-N ranking lists that update by themselves.

The four functions in this lesson are the heart of modern Excel reporting. Each does one small job: FILTER keeps rows that meet a condition, SORT and SORTBY put rows in order, and UNIQUE removes repeats. Because each takes an array and returns an array, you can nest them like building blocks and get a report that rebuilds itself whenever the data changes, with no copying, no manual sorting and no pivot refresh.

You met each function on its own in Sorting Data, Filtering Data and Finding and Removing Duplicates. Here you combine them. All of them need Microsoft 365 or Excel 2021 or newer, and they spill (see Dynamic Arrays and Spilling). The formulas are real Excel; the examples run on stand-in functions written in JavaScript and pandas that follow the same rules on small data.

Four building blocks

FILTER Keep the rows that pass a test.
SORT / SORTBY Put rows in order.
UNIQUE Drop repeats.

Each returns an array, so they nest.

1 / 2

The syntax at a glance

TEXT
=FILTER(array, include, [if_empty])
=SORT(array, [sort_index], [sort_order], [by_col])
=SORTBY(array, by_array1, [order1], [by_array2], [order2], ...)
=UNIQUE(array, [by_col], [exactly_once])
=TAKE(array, rows, [columns])

Sample data for the rest of the lesson sits in A2:D9 with headings in row 1: Rep, Region, Product, Sales. Wherever you see Sales[Region] read it as a column of an Excel Table named Sales; with plain ranges, write B2:B9. (Spilled formulas cannot sit inside a table, so put the report on the side or on another sheet.)

Combining them

Nest the functions so the innermost one runs first:

TEXT
=SORT(FILTER(A2:D9, B2:B9="East", "No sales"), 4, -1)

This reads: keep only East rows, and sort what is left by the 4th column (Sales) from largest to smallest. If no row is East, FILTER returns the text No sales, and SORT passes it through unchanged. Always supply the third argument of FILTER when it feeds other functions, because the default empty result, #CALC!, would travel up the whole chain.

Other useful combinations:

TEXT
=SORT(UNIQUE(B2:B9))                          distinct regions, A to Z
=SORTBY(A2:D9, B2:B9, 1, D2:D9, -1)           by region, then sales high to low
=UNIQUE(FILTER(C2:C9, D2:D9 > 100))           distinct products with a big sale
=COUNTA(UNIQUE(FILTER(A2:A9, B2:B9="East")))  how many different East reps
=FILTER(A2:D9, D2:D9 = MAX(D2:D9))            the row(s) with the top sale

Choosing between SORT and SORTBY

SORT identifies the key by position (4 means the 4th column of the array), so the key must be part of the array. SORTBY takes the keys as ranges of their own: they can lie outside the returned block, be computed, and survive inserted columns. Prefer SORTBY once you need more than one key, or a calculated key such as LEN(A2:A9).

JS Stand-in FILTER, SORT, SORTBY and UNIQUE

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Dynamic reports

Put a dropdown in a cell, say G1, and have a formula use it. The report rebuilds when someone picks another region:

TEXT
=SORT(FILTER(A2:D9, B2:B9=G1, "No sales"), 4, -1)

Use UNIQUE to feed the dropdown itself, so new regions appear automatically. In H2:

TEXT
=SORT(UNIQUE(B2:B9))

Then set the dropdown's List source to =$H$2# (see Data Validation and Dropdown Lists). One more tip: to let people pick "All", test the selection inside include:

TEXT
=FILTER(A2:D9, (B2:B9=G1) + (G1="All") > 0, "No sales")

When the choice is All, the second term is TRUE for every row, so every row passes (see AND, OR, NOT and XOR for why adding works as OR).

Ranking lists and top N

A top-5 list is a sort followed by a trim:

TEXT
=TAKE(SORT(A2:D9, 4, -1), 5)                 five biggest sales, all columns
=TAKE(SORT(A2:D9, 4, -1), -3)                three smallest sales
=TAKE(SORTBY(A2:A9, D2:D9, -1), 3)           names of the top 3

TAKE is a newer function (see ROWS, COLUMNS, TAKE, DROP, CHOOSECOLS and Reshaping Data). In versions that lack it, use INDEX(SORT(A2:D9,4,-1), SEQUENCE(5), {1,2,3,4}). Remember that ties are sorted in their original order, so two reps with equal sales appear in the order they have in the data.

To add a rank column, use SEQUENCE(ROWS(...)) beside the list, covered in the next lessons.

JS A report that rebuilds when the choice changes

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Good habits

  • Give FILTER an if_empty whenever it is part of a longer formula.
  • Mind blanks in UNIQUE. Empty cells come back as 0. Filter them out first: =UNIQUE(FILTER(B2:B20, B2:B20<>"")).
  • Use complete ranges or table columns. New rows added below a plain range are not included. A table column grows with the table, so use a table as the source and put the formula outside it.
  • Don't spill into the way of something. Leave space to the right and below, or you will meet #SPILL!.
  • Refer to the result with #. =SUM(F2#) follows the spill; =SUM(F2:F6) will not.
  • Keep an eye on compatibility. Older Excel versions show #NAME?.

The same ideas in pandas

The pandas pipeline reads in the same order as the nested formula, and the result is a new DataFrame:

Python FILTER, SORT, UNIQUE and top-N in pandas

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. In =SORT(FILTER(A2:D9, B2:B9="East"), 4, -1), which part runs first?

    Inner functions are evaluated first. FILTER keeps the East rows, then SORT orders them by column 4 in descending order.
  2. 2. What is a good reason to prefer SORTBY over SORT?

    SORT uses a column position within the array. SORTBY uses real ranges, which can be calculated or located elsewhere.
  3. 3. How do you feed a dropdown with the distinct regions, sorted, so the list grows with the data?

    The formula spills a clean list, and the # reference follows the spill range when it changes.
  4. 4. What does =TAKE(SORT(A2:D9, 4, -1), 5) return?

    SORT puts the largest Sales first, and TAKE keeps the first five rows. A negative row count would take rows from the end.
  5. 5. Why should FILTER usually get a third argument when nested in other functions?

    if_empty supplies a readable fallback, such as "No sales", instead of the #CALC! error.

Summary

  • FILTER keeps rows, SORT/SORTBY order them, UNIQUE drops repeats, and TAKE trims to the top or bottom rows. They nest, innermost first.
  • SORT picks its key by position; SORTBY uses separate ranges and supports several keys.
  • Give FILTER an if_empty value, and filter blanks out before UNIQUE.
  • Combine UNIQUE with a spill reference ($H$2#) to build dropdown lists that update by themselves.
  • A top-N list is TAKE(SORT(...), N); ties keep their original order.
  • These functions need Microsoft 365 or Excel 2021+; pandas offers sort_values, unique, boolean masks and nlargest.

Next: SEQUENCE, RANDARRAY and Array Arithmetic.