How to use the EXPAND function

What is the EXPAND function?

The EXPAND function increases a cell range or array by a specified number of columns and rows. You may use a string to pad empty expanded cells if you like. The EXPAND function is useful for making arrays larger in terms of rows and columns. This is a huge step forward making it much easier to manipulating arrays, earlier Excel versions required lots of functions if even possible for the same task.

The EXPAND function returns a #N/A! error in new cells, use the fourth argument pad_with to populate these cells with a string you want. Tip! Use double quotes to specify a blan value like this: ""

The EXPAND function is available to Excel 365 users and is in the "Array manipulation" category.

1. Syntax

EXPAND(array, rows, [columns], [pad_with])

Back to top

2. Arguments

array	Required. The source cell range or array.
rows	Required. The new number of rows.
[columns]	Optional. The new number of columns.
[pad_with]	Optional. A string to pad new array values with. Default is #N/A meaning not available.

Back to top

3. Example

The picture above shows how the EXPAND function increases the original array from two columns and two rows to three columns and three rows. The number of cells increases to 9 cells from 4 cells, 3 rows * 3 columns equals 9 cells. The new cells are padded with a minus sign.

Dynamic array formula in cell B6:

The EXPAND function has 4 arguments. The first argument refers to the source data you want to manipulate. The second argument is the number of rows the new array shall have. The third argument specifies how many columns you want. The fourth and last argument specifies the string you want to pad the new cells with.

The columns and pad_with arguments are optional, however, if you specify the columns argument then you don't need to specify the rows argument if you only need to expand the columns. This means that the rows argument is optional if you specify the columns argument.

3.1 Explaining formula

Step 1 - EXPAND function

EXPAND(array, rows, [columns], [pad_with])

Step 2 - Populate arguments

array - B2:C3
rows - 3
[columns] - 3
[pad_with] - "-". The double quotes are needed if a non-numeric value is used.

Step 3 - Evaluate function

EXPAND(B2:C3, 3, 3, "-")

becomes

EXPAND({89, 27;
68, 84}, 3, 3, "-")

and returns

{89, 27, "-";
68, 84, "-";
"-", "-", "-"}

Back to top

4. Example - how to extend an array up and left

The EXPAND function lets you add more rows and columns to an array, they are, however, added to the right and below of the source array. It would be nice if negative values added cells above or the the left of the array. But that won't work, this workaround shows you how to append cells above and to the right of the original array.

Use the following formula to add array containers above and to the left of the original array.

Dynamic array formula in cell B8:

This example demonstrates an array in cell range C3:D4 that we want to expand one row above and one column to the left. As far as I know this is not possible using the EXPAND function, however, the HSTACK and VSTACK lets you add arrays to the original array in a way that makes it possible to expand it above and to the right of the source array.

If you need more than one row and column then use larger empty cell ranges in the HSTACK and VSTACK arguments, this makes it possible to adjust the source array like you want.

Explaining formula

Step 1 - Add arrays horizontally

The HSTACK function lets you combine cell ranges or arrays, it joins data to the first blank cell to the right of a cell range or array (horizontal stacking)

HSTACK(array1, [array2],...)

HSTACK(B3:B4, C3:D4)

becomes

HSTACK({0; 0}, {89, 27; 68, 84})

and returns

{0, 89, 27; 0, 68, 84}.

Step 2 - Add arrays vertically

The VSTACK function lets you combine cell ranges or arrays, it joins data to the first blank cell at the bottom of a cell range or array (vertical stacking).

VSTACK(array1, [array2],...)

VSTACK(B2:D2, HSTACK(B3:B4, C3:D4))

becomes

VSTACK({0, 0, 0}, {0, 89, 27; 0, 68, 84})

and returns

{0, 0, 0;
0, 89, 27;
0, 68, 84}.

Back to top

5. Example - extend array dynamically

The section shows a formula in cell B9 that:

1. splits a text string based on a delimiting character. In this case a space character. The output is an array containing each substring in each array value.
2. then counts the number of values in the array.
3. the count is used to expand the array one row below and one column to the right.

In other words, the formula resizes an array dynamically meaning the array is always one row and one column larger than the source array. Try changing the text value in cell B3 and the dynamic array formula in cell D3 adjusts accordingly.

Dynamic array formula in cell B9:

Explaining formula

You can follow the formula calculations step by step by utilizing the "Evaluate" tool located on the Formula tab on the ribbon.

The new LET function is also possible examining using the "Evaluate" tool, this is not the case with the LAMBDA functions. However, there is no LAMBDA function in this example so no worries yet.

Step 1 - Split text

The TEXTSPLIT function lets you split a string into an array across columns and rows based on delimiting characters.

TEXTSPLIT(Input_Text, col_delimiter, [row_delimiter], [Ignore_Empty])

TEXTSPLIT(B3, " ")

Step 2 - Calculate rows in array and add 1

The ROWS function calculates the number of rows in a given cell range or array.

ROWS(array)

ROWS(TEXTSPLIT(B3, " "))+1

Step 3 - Calculate columns in array and add 1

The COLUMNS function calculates the number of columns in a given cell range or array.

COLUMNS(array)

COLUMNS(TEXTSPLIT(B3, " "))+1

Step 4 - Expand array one row and one column

EXPAND(TEXTSPLIT(B3, " "), ROWS(TEXTSPLIT(B3, " "))+1, COLUMNS(TEXTSPLIT(B3, " "))+1, "-")

becomes

EXPAND({"I", "travel", "the", "world"}, 1+1, 4+1, "-")

and returns

{"I", "travel", "the", "world", "-";
"-", "-", "-", "-", "-"}

Step 5 - Shorten the formula

The LET function lets you name intermediate calculation results which can shorten formulas considerably and improve performance.

LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])

TEXTSPLIT(B3, " ") is repeated three times.

EXPAND(TEXTSPLIT(B3, " "), ROWS(TEXTSPLIT(B3, " "))+1, COLUMNS(TEXTSPLIT(B3, " "))+1, "-")

becomes

LET(x, TEXTSPLIT(B3, " "),EXPAND(x,ROWS(x)+1,COLUMNS(x)+1,"-"))

Back to top

6. EXPAND function error

Can you use negative numbers in the EXPAND function?

No, the EXPAND function returns a #VALUE! error if a negative number is used in the second or third argument. It would have been nice to add rows and columns above and to the right of the array, however, this is not the case. Change the negative numbers in the second and third argument to solve the #VALUE! issue.

Can you use numbers smaller than the actual size of the array in the EXPAND function?

No, numbers smaller than the actual array size also return  a #VALUE! error. Cell range B2:D4 has three rows and three columns.

Back to top

7. Example - values in random order

The image above demonstrates a formula that adds an additional row to cell range B2:D5 and the returns values in random order.

Dynamic array formula in cell B8:

Cell range B2:D5 contains values, the expand function adds another row and pads those cells with a hyphen. The formula in cell B8 returns an array that has the same number of columns as the source range B2:D5 but an additional row. The values are in random order  and changes every time you press function key f9.

7.1 Explaining formula in cell B8

You can follow the calculations step by step by pressing the "Evaluate" tool located on the Formula tab on the ribbon. This makes it easier to spot errors and understand the formula in greater detail.

Step 1 - Expand array with an extra row

The expand function has four arguments, the first one is the row argument.  We want to expand the array by 1 row. The Source data range has four rows, an additional row equals 5 rows.The third argument is the columns argument which does not change, I leave the argument empty. The last argument is the pad_with argument, I use the hyphen character  to populate empty cells.

EXPAND(B2:D5,5,,"-")

becomes

EXPAND({89, 27, 26;68, 84, 98;19, 92, 62;37, 63, 45}, 5, , "-")

and returns

{89, 27, 26;
68, 84, 98;
19, 92, 62;
37, 63, 45;
"-", "-", "-"}

Step 2 - Rearrange values into a single column

The TOCOL function rearranges values from a 2D cell range or array to a single column.

TOCOL(array, [ignore], [scan_by_col])

TOCOL(EXPAND(B2:D5,5,,"-"))

becomes

TOCOL({89, 27, 26;
68, 84, 98;
19, 92, 62;
37, 63, 45;
"-", "-", "-"})

and returns

{89; 27; 26; 68; 84; 98; 19; 92; 62; 37; 63; 45; "-"; "-"; "-"}

The semicolon is a row delimiting character, your computer may use an other row delimiting character.

Step 3 - Count cells

The ROWS function returns the number of columns in a given cell range or array.

ROWS(array)

ROWS(TOCOL(EXPAND(B2:D5,5,,"-")))

becomes

ROWS({89; 27; 26; 68; 84; 98; 19; 92; 62; 37; 63; 45; "-"; "-"; "-"})

and returns 15.

Step 4 - Create random cells

The RANDARRAY function returns a table of random numbers across rows and columns.

RANDARRAY([rows], [columns], [min], [max], [whole_number])

RANDARRAY(ROWS(TOCOL(EXPAND(B2:D5, 5, , "-")))))

becomes

RANDARRAY(15)

and returns

{0.215398134613085, 0.390607168196479, ... ,0.83231474462401}.

Step 6 - Rearrange values in random order

The SORTBY function allows you to sort values from a cell range or array based on a corresponding cell range or array.

SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…)

SORTBY(TOCOL(EXPAND(B2:D5, 5, , "-")), RANDARRAY(ROWS(TOCOL(EXPAND(B2:D5, 5, , "-")))))

becomes

SORTBY({89; 27; 26; 68; 84; 98; 19; 92; 62; 37; 63; 45; "-"; "-"; "-"}, {0.215398134613085, 0.390607168196479, ... ,0.83231474462401})

and returns

{"-"; "-"; 84; 62; 89; 92; 98; 37; 63; "-"; 26; 27; 45; 68; 19}.

Step 7 - Rearrange values to the original array size

The WRAPROWS function rearranges values from a single row to a 2D cell range based on a given number of values per row.

WRAPROWS(vector, wrap_count, [pad_with])

WRAPROWS(SORTBY(TOCOL(EXPAND(B2:D5,5,,"-")),RANDARRAY(ROWS(TOCOL(EXPAND(B2:D5,5,,"-"))))),3)

becomes

WRAPROWS({"-"; "-"; 84; 62; 89; 92; 98; 37; 63; "-"; 26; 27; 45; 68; 19},3)

and returns

{84,92,"-";
"-",68,63;
27,37,19;
45,62,"-";
98,89,26}

Step 8 - Shorten formula

The LET function lets you name intermediate calculation results which can shorten formulas considerably and improve performance.

LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])

WRAPROWS(SORTBY(TOCOL(EXPAND(B2:D5,5,,"-")),RANDARRAY(ROWS(TOCOL(EXPAND(B2:D5,5,,"-"))))),3)

becomes

LET(x, TOCOL(EXPAND(B2:D5, 5, , "-")), WRAPROWS(SORTBY(x, RANDARRAY(ROWS(x))), 3))

Back to top

Back to top

Useful links

EXPAND function - Microsoft support
Excel EXPAND function to grow array to specified number of rows and columns
EXPAND Function - Exceljet

'EXPAND' function examples

Functions in 'Array manipulation' category

The EXPAND function function is one of 11 functions in the 'Array manipulation' category.

Excel function categories

Excel categories

Leave a Comment