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

6.1 Troubleshooting the error value

When you encounter an error value in a cell a warning symbol appears, displayed in the image above. Press with mouse on it to see a pop-up menu that lets you get more information about the error.

1. The first line describes the error if you press with left mouse button on it.
2. The second line opens a pane that explains the error in greater detail.
3. The third line takes you to the "Evaluate Formula" tool, a dialog box appears allowing you to examine the formula in greater detail.
4. This line lets you ignore the error value meaning the warning icon disappears, however, the error is still in the cell.
5. The fifth line lets you edit the formula in the Formula bar.
6. The sixth line opens the Excel settings so you can adjust the Error Checking Options.

Here are a few of the most common Excel errors you may encounter.

#NULL error - This error occurs most often if you by mistake use a space character in a formula where it shouldn't be. Excel interprets a space character as an intersection operator. If the ranges don't intersect an #NULL error is returned. The #NULL! error occurs when a formula attempts to calculate the intersection of two ranges that do not actually intersect. This can happen when the wrong range operator is used in the formula, or when the intersection operator (represented by a space character) is used between two ranges that do not overlap. To fix this error double check that the ranges referenced in the formula that use the intersection operator actually have cells in common.

#SPILL error - The #SPILL! error occurs only in version Excel 365 and is caused by a dynamic array being to large, meaning there are cells below and/or to the right that are not empty. This prevents the dynamic array formula expanding into new empty cells.

#DIV/0 error - This error happens if you try to divide a number by 0 (zero) or a value that equates to zero which is not possible mathematically.

#VALUE error - The #VALUE error occurs when a formula has a value that is of the wrong data type. Such as text where a number is expected or when dates are evaluated as text.

#REF error - The #REF error happens when a cell reference is invalid. This can happen if a cell is deleted that is referenced by a formula.

#NAME error - The #NAME error happens if you misspelled a function or a named range.

#NUM error - The #NUM error shows up when you try to use invalid numeric values in formulas, like square root of a negative number.

#N/A error - The #N/A error happens when a value is not available for a formula or found in a given cell range, for example in the VLOOKUP or MATCH functions.

#GETTING_DATA error - The #GETTING_DATA error shows while external sources are loading, this can indicate a delay in fetching the data or that the external source is unavailable right now.

6.2 The formula returns an unexpected value

To understand why a formula returns an unexpected value we need to examine the calculations steps in detail. Luckily, Excel has a tool that is really handy in these situations. Here is how to troubleshoot a formula:

1. Select the cell containing the formula you want to examine in detail.
2. Go to tab “Formulas” on the ribbon.
3. Press with left mouse button on "Evaluate Formula" button. A dialog box appears.
   The formula appears in a white field inside the dialog box. Underlined expressions are calculations being processed in the next step. The italicized expression is the most recent result. The buttons at the bottom of the dialog box allows you to evaluate the formula in smaller calculations which you control.
4. Press with left mouse button on the "Evaluate" button located at the bottom of the dialog box to process the underlined expression.
5. Repeat pressing the "Evaluate" button until you have seen all calculations step by step. This allows you to examine the formula in greater detail and hopefully find the culprit.
6. Press "Close" button to dismiss the dialog box.

There is also another way to debug formulas using the function key F9. F9 is especially useful if you have a feeling that a specific part of the formula is the issue, this makes it faster than the "Evaluate Formula" tool since you don't need to go through all calculations to find the issue.

1. Enter Edit mode: Double-press with left mouse button on the cell or press F2 to enter Edit mode for the formula.
2. Select part of the formula: Highlight the specific part of the formula you want to evaluate. You can select and evaluate any part of the formula that could work as a standalone formula.
3. Press F9: This will calculate and display the result of just that selected portion.
4. Evaluate step-by-step: You can select and evaluate different parts of the formula to see intermediate results.
5. Check for errors: This allows you to pinpoint which part of a complex formula may be causing an error.

The image above shows cell reference B4:C5 converted to hard-coded values using the F9 key. The EXPAND function requires valid arguments which is not the case in this example. We have found what is wrong with the formula.

Tips!

• View actual values: Selecting a cell reference and pressing F9 will show the actual values in those cells.
• Exit safely: Press Esc to exit Edit mode without changing the formula. Don't press Enter, as that would replace the formula part with the calculated value.
• Full recalculation: Pressing F9 outside of Edit mode will recalculate all formulas in the workbook.

Remember to be careful not to accidentally overwrite parts of your formula when using F9. Always exit with Esc rather than Enter to preserve the original formula. However, if you make a mistake overwriting the formula it is not the end of the world. You can “undo” the action by pressing keyboard shortcut keys CTRL + z or pressing the “Undo” button

6.3 Other errors

Floating-point arithmetic may give inaccurate results in Excel - Article

Floating-point errors are usually very small, often beyond the 15th decimal place, and in most cases don't affect calculations significantly.

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