EXPAND Function
Pads an array out to a set number of rows and columns.
Category
Lookup & Reference
Available in
Microsoft 365, Excel 2024
Returns
A spilled array
Want to practice EXPAND in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox
Overview
EXPAND grows an array to the size you ask for. Any new cells are filled with #N/A, or with a value you choose using pad_with.
It's most useful when combining arrays of different sizes, so that everything lines up neatly.
Syntax
=EXPAND(array, rows, [columns], [pad_with])
Arguments
| Argument | Required? | What it does |
|---|---|---|
| array | Yes | The array to expand. |
| rows | Yes | Total rows in the result. Must be at least the array's current row count. |
| columns | Optional | Total columns in the result. Defaults to the current width. |
| pad_with | Optional | What to put in the new cells. Defaults to #N/A. |
Examples
Example 1 — Padding a list with blanks
We have three names and want a fixed five-row block, with dashes in the empty spots.
C1fx=EXPAND(A1:A3, 5, , "-")
| A | B | C | |
|---|---|---|---|
| 1 | Ana | Ana | |
| 2 | Ben | Ben | |
| 3 | Cara | Cara | |
| 4 | - | ||
| 5 | - |
=EXPAND(A1:A3, 5, , "-") Explain
Example 2 — Adding an empty column
Make a two-column list three columns wide, leaving the new column blank.
=EXPAND(A2:B10, ROWS(A2:B10), 3, "") Explain
Use "" as pad_with when you want the new cells to look blank instead of showing #N/A.
Common mistakes
- #VALUE! errors. rows or columns is smaller than the array you started with — EXPAND can't shrink. Use TAKE for that.
- Forgetting the comma. To skip columns but still set pad_with, leave an empty argument: EXPAND(A1:A3, 5, , 0).
Notes

Author
Kyle Stott
Certified Microsoft Excel Expert
Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.