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

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

  1. Pad a 2 × 1 range to 2 × 3 with dashes

    ABCDE
    1ItemPen--
    2PenTape--
    3Tape

    In C1: =EXPAND(A2:A3, 2, 3, "-") → Pen, -, -; Tape, -, -

    Try this example

Common mistakes

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.

Translate a whole formula

Try EXPAND in the playground

Related functions