ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
2
⚡ Dynamic Arrays Complete Guide — Companion Workbook
3
All 15 dynamic array functions — sheetsandcells.com/guides/dynamic-arrays-complete-guide/
4
5
What's inside
6
Function ReferenceAll 15 dynamic array functions grouped by category — Filter/Sort, Generation, Reshape. Syntax + one canonical example each.
7
Nested Patterns6 real-world combinations of multiple DA functions: sorted unique list, top-N with names, cascading dropdowns, running totals, and more.
8
9
10
⚠ Important compatibility notes
11
• ALL dynamic array functions require Excel 365 or Excel 2021+. They do NOT work in Excel 2019 or older — you get the NAME error.
12
• TAKE, DROP, TOROW, TOCOL, WRAPROWS, WRAPCOLS, CHOOSEROWS, CHOOSECOLS were added in 2022 — need Excel 365 (not 2021 perpetual).
13
• This workbook shows DA formulas as TEXT (with a leading apostrophe) so LibreOffice can render it — DA functions won't evaluate outside real Excel.
14
• Open in Excel 365 to see all formulas evaluate live and see the spill visualization.
15
• Google Sheets supports most DA functions with slightly different syntax (uses ARRAYFORMULA wrapper for some).
16
17
18
Author: Zain Khan · sheetsandcells.com · Aug 2026
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100