The Excel EXPAND function grows an array to a size you specify, padding the new cells with a value of your choice. It’s the missing piece when arrays must be a fixed shape — aligning blocks before VSTACK or HSTACK, filling report templates, or squaring off ragged data. One warning up front: leave the pad value out and Excel fills the new cells with #N/A.
Syntax
| Argument | Description | |
|---|---|---|
array | Required | The range or array to grow. |
rows | Required | Total rows in the result. Must be at least the array’s current row count — EXPAND can’t shrink. Omit to keep the current count. |
columns | Optional | Total columns in the result. Same rule: equal or larger only. |
pad_with | Optional | The value for every added cell. Defaults to the #N/A error — supply "", 0, or "-" to get something usable. |
Available in: Excel for Microsoft 365, Excel 2024+, and Excel for the web. Older versions show #NAME?. The result is a dynamic array that spills onto the sheet.
Grow an array to a fixed size
Quarterly results so far sit in A1:B2 — two quarters in. The report template needs a fixed 4×3 block, dashes where data hasn’t landed yet:
| A | B | D | E | F | ||
|---|---|---|---|---|---|---|
| 1 | Q1 | 8,200 | Q1 | 8,200 | - | |
| 2 | Q2 | 9,100 | Q2 | 9,100 | - | |
| 3 | - | - | - | |||
| 4 | - | - | - |
Note that rows and columns are the total size of the result, not how many to add. And they only go up — to cut an array down, use TAKE or DROP instead.
Try it: interactive EXPAND demo
Grow the 2×2 quarterly block: set the target size and the pad value — including the trap of leaving pad_with out.
Where EXPAND shines: aligning arrays before stacking
HSTACK and VSTACK pad mismatched arrays with #N/A. EXPAND lets you control that padding by sizing the blocks to match first:
Combine with ROWS to make the target size dynamic — “as tall as the other block, whatever that is”:
Or build fixed-size print blocks: a 10-row order form that always reserves space whether 3 lines or 9 are filled:
IFNA cleanup: already stuck with #N/A padding from a stack? Wrap it: =IFNA(VSTACK(a, b), "") swaps every padded error for a blank.
Errors & common pitfalls
The big one — omitted pad_with fills with #N/A. =EXPAND(A1:B2, 4, 3) gives you a block whose new cells are all #N/A errors. That poisons downstream SUMs and lookups. Make supplying pad_with a habit: "" for display, 0 for math.
#VALUE! — you tried to shrink. rows and columns must be greater than or equal to the array’s current size. EXPAND only grows; to cut down, use TAKE or DROP.
#SPILL! — the landing zone isn’t empty. The expanded block is bigger than the source by design — make sure the full target size has room to spill.
#NAME? — older Excel. EXPAND requires Microsoft 365 or Excel 2024+. Earlier versions don’t recognize it.
Pitfall: rows/columns are totals, not increments. =EXPAND(A1:B2, 2) adds nothing — the array is already 2 rows tall. To add two rows to a block of unknown size, use ROWS(array)+2.
Practice workbook
Frequently asked questions
What does EXPAND pad with if I omit pad_with?
Can EXPAND make an array smaller?
Are rows and columns in EXPAND the new total size or the amount to add?
When would I actually use EXPAND?
Which Excel versions support EXPAND?
Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class