HomeTechnologyNewsHow to Combine, Reshape, and Resize Arrays in Excel

# How to Combine, Reshape, and Resize Arrays in Excel

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

Note: These 11 features are new to Excel as of August 2022. They will roll out to Excel users over time, starting with Office Insiders.

## Combine Arrays

Combining data in a spreadsheet can be difficult. With the VSTACK and HSTACK functions, you can stack arrays vertically and horizontally.

RELATED: How to Combine Spreadsheet Data in Microsoft Excel

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

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

`=VSTACK(B2:F3,H2:L3)` To combine those same arrays horizontally, use this formula for the HSTACK function:

`=HSTACK(B2:F3,H2:L3)` ## Reshape Matrices

If it’s not combining arrays you want to do, but reshaping them, there are four functions you can use.

RELATED: 12 Basic Excel Functions Everyone Should Know

### Convert an array to a row or column

First, the TOROW and TOCOL functions allow you to format the array as a row or a column. The syntax for each is `TOROW(array, ignore, by_column)` Y `TOCOL(array, ignore, by_column)`.

• Ignore: To ignore certain types of data, enter 1 for blanks, 2 for errors, or 3 for blanks and errors. The default value is 0 to ignore any values.
• By_column: Use this argument to scan the matrix by column using TRUE. If no argument is included, FALSE is the default value, which parses the array by row. This determines how the values ​​are ordered.

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

`=TOROW(B2:F3)` To convert that same array to a column, use the TOCOL function with this formula:

`=TOCOL(B2:F3)` ### Convert a row or column to a matrix

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

• wrap_count: The number of values ​​for each row or column.

To convert cells B2 through K2 to a two-dimensional array by wrapping rows, use the WRAPROWS function. With this formula, cells are wrapped using three values ​​per row with “empty” as the `pad`.

`=WRAPROWS(B2:K2,3,"empty")` To convert the same cells to a two-dimensional array by wrapping columns, use the WRAPCOLS function. With this formula, cells are wrapped using three values ​​per column with “empty” as the `pad`.

`=WRAPCOLS(B2:K2,3,"empty")` ## Resize arrays

Maybe you want to adjust the size of an array by adding some data or deleting unnecessary cells. There are five functions to help you do this depending on the result you want.

RELATED: 13 Essential Excel Functions for Data Entry

### Grab or drop rows or columns

With the TAKE function, you keep the number of rows or columns that you specify. With the DROP function, it does the opposite and removes the number of rows or columns that you specify. You will use positive numbers to take or take from the beginning of the array and negative numbers to take or take from the end.

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

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

`=TAKE(B2:F5,2)` To keep the first two columns in the same array, use the `columns` argument instead:

`=TAKE(B2:F5,,2)` To remove the first two rows of array B2 through F5, use DROP with the `rows` argument and this formula:

`=DROP(B2:F5,2)` To remove the first two columns in that same array, use the `columns` argument instead and this formula:

`=DROP(B2:F5,,2)` ### Keep a Certain 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,...)` Y `CHOOSECOLS(array, column_num1, column_num2,...)` where the first two arguments are required. You can add more row and column numbers if you want.

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

`=CHOOSEROWS(B2:F5,2,4)` To return columns 3 and 5 of 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 matrix, No for the blade

### Expand an array to specific dimensions

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

RELATED: How to Add and Change Cell Borders in Excel

The syntax of the function is `EXPAND(array, rows, columns, pad)` where is missing `rows` either `columns` argument means they will not be expanded. Optionally, you can include the `pad` value for empty cells.

To expand matrix B2 through F5 to cover 10 rows and 10 columns, you would use this formula:

`=EXPAND(B2:F5,10,10)` To expand that same array to the same dimensions and include the `pad` “Empty”, use this formula:

`=EXPAND(B2:F5,10,10,"empty")` Advice: Although the `pad` The argument is optional, you may prefer it to seeing an error as shown above.

These 11 functions give you more control than ever over your arrays in Microsoft Excel. Give them a try and see if they accomplish what you need.

RELATED: How to fix common formula errors in Microsoft Excel

Stay Connected
16,985Fans
2,458Followers
61,453Subscribers

Related News