EXPAND function
EXPAND makes an array bigger by padding it with a value you choose, which is useful before stacking arrays of different sizes. New cells show #N/A unless you set a padding value.
Example
Read it as a sentence: grow the 2 by 2 block {1,2;3,4} to 3 rows and 3 columns, fill the new spots with 10, then add everything up, which gives 60.
Syntax
EXPAND(array, rows, [columns], [pad_with])
Arguments in [square brackets] are optional.
-
array(required) - Array to enlarge.
-
rows(required) - Total rows in the result.
-
columns(optional) - Total columns in the result.
-
pad_with(optional) - Value for the new cells; default is #N/A.
Returns: The array padded out to a bigger size.
One EXPAND formula can fill more than one cell. When the result has several values, they spill into the cells below or to the right of the formula.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=EXPAND({1,2;3,4},3,3) | 1 | Default pad |
=EXPAND({1,2;3,4},3,3,0) | 1 | Pad with 0 |
=EXPAND({1,2},,3,"-") | 1 | Columns only, pad text |
=EXPAND(1,2) | 1 | Scalar |
=EXPAND({1,2;3,4},"3",3,"") | 1 | Text rows, empty pad |
=EXPAND({1,2;3,4},1) | #VALUE! | Smaller than array |
Watch out for
- New cells are #N/A unless you set pad_with.
- You cannot shrink an array with it.
Where it works
- Excel
- EXPAND arrived in Excel 2024. Older versions do not have it. 365, Excel for the web and Excel 2024; not in older perpetual versions.
- Google Sheets
- No direct equivalent. No direct equivalent (unsure if added recently); pad with ARRAYFORMULA and IFERROR over a larger range.
Part of Lookup and reference functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .