Skip to content
Educora
EN
AzərbaycancaEnglishРусскийTürkçe
My learningEducora StudioSettings
Advanced22 min16 / 27

Dynamic arrays: FILTER, SORT, UNIQUE, LET, LAMBDA

Modern Excel, where one formula returns a whole table: spilling, FILTER, SORT, SORTBY, UNIQUE, SEQUENCE, plus LET and LAMBDA for building your own functions.

Check yourself
In this lesson you will learn
  • Understand the spill range, the # reference and the #SPILL! error
  • Build dynamic reports with FILTER, SORT, SORTBY and UNIQUE
  • Generate series with SEQUENCE and simplify formulas with LET
  • Create a simple named function with LAMBDA

For many years Excel had a simple rule: one formula — one cell. A task like “show all Baku orders in a separate list” needed a filter, copy-and-paste or awkward array formulas. Microsoft 365 has dynamic arrays: you write one formula, the result spills into as many cells as it needs, and it updates when the source changes.

Throughout this lesson we use one sales table. A1:D9 has the headers Seller, Region, Product, Amount and 8 orders: Aysel (Baku, Laptop, 1200), Murad (Ganja, Phone, 650), Leyla (Baku, Phone, 700), Elvin (Sumgait, Laptop, 1100), Nigar (Baku, Tablet, 480), Rauf (Ganja, Laptop, 1250), Aysel (Baku, Tablet, 520), Murad (Ganja, Laptop, 1150). Amounts are in manat.

Spilling and the `#` operator

Definition
Spill range

The cells occupied by the result of a dynamic array formula. The formula lives only in the first cell; the other cells are shown inside a blue border and cannot be edited by hand.

  • Type =UNIQUE(B2:B9) in F2 — Baku, Ganja and Sumgait appear in F2:F4.
  • F2# refers to the whole spill range: =COUNTA(F2#) → 3. If a new region appears, F2# grows with it.
  • Type =SUMIF(B2:B9,F2#,D2:D9) in G2 — one formula gives three totals: 2900, 3050, 1100.
  • If you see @ in front of a formula from an old workbook, it means “take a single value” (implicit intersection).

Filtering, sorting, de-duplicating: FILTER, SORT, SORTBY, UNIQUE

FormulaResult (spills)
=FILTER(A2:D9,B2:B9="Baku")Baku's 4 orders: Aysel 1200, Leyla 700, Nigar 480, Aysel 520
=FILTER(A2:D9,B2:B9="Shaki","No orders")“No orders” (without the 3rd argument — #CALC!)
=SORT(A2:D9,4,-1)the whole table by amount, descending: Rauf 1250, Aysel 1200, Murad 1150…
=SORTBY(A2:A9,D2:D9,-1)only the names, but sorted by amount (the sort column is not in the result)
=UNIQUE(A2:A9)6 sellers: Aysel, Murad, Leyla, Elvin, Nigar, Rauf
=UNIQUE(A2:A9,,TRUE)only those that appear exactly once: Leyla, Elvin, Nigar, Rauf
=SORT(UNIQUE(C2:C9))Laptop, Phone, Tablet
=TAKE(SORT(A2:D9,4,-1),3)the 3 largest orders (a top 3)
Several conditions in FILTER

1) Show Baku orders larger than ₼600. 2) Show all Ganja orders or tablet orders from any region.

Show solution
AND and OR do not work inside FILTER, because they return a single TRUE/FALSE for the whole array. Instead, multiply and add the arrays: TRUE = 1, FALSE = 0.
1) AND = multiply: =FILTER(A2:D9,(B2:B9="Baku")*(D2:D9>600)) → Aysel 1200 and Leyla 700 (2 rows).
2) OR = add: =FILTER(A2:D9,(B2:B9="Ganja")+(C2:C9="Tablet")) → Murad 650, Nigar 480, Rauf 1250, Aysel 520, Murad 1150 (5 rows).
Each bracket gives an 8-item array of 1s and 0s; rows whose result is not 0 are kept.
Interactive
Loading simulation…
Sales by region: the UNIQUE + COUNTIF/SUMIF pattern (the practice sheet does not support dynamic arrays, so the list is typed by hand).

SEQUENCE and LET

=SEQUENCE(rows, [columns], [start], [step])
where:
  • rows, columnshow many rows and columns to fill (columns defaults to 1)
  • start, stepthe first value and the step (both default to 1)
  • =SEQUENCE(5) → 1, 2, 3, 4, 5 (for row numbers).
  • =SEQUENCE(3,4) → a 3 × 4 block from 1 to 12.
  • =SEQUENCE(7,1,DATE(2026,9,28)) → one week of dates starting on 28 Sep 2026 (format the cells as dates).
  • =SEQUENCE(10,1,10,-1) → a countdown from 10 to 1.

LET lets you create variables inside a formula: =LET(name1, value1, name2, value2, …, result). If the same piece appears several times in a formula, it is calculated only once, and the formula becomes readable.

Baku's average order with LET

Find the average amount of the Baku orders, using LET instead of writing FILTER twice.

Show solution
Without LET: =SUM(FILTER(D2:D9,B2:B9="Baku"))/COUNT(FILTER(D2:D9,B2:B9="Baku")) — FILTER is calculated twice.
With LET: =LET(baku,FILTER(D2:D9,B2:B9="Baku"),SUM(baku)/COUNT(baku)).
baku = {1200; 700; 480; 520}, the sum is 2900 and the count is 4.
Result: 2900 / 4 = ₼725.
Hint: with Alt+Enter you can put each LET pair on its own line in the formula bar.

LAMBDA: build your own function

  1. 1
    Test the formula in a cell

    Type =LAMBDA(price,vat,price*(1+vat))(100,0.18) — with the arguments in the final brackets the result is 118 (a price with 18% VAT).

  2. 2
    Give it a name

    Open Formulas › Defined Names › Name Manager › New. Name: WITHVAT, Refers to: =LAMBDA(price,vat,price*(1+vat)). Click OK.

  3. 3
    Use it like any function

    Now you can write =WITHVAT(D2,18%) anywhere in the workbook. If the logic changes, you fix it once in Name Manager.

New line in the formula bar (makes long LET formulas readable)Alt+Enter
Expand or collapse the formula barCtrl+Shift+U
Legacy array formula (only needed in Excel 2019 and earlier)Ctrl+Shift+Enter
Open Name Manager (LAMBDA names live here)Ctrl+F3

Key points

  • A dynamic array formula is written in one cell and spills into its neighbours; refer to the whole result with F2#.
  • #SPILL! means something is in the way, cells are merged or the formula is inside an Excel Table.
  • In FILTER, AND is written with * and OR with +; the 3rd argument handles an empty result.
  • SORT sorts by a column number, SORTBY by another range; UNIQUE removes duplicates.
  • LET creates variables; LAMBDA plus Name Manager lets you create your own named function.

Check yourself

10 questions. Every correct answer earns XP.

1 / 10
How do you refer to the whole result of =UNIQUE(B2:B9) in F2?