Syntax
EXPAND(array, rows, [columns], [pad_with])
| Parameter | Description |
|---|---|
| array | Parameter of the EXPAND function. |
| rows | Parameter of the EXPAND function. |
| [columns] | (Optional.) Parameter of the EXPAND function. |
| [pad_with] | (Optional.) Parameter of the EXPAND function. |
Examples
Pad a 2x2 to 5x5 with zeros
Formula
=EXPAND(A1:B2, 5, 5, 0)
Takes the 2x2 block and creates a 5x5 array. The original values are in the top-left; all new cells contain 0.
Expand rows only
Formula
=EXPAND(A1:C3, 10, 3, "")
Extends the 3-row table to 10 rows, padding new rows with empty strings. Column count stays at 3.
Default pad value
Formula
=EXPAND(A1:B2, 4, 4)
Creates a 4x4 array. New cells show #N/A (the default pad value) because no pad_with argument was specified.
Common Errors
#VALUE!
The target rows or columns is less than the current dimensions of the source array — EXPAND can only grow, not shrink.
#SPILL!
The expanded array would overlap existing non-empty cells.
Tips
Use before HSTACK/VSTACK
When stacking arrays of different sizes, EXPAND them to matching dimensions first. This prevents misalignment errors.
Pad with meaningful values
Set pad_with to 0 for numeric tables, "" for text, or a specific default value. The default #N/A padding will cause errors in SUM, AVERAGE, etc.
Cannot shrink
EXPAND only makes arrays larger. To reduce dimensions, use TAKE, DROP, or CHOOSEROWS/CHOOSECOLS instead.
Try EXPAND in Viztab
Import your data and use EXPAND with 370+ other formulas. No signup required.
Open Viztab