(Office Insider Beta only)
Windows: 2203 (Build 15104)
Mac: 16.60 (220304)

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

Syntax

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

The EXPAND function syntax has the following arguments:

  • array       The array to expand.

  • rows       The number of rows in the expanded array. If missing, rows will not be expanded.

  • columns  The number of columns in the expanded array. If missing, columns will not be expanded.

  • pad_with        The value with which to pad. The default is #N/A.

Remarks

  • If rows isn’t provided or is empty, the default value is the number of rows in the array argument.

  • If columns isn’t provided or is empty, the default value is the number of columns in the array argument.

Errors

  • Excel returns a #VALUE error when the rows or columns argument is less than the rows or columns in the array argument.

  • Excel returns a #N/A error in padded cells if pad_with is not provided.

  • Excel returns a #NUM when array is too large.

Examples

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. If you need to, you can adjust the column widths to see all the data.

Resize a 2X2 array to a 3X3 array and pad empty elements with #N/A.

Data

1

2

3

4

Formulas

=EXPAND(A2:B3,3,3)

Resize a 1X1 array to a 3X3 array and pad empty elements with "-".

Data

1

2

3

4

Formulas

=EXPAND(A2,3,3, "-")

See Also

TOCOL function

TOROW function

TAKE function

DROP function

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Office Insiders

Was this information helpful?

What affected your experience?

Thank you for your feedback!

×