| Syntax |
| =EXPAND(array,rows,[columns]) |
| Purpose / Description |
| Returns an array whose dimensions are expanded by the given number of rows and/or columns. |
| Arguments |
| Name |
Type |
Description |
| array |
reference or array of any |
A reference or nested expression that returns a valid reference, a constant array of any values or a nested expression that returns an array of any values. |
| rows |
number |
A reference to a range, a constant or nested expression that provides the number of rows to be in the resulting array. The argument may be omitted or a number greater than or equal to the number of rows in the original range or array. |
| [columns] |
number |
A reference to a range, a constant or nested expression that provides the number of columns to be in the resulting array. The argument may be omitted or a number greater than or equal to the number of columns in the original range or array. |
| [pad_with] |
any |
A reference to a range, a constant or nested expression that provides the value to be used to pad the new cells. If omitted, the value #N/A is used to fill the newly created cells. |
| Return Values |
| Type |
Specific Value |
Description |
| array of any |
|
Regardless of whether the original array is a reference to a range, a calculated array, or a constant array, the result returned is an array containing the original values and newly added rows or columns, whose values are padded with the value of pad_with. |
| error |
#VALUE! |
If either rows or columns is a value less than the original number of rows and columns respectively. |
| error |
#NUM! |
if the array referenced by the array argument is too large. |
| error |
#N/A |
Newly created cells may contain the value #N/A if the value of the the pad_with argument was omitted. |
| error |
|
Any errors referenced by arguments are returned due to propagation of error values. |
| Notes / Comments / Advice / Warnings |
| Comment: |
This is an extremely useful addition to Excel, along with the DROP, TAKE, CHOOSECOLS and CHOOSEROWS functions, that allow you to manipulate the contents of ranges and arrays for advanced analytical calculations in dynamic array formulas. |
| Examples: |
Open / download sample workbook: DROP, EXPAND, TAKE.xlsx |
| |