Excel functions / Dynamic arrays
EXPAND function
Grows an array to a set number of rows and columns, filling the new cells.
Syntax
=EXPAND(array, rows, [columns], [pad_with])
Arguments
array: The array to grow.rows: How many rows the result has. Leave empty to keep the rows as they are.columns(optional): How many columns the result has.pad_with(optional): What fills the new cells (default #N/A).
When to use it
EXPAND makes an array bigger, to a given number of rows and columns, filling the new cells with a value you choose (or #N/A). It pads a result so that stacked arrays line up.
Examples
Pad a 2 × 1 range to 2 × 3 with dashes
A B C D E 1 Item Pen - - 2 Pen Tape - - 3 Tape In C1:
=EXPAND(A2:A3, 2, 3, "-")→ Pen, -, -; Tape, -, -
Common mistakes
- A smaller size. EXPAND cannot shrink: a size smaller than the array gives #VALUE!. Use TAKE to shrink.
EXPAND in other languages
Excel shows function names in its own language. In these languages it also puts a semicolon between arguments where English has a comma.
- Czech: ROZBALIT
- Danish: UDVID
- Dutch: UITBREIDEN
- Finnish: LAAJENNA
- German: ERWEITERN
- Italian: ESPANDI
- Norwegian: UTVID
- Polish: ROZSZERZ
- Russian: РАЗВЕРНУТЬ
- Spanish: EXPANDIR
- Swedish: EXPANDERA
- Turkish: GENİŞLET