Wauvel

Excel like a finance pro.

← The library

Dynamic array function

EXPAND

Grows an array to given dimensions, padding new cells.

Rarely usedDifficulty 1400 · AdvancedUsage rank #116 of 520
Practice · 2 questions →

When to use it

Grows an array to a given size, filling the new cells with a value. Pads ragged data so arrays line up for VSTACK or HSTACK.

The shape of it

Syntax
=EXPAND(array, rows, [columns], [pad_with])

Worked examples

  • Pad a block

    =EXPAND(A2:B3,3,3,"-") → 3 by 3 with dashes in the new cells

    Two rows and columns padded to three of each.

  • Pad a row

    =EXPAND({1,2},1,4,0) → {1,2,0,0}

    A row padded with zeros.

  • Align for HSTACK

    =HSTACK(A2:A6,EXPAND(B2:B4,5,1,"")) → aligned columns

    The shorter column is padded so the stack has no #N/A.

Worth knowing

  • Without a pad value the new cells are #N/A.
  • Cannot shrink; use TAKE for that.
  • Useful for fixed-size print areas.

Where it goes wrong

  • #VALUE! when the new size is smaller than the array.
  • Requires Microsoft 365.

Related

FILTERSORTUNIQUESEQUENCESORTBYVSTACK

Learn the moves here — or let Wauvel run them on your numbers.

Meet your AI CFO →

One CFO-grade Excel tip a week

A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.