Pad an Array with EXPAND

Excel Formulas › Dynamic Arrays

365EXPAND

EXPAND grows an array to a set size, filling the new cells with a value you choose. Handy for forcing results to a fixed shape or padding ragged data before stacking.

Quick formula: pad an array to 5 rows, filling blanks with 0:
=EXPAND(A2:A4, 5, 1, 0)
The array becomes 5 rows tall; the extra cells are filled with 0 (your pad_with value) instead of #N/A.

Functions used (tap for the full reference guide):

EXPANDVSTACK

The example

A 3-row array padded to 5 rows with 0.

AB
1OriginalExpanded
21010
32020
43030
50

The formula

Grow to a fixed shape, padding the rest:

=EXPAND(A2:A4, 5, 1, 0) // 3 rows → 5 rows, padded with 0

How it works

EXPAND resizes and fills:

  1. Arguments: the array, target rows, target columns, and the pad value.
  2. New cells beyond the original data are filled with the pad value (default #N/A — usually set 0 or "").
  3. You can grow rows, columns, or both. EXPAND never shrinks — the target must be ≥ the original size.
  4. It’s most useful for making arrays the same shape before combining them with VSTACK/HSTACK.

Even up before stacking: when two ranges have different widths, EXPAND the narrower one to match, then VSTACK them without ragged-array errors. Pad with "" for clean blanks in a report.

Try it: interactive demo

Live demo

Values + target rows + pad value.

Result:

Variations

Pad with blank

Empty string fill:

=EXPAND(A2:A4, 5, 1, "")

Grow columns

Widen instead:

=EXPAND(A2:C2, 1, 5, 0)

Even up then stack

Match shapes:

=VSTACK(EXPAND(a,,3,""), b)

Pitfalls & errors

Can’t shrink. The target rows/cols must be at least the original size, or EXPAND returns #VALUE!.

Default pad is #N/A. Omitting the pad value fills with #N/A — usually you want 0 or "".

365 only. EXPAND is a dynamic-array function.

Practice workbook

📊
Download the free Pad an Array with EXPAND practice workbook
EXPAND examples (formula text + result) with blank-pad, widen, and stack variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

What does the EXPAND function do in Excel?
EXPAND grows an array to a target number of rows and columns, filling the new cells with a pad value: =EXPAND(array, rows, cols, padValue). Requires Excel 365.
How do I avoid #N/A in the padded cells?
Supply a pad value as the last argument, e.g. =EXPAND(A2:A4, 5, 1, 0) fills with 0 instead of #N/A.
Can EXPAND make an array smaller?
No — the target size must be at least the original. Use TAKE or DROP to shrink an array.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Stack arrays · TAKE & DROP · Reshape an array

Function references: EXPAND · VSTACK