📎 This article includes 1 downloadable practice file ↓
In this article
EXPAND pads an array to a given number of rows and columns — handy before VSTACK when pieces have different widths.
Syntax
=EXPAND(array, rows, [columns], [pad_with])| Argument | What it means |
|---|---|
pad_with |
Value for new cells; default #N/A. |
Examples
Example 1
=VSTACK(A1:D10, EXPAND(F1:G5, , 4, ""))Stack a 2-column block under a 4-column one without #N/A.
EXPAND in practice
Where you will use it
- Pad an array to a fixed size for a template
- Make ragged results line up
- Add blank rows for printing
Worked example
| Rows | Formula | Result |
|---|---|---|
| 3 | =ARRAYTOTEXT(EXPAND({"A",1;"B",2},A2,2,"-")) | A, 1, B, 2, -, - |
| 4 | =ARRAYTOTEXT(EXPAND({"A",1;"B",2},A3,2,"-")) | A, 1, B, 2, -, -, -, - |
| 2 | =ARRAYTOTEXT(EXPAND({"A",1;"B",2},A4,2,"-")) | A, 1, B, 2 |
Results calculated in Excel.
Mistakes people make
| Mistake | What to do instead |
|---|---|
| Smaller than the source | #VALUE!; EXPAND only grows |
| Pad value | Without it you get #N/A in new cells |
Related functions
📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.
⚡ Interactive Practice Lab & Formula Tester:
Test formulas dynamically in our web formula laboratory or download the companion Excel workbook.
📎 Practice files for this article
- 📗Practice workbook: Dynamic arrays18 tasks on realistic sales data — type your formula, the Check column turns green when it is right. Answers sheet included.⬇ XLSX · 35 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
✨ Ask AI about this article
Stuck on a step? Ask a question and the AI answers using this article.
Free · AI can be wrong