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

ArgumentRequired?What it does
arrayYesThe array to expand.
rowsYesTotal rows in the result. Must be at least the array's current row count.
columnsOptionalTotal columns in the result. Defaults to the current width.
pad_withOptionalWhat 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, , "-")
ABC
1AnaAna
2BenBen
3CaraCara
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

Kyle Stott

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.