ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
ARRAYFORMULA Function Pillar Guide — Companion Workbook
2
3
About this workbook
4
This workbook contains sample datasets for the ARRAYFORMULA function in Google Sheets.
5
ARRAYFORMULA is native to Google Sheets — Excel uses dynamic arrays with different syntax.
6
To use this workbook:
7
1. Save this file to Google Drive
8
2. Right-click → Open with → Google Sheets
9
3. Copy any ARRAYFORMULA from the pillar guide and paste into a blank cell
10
4. Watch a single formula spill across an entire column
11
12
Sheets in this workbook
13
14
Sheet NameDescriptionSize
15
Employees
Employee master data — First/Last names, Salary, Bonus %, Tax Rate, Hire Date
30 rows
16
DepartmentsDepartment lookup table for ARRAYFORMULA+VLOOKUP patterns8 rows
17
Sales_RowsSimple sales transactions for Quantity × Price column patterns25 rows
18
ArrayFormula_Reference20+ ready-to-paste ARRAYFORMULA examplesreference
19
20
About ARRAYFORMULA
21
ARRAYFORMULA applies a formula to an entire range instead of a single cell.
22
One formula in one cell produces results across many rows or columns.
23
Syntax: =ARRAYFORMULA(formula_that_operates_on_ranges)
24
Shortcut: Type your formula and press Ctrl+Shift+Enter to auto-wrap in ARRAYFORMULA.
25
26
Note on ArrayFormula_Reference sheet
27
Formulas in the reference sheet have a leading space (' =ARRAYFORMULA(...)') to prevent
28
Excel from parsing them as broken formulas. When copying to Google Sheets, remove
29
the leading space. Or copy directly from the pillar guide's copy buttons.
30
31
Learn more
32
Full guide: https://sheetsandcells.com/sheets/guides/arrayformula-complete-guide/
33
34
Built by Zain Khan · Rawalpindi, Pakistan
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