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?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.