The Excel EXPAND function expands, or pads, an array to a specified number of rows and columns.
EXPAND Function Syntax
=EXPAND(array, rows, [columns])
array : The array to expand.
rows : The number of rows in the expanded array. If missing, rows will not be expanded.
columns : [Optional] The number of columns in the expanded array. If missing, columns will not be expanded.
pad_with : [Optional] The value with which to pad. The default is #N/A.
Note: The EXPAND function is part of the new Excel Dynamic Arrays family. At the time of writing, Dynamic Arrays are only available in Microsoft 365. Excel 2021 and earlier Excel versions will not get EXPAND.
Watch the Video
Download the Example Workbook
Enter your email address below to download the sample workbook.
Excel EXPAND Function Example
Let’s say I want to expand the suburb table to include an extra row and column. I can do that with the EXPAND function and specify TBA as the padding like so:
Notice cell C25 returns zero. This is because it’s an empty cell in the array argument (C17:D19). Only the cells outside of this range return the pad_with argument of "TBA".
Related Excel Array Shaping Functions
| TOROW Function | Returns the array in a single row. Useful for combining data across multiple columns and rows into a single row. |
| TOCOL Function | Returns the array in a single column. Useful for combining data across multiple columns and rows into a single column. |
| WRAPROWS Function | Lets you wrap (reshape) a row or column of values into rows, you specify the number of values in each row. |
| WRAPCOLS Function | Lets you wrap (reshape) a row or column of values into columns, you specify the number of values in each column. |
| DROP Function | Remove a specified number of contiguous rows or columns from the start or end of an array. |
| TAKE Function | Extract a specified number of contiguous rows or columns from the start or end of an array. |
| CHOOSEROWS Function | Extract rows from the specified column or columns. |
| CHOOSECOLS Function | Extract columns from the specified rows or rows. |
| VSTACK and HSTACK Functions | Combine arrays arranged vertically (VSTACK) or horizontally (HSTACK) into a new single array. |