What is the syntax for EXPAND in Excel?

The syntax for EXPAND is: =EXPAND(array, rows, [columns], [pad_with])

What are common errors with EXPAND?

Requires Excel 365. Cannot shrink array

What is EXPAND used for in Excel?

EXPAND pads an array to larger dimensions by adding rows and/or columns filled with a specified value. Useful for aligning arrays of different sizes, creating fixed-size outputs, and matrix operations requiring specific dimensions.

How do I pad an array with blanks instead of #N/A?

Use empty string as pad_with: =EXPAND(A1:B2, 10, 5, ""). This creates a 10×5 array with blanks in the expanded cells. Use 0 for zeros: =EXPAND(A1:B2, 10, 5, 0).

Can EXPAND shrink an array?

No, EXPAND only increases dimensions. If you specify fewer rows or columns than the original array, you get an error. Use TAKE or DROP to reduce array size, or CHOOSEROWS/CHOOSECOLS for specific selections.

Lookup & Reference

EXPAND Function in Excel

Expands or pads an array to specified row and column dimensions.

Syntax

=EXPAND(array, rows, [columns], [pad_with])

Arguments

array (required): Array to expand
rows (required): Number of rows in result
columns (optional): Number of columns in result
pad_with (optional): Value for padding (default #N/A)

Examples

=EXPAND(A1:B2, 5, 5, 0) - Expand 2x2 to 5x5 with zeros - Result: 5x5 array

When to use EXPAND

Use EXPAND when you need to retrieve values from a table or range based on a key.
Confirm data types match (text vs number) and trim spaces before lookups.
Pair with [IFERROR](/functions/iferror/) or [IFNA](/functions/ifna/) for cleaner reports.
See the [lookup functions guide](/guides/excel-lookup-functions-guide/) for VLOOKUP, XLOOKUP, and INDEX MATCH paths.

Common EXPAND mistakes

#N/A often means the key is missing or exact match is wrong — see [#N/A error](/errors/na/).
Lock table references with $ when copying formulas down a column.
Compare legacy vs modern lookups in [XLOOKUP vs VLOOKUP](/compare/xlookup-vs-vlookup/).

Common errors

Requires Excel 365
Cannot shrink array

Use cases

Pad arrays
Align dimensions
Matrix operations

Frequently asked questions

What is EXPAND used for in Excel? EXPAND pads an array to larger dimensions by adding rows and/or columns filled with a specified value. Useful for aligning arrays of different sizes, creating fixed-size outputs, and matrix operations requiring specific dimensions.
How do I pad an array with blanks instead of #N/A? Use empty string as pad_with: =EXPAND(A1:B2, 10, 5, ""). This creates a 10×5 array with blanks in the expanded cells. Use 0 for zeros: =EXPAND(A1:B2, 10, 5, 0).
Can EXPAND shrink an array? No, EXPAND only increases dimensions. If you specify fewer rows or columns than the original array, you get an error. Use TAKE or DROP to reduce array size, or CHOOSEROWS/CHOOSECOLS for specific selections.

Editorial review

Reviewed by Excel.Directory Editorial Team. Updated May 2026.