Exceljet

Quick, clean, and to the point

Excel EXPAND Function

Excel EXPAND function
Summary 

The EXPAND function expands an array by adding rows and columns, which are supplied as separate arguments. The numbers given for rows and columns represent the dimensions of the final array.

Purpose 
Expand array by adding rows or columns
Return value 
Expanded array
Syntax 
=EXPAND (array, [rows], [columns], [pad_with])
Arguments 
  • array - The array to expand.
  • rows - [optional] The final number of rows. Default is total rows.
  • columns - [optional] The final number of columns. Default is total columns.
  • pad_with - [optional] Value to use for new cells. Default is #N/A.
Version 
Usage notes 

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_withArray 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

 EXPAND function - add two rows 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

 EXPAND function - add two rows custom padding

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.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.