All posts
Priya Nair 3 min readFormulasOnly E2 holds a formula. The blue cells are its spill range. E2 holds =UNIQUE(B2:B6); F2 counts each department. Practise this 
FILTER, SORT and UNIQUE: dynamic arrays explained
One formula, many results. Dynamic arrays spill answers into neighbouring cells and replace a lot of copy-paste work and helper columns.
One formula, many cells
FILTER: a live, formula-driven filter
Type one formula and the matching rows spill down and across. Change the source data and the result updates. No AutoFilter clicks needed.
FILTER by department
E2fx=FILTER(A2:C6,B2:B6="Sales")
A
B
C
D
E
F
G
1
Name
Dept
Sales
Name
Dept
Sales
2
Asha
Sales
1,200
Asha
Sales
1,200
3
Ben
Ops
800
Chen
Sales
1,500
4
Chen
Sales
1,500
Eli
Sales
900
5
Dana
HR
600
6
Eli
Sales
900
SORT the result
Wrap the FILTER in SORT(array, sort_index, order). Here we sort by the 3rd column, largest first (-1).
SORT + FILTER
E2fx=SORT(FILTER(A2:C6,B2:B6="Sales"),3,-1)
A
B
C
D
E
F
G
1
Name
Dept
Sales
Name
Dept
Sales
2
Asha
Sales
1,200
Chen
Sales
1,500
3
Ben
Ops
800
Asha
Sales
1,200
4
Chen
Sales
1,500
Eli
Sales
900
5
Dana
HR
600
6
Eli
Sales
900
UNIQUE and the # spill reference
UNIQUE lists each value once. Refer to a whole spill range by adding # to its first cell (E2#), so formulas beside it grow and shrink with it.
UNIQUE with a count
F2fx=COUNTIF(B2:B6,E2#)
A
B
C
D
E
F
1
Name
Dept
Sales
Dept
People
2
Asha
Sales
1,200
Sales
3
3
Ben
Ops
800
Ops
1
4
Chen
Sales
1,500
HR
1
5
Dana
HR
600
6
Eli
Sales
900
Try it in a live spreadsheet
Practise these formulas in interactive lessons with instant grading.
Priya Nair
Lead instructor at FluentExcel