Release notes
Semi-Annual Enterprise Channel
Semi-Annual Enterprise Channel (Preview)
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.
Example 1
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) |
Example 2
Resize a 1X1 array to a 3X3 array and pad empty elements with "-".
| Data | |
|---|---|
| 1 | 2 |
| 3 | 4 |
| Formulas | |
| =EXPAND(A2,3,3, "-") |