EXPAND Function

The EXPAND function expands an array by adding rows and columns, which are supplied as separate arguments. The values given for rows and columns represent the dimensions of the final array, not the number of rows or columns to add.

The EXPAND function takes four arguments: array, rows, columns, and pad_with. Array is required, along with at least one value for rows or columns. Array can be a range or an array from another formula. Rows and columns must be positive numbers that are at least the same size as the given array. If not provided, both rows and columns will default to the dimensions of array.

Basic usage

To expand an array to be 5 rows by 4 columns, you can use EXPAND like this:

=EXPAND(array,5,4) // expand to 5 x 4

By default, any new cells created will be filled with #N/A errors. To expand an array to be 10 rows by 3 columns, and fill new cells with "x":

=EXPAND(array,10,3,"x") // expand to 10 x 3, fill with "x"

Note that the numbers given for rows and columns represent final dimensions, not new rows and columns.

Default and custom padding

In the example below, we are adding 2 rows to an existing array with 5 rows. The result is an array with 7 rows. The formula in F3 is:

=EXPAND(B3:D7,7) // default padding

Notice that by default, EXPAND fills the new empty cells with the #N/A error. In the screen, the formula in F3 has been modified to provide zero (0) for the pad_with argument:

=EXPAND(B3:D7,7,,0) // pad with 0

Notice the new cells now contain zero. Also notice that because we are not providing a value for columns, we need to add another comma after rows, in order to place the zero in the right location as the pad_with argument.

Notes

• If rows is less than the row count in array, EXPAND will return a #VALUE! error.
• If columns is less than the column count in array, EXPAND will return a #VALUE! error.