How to Combine, Reshape, and Resize Arrays in Excel

Working with arrays or adjacent ranges of cells in Microsoft Excel can sometimes be challenging. If you want to combine, reshape, or resize an array, you can choose from a collection of functions that can cover many situations.

Note: These 11 functions are new in Excel as of August 2022. They will be introduced to Excel users over time, starting with Office Insiders.

Combine arrays

Combining data in a table can be difficult. You can use the VSTACK and HSTACK functions to stack arrays vertically and horizontally.

TIED TOGETHER: How to combine data from spreadsheets in Microsoft Excel

The syntax for each function is the same as VSTACK(array1, array2,...) and HSTACK(array1, array2,...) with only one required array and others optional.

To vertically combine the arrays in cells B2 to F3 and H2 to L3, use this formula for the VSTACK function:

=VSTACK(B2:F3,H2:L3)

To combine the same arrays horizontally instead, use this formula for the HSTACK function:

=HSTACK(B2:F3,H2:L3)

reshape arrays

If you don’t want to combine arrays but want to reshape them, you have four functions at your disposal.

TIED TOGETHER: 12 basic Excel functions everyone should know

Convert an array to a row or column

First, you can use the TOROW and TOCOL functions to shape the array as a row or column. The syntax for each is TOROW(array, ignore, by_column) and TOCOL(array, ignore, by_column).

• To ignore: To ignore specific data types, enter 1 for spaces, 2 for errors, or 3 for spaces and errors. The default is 0 to not ignore any values.
• by_column: Use this argument to scan the array column by column with TRUE. If no argument is included, the default is FALSE, which searches the array one line at a time. This determines how the values ​​are sorted.

To convert the array B2 to F3 into one row, use this formula with the TOROW function:

=TOROW(B2:F3)

To turn the same array into a column instead, use the TOCOL function with this formula:

=TOCOL(B2:F3)

Convert a row or column to an array

To do the opposite of the above and turn a row or column into an array, you can use WRAPROWS and WRAPCOLS. The syntax for each is WRAPROWS(reference, wrap_count, pad) and WRAPCOLS(reference, wrap_count, pad) with the reference be a group of cells.

• wrap_count: The number of values ​​for each row or column.
• Pads: The value to display for the pad (blank cell).

Use the WRAPROWS function to wrap cells B2 through K2 into a two-dimensional array. This formula wraps the cells with three values ​​per row with “blank” as the pad.

=WRAPROWS(B2:K2,3,"empty")

Use the WRAPCOLS function to convert the same cells into a two-dimensional array by wrapping columns. This formula wraps the cells with three values ​​per column with “blank” as the pad.

=WRAPCOLS(B2:K2,3,"empty")

Resize arrays

You may want to resize an array by adding some data or deleting unnecessary cells. There are five features to help you, depending on the result you want.

TIED TOGETHER: 13 essential Excel functions for data entry

Take or drop rows or columns

Use the TAKE function to keep the number of rows or columns you specify. With the DROP function you do the opposite and remove the number of rows or columns you specify. You use positive numbers to take or delete from the beginning of the array and negative numbers to take or delete from the end.

The syntax for each is TAKE(array, rows, columns) and DROP(array, rows, columns) where you need at least one of the second two arguments; rows or columns.

To keep the first two rows in array B2 through F5, use TAKE with the rows Fight. Here is the formula:

=TAKE(B2:F5,2)

To keep the first two columns in the same array, use the columns argument instead of:

=TAKE(B2:F5,,2)

To remove the first two rows in array B2 through F5, use DROP with the rows argument and this formula:

=DROP(B2:F5,2)

To remove the first two columns in the same array, use the columns Argument instead and this formula:

=DROP(B2:F5,,2)

Keep a specific number of rows or columns

To select the exact row and column numbers you want to keep from an array, you would use the CHOOSEROWS and CHOOSECOLS functions.

The syntax for each is CHOOSEROWS(array, row_num1, row_num2,...) and CHOOSECOLS(array, column_num1, column_num2,...) where the first two arguments are required. You can add more row and column numbers if you like.

To return rows 2 and 4 from array B2 through F5, you would use the CHOOSEROWS function and this formula:

=CHOOSEROWS(B2:F5,2,4)

To return columns 3 and 5 from the same array, you would use the CHOOSECOLS function with this formula:

=CHOOSECOLS(B2:F5,3,5)

Note: Remember to use the row or column numbers for the array, Not for the leaf.

Expand an array to specific dimensions

Maybe you plan to add more data to your array so you want to give it a specific size to add a border or use conditional formatting. With the EXPAND function you enter the number of rows and columns you want your array to cover.

TIED TOGETHER: How to add and change cell borders in Excel

The syntax for the function is EXPAND(array, rows, columns, pad) where one is missing rows or columns Argument means that these do not expand. Optionally you can use the pad Value for the empty cells.

To expand the array B2 to F5 to cover 10 rows and 10 columns you would use this formula:

=EXPAND(B2:F5,10,10)

To extend and include the same array to the same dimensions pad “empty”, use this formula:

=EXPAND(B2:F5,10,10,"empty")

Tip: Although the pad The argument is optional, you may prefer to see an error as shown above.

These 11 functions give you more control than ever over your arrays in Microsoft Excel. Try them and see if they achieve what you need.

TIED TOGETHER: How to fix common formula errors in Microsoft Excel