Excel EXPAND function to extend array to specified dimensions

The tutorial introduces a new Excel 365 dynamic array function that can expand an array to the desired dimensions with a value of your choosing.

Want to increase an array to a specified number of rows or columns so that all similar arrays in your worksheet have the same size? In Excel 365, that can be done with one simple formula. Meet the brand-new EXPAND function that can grow an array to any dimensions you set.

• Excel EXPAND function
• How to use EXPAND function in Excel
• Extend array to a given number of rows
• Extend array to a given number of columns
• Expand an array left and up
• Split string and pad array
• Excel EXPAND function not working

Excel EXPAND function

The EXPAND function in Excel is designed to pad an array with a value of your choice to the specified row and column dimensions.

The syntax includes the following arguments:

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

Where:

Array (required) - the original array.

Rows (optional) - the number of rows in the returned array. If omitted, new rows are not added, and the columns argument must be set.

Columns (optional) - the number of columns in the returned array. If omitted, new columns are not added, and the rows argument must be set.

Pad_with - the value to fill new cells with. If omitted, defaults to #N/A.

Tip. To remove unwanted rows or columns from an array, use the DROP function.

EXPAND function availability

Currently, the EXPAND function is available in Excel for Microsoft 365 (Windows and Mac) and Excel for the web.

How to use the EXPAND function in Excel

To increase an array to the desired size, construct an EXPAND formula in this way:

1. For array, supply a range of cells or an array of values returned by another formula.
2. For rows and columns, provide positive numbers that are greater than the number of rows and columns in the original array. Note that these arguments define the dimensions of the resulting array, not the number of rows or columns to add. In each particular formula, either rows or columns must be set. The omitted argument defaults to the corresponding dimension (rows or columns) of the source array.
3. For pad_with, specify the value to populate the new cells added to the array. If it's a text value, enclose it in double quotes. If it's a numeric value, use it without quotation marks. If not provided, the new cells will be padded with the #N/A error.

Being a dynamic array function, EXPAND needs to be entered only in one cell, and it automatically spills the results into as many cells as specified in the rows and columns arguments.

For example, to expand the array C6:D13 to 12 rows and 3 columns, the formula is:

=EXPAND(C6:D13, 12, 3)

As the pad_with argument is not set, the above formula will pad the newly added cells with the #N/A error. To change the default behavior, supply any value you want for the pad_with argument. For example, you can pad the new array values with a hyphen:

=EXPAND(C6:D13, 12, 3, "-")

Below you will find a few more examples of using the EXPAND function in Excel to grow an array in a specific direction.

Expand array to a certain number of rows

To extend an array down to the desired number of rows, define the rows argument and omit columns.

For instance, to increase the initial array to a total of 12 rows, use this formula:

=EXPAND(C6:D13, 12, , "-")

Because columns should not be extended, we type a comma after rows, and after the comma enter the padding value.

Expand array to a certain number of columns

To add more columns to an array, define the columns argument and omit rows.

For example, here's how you can extend the source array to the right, so it has a total of 4 columns:

=EXPAND(A4:C15, , 4, "-")

How to expand an array left and up

The EXPAND function is designed to extend an array right and down. To add cells to the left and above of the source array, you will have to work out your own solution.

Suppose you have an array of values in B4:C12. You aim to add 3 rows above and 1 column to the left of the original array. To better understand the mechanics, we'll first perform each task individually, and then put all the pieces together.

Add columns to left

To extend the array horizontally, you can use the HSTACK function to append a given number of empty columns to the left of your array. In this example, we add 1 blank column using this formula:

=HSTACK(D4:D12, B4:C12)

Notice that an empty array that you stack (D4:D12) should have the same number of rows as the source array and should be supplied first in the formula. As a result, a column of zero values is added to the left of the original array.

Add rows above

To expand the array vertically, leverage the VSTACK function to place a certain number of empty rows above the first row of your original array.

In our case, the following two ranges with the same number of columns are stacked, an empty array coming first:

=VSTACK(B14:C16, B4:C12)

In the result, 3 rows with zero values are added to the top of the initial array.

Add rows above and columns to left

To expand an array vertically and horizontally, nest the VSTACK and HSTACK function one into the other. In our case, the formula takes this form:

=VSTACK(B14:D16, HSTACK(D4:D12, B4:C12))

And produces this result:

Pad an array up and left with custom value

By default, the VSTACK and HSTACK functions return zeros instead of blank cells. To pad the new cells with a custom value, nest a combination of IF and ISBLANK within each stack function, which will replace blanks with the value you specify.

For example, to get the new cells padded with a hyphen, the formula is:

=VSTACK(IF(ISBLANK(B14:D16),"-", B14:D16), HSTACK(IF(ISBLANK(D4:D12),"-", D4:D12), B4:C12))

To extend an array with blank cells, use this formula:

=VSTACK(IF(ISBLANK(B14:D16),"", B14:D16), HSTACK(IF(ISBLANK(D4:D12),"", D4:D12), B4:C12))

Split strings and expand arrays

This example shows how to use the EXPAND and TEXTSPLIT functions together to split a text string by a specific delimiter in individual columns, and then grow the final array to the desired size.

Let's say you have a column of strings like column B in the image below. Each string has several values separated with a particular delimiter (a comma and a space in our case). Your goal is to split each string into separate cells, so that all the resulting arrays have the same number of columns.

To accomplish the task, enter this formula in E4:

=EXPAND(TEXTSPLIT(B4, ", "), , COLUMNS(E3:H3), "-")

In this formula:

• TEXTSPLIT separates the values in B4 using a comma and a space (", ") as the delimiter.
• COLUMNS computes the number of columns in the output array.
• EXPAND extends the returned array to the right padding missing values with hyphens ("-").

Drag the formula down through E15 and you'll get this result:

EXPAND function not working

If the EXPAND function works incorrectly in your Excel or throws an error, the following tips will help you pin down the cause and fix it.

#VALUE! error

Occurs if the value used for the rows or columns argument is less than the total number of rows or columns of the source array.

#N/A error

Appears in padded cells if the pad_with argument is not set.

#NAME? error

Occurs if either the function's name is misspelled or EXPAND is not available in your Excel version. Currently, it's only supported in Excel 365 and Excel for the web. More details can be found in Fixing the #NAME error in Excel.

#SPILL! error

Occurs when there are not enough empty cells to output the expanded array. In most situations, clearing the obstructing cells fixes the error at once. For more information, please see How to fix #SPILL! error in Excel.

That's how to use the EXPAND function in Excel to extend an array to as many rows and columns as your business logic requires. I thank you for reading and hope to see you on our blog next week!

Practice workbook for download

Excel EXPAND formula - examples (.xlsx file)

The above is the detailed content of Excel EXPAND function to extend array to specified dimensions. For more information, please follow other related articles on the PHP Chinese website!