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, "-") |