The `EXPAND` function in Excel is used to resize an array to a specified number of rows and columns. It fills any additional spaces with a specified value. It was introduced in Excel 365 and Excel 2021 as part of the dynamic array feature set.
Here’s how you can use the `EXPAND` function:
Syntax
EXPAND(array, rows, [columns], [default])
Parameters
- array: The array you want to expand.
- rows: The number of rows you want the resulting array to have.
- [columns]: Optional. The number of columns you want the resulting array to include. If omitted, it keeps the same number of columns as the original array.
- [default]: Optional. The value to fill in any expanded cells. If omitted, Excel fills the additional cells with `#N/A`.
Steps to Use
- Select a Cell: Click the cell where you want the expanded array to start.
- Enter the Formula: Type the `EXPAND` function formula with the desired parameters.
- Finish with Enter: Press Enter, and Excel will automatically fill the range with the expanded array.
Example
Suppose you have a 2×2 array in cells A1:B2 and want to expand it to a 4×4 array, filling new cells with zero.
=EXPAND(A1:B2, 4, 4, 0)
- In a new cell, type:
- Press Enter.
The original 2×2 array data will appear in the top-left corner of the 4×4 array with zeroes filling the additional space.
Notes
- If the specified `rows` or `columns` are smaller than the source array dimensions, only the existing portion of the array will be displayed.
- The `default` parameter is useful for specifying a meaningful placeholder value in expanded cells instead of `#N/A`.
Using the `EXPAND` function, you can manipulate arrays efficiently to suit your data presentation and analysis needs while maintaining dynamic references.
Unlock Your Potential
Excel
Access
Power BI
