Array Manipulation

 

VSTACK  |  HSTACK  |  TOROW  |  TOCOL  |  WRAPROWS  |  WRAPCOLS  |  TAKE  |  CHOOSEROWS

Use the new array manipulation functions in Excel 365 to quickly combine, shape and resize arrays.

VSTACK

The VSTACK function in Excel 365 vertically stacks multiple arrays into a single array.

VSTACK function in Excel 365

Note: the VSTACK function, entered into cell B5, fills multiple cells. This behavior is called spilling.

HSTACK

The HSTACK function in Excel 365 horizontally stacks multiple arrays into a single array.

HSTACK function

TOROW

The TOROW function in Excel 365 converts a 2D array into a single row.

TOROW function in Excel 365

TOCOL

The TOCOL function in Excel 365 converts a 2D array into a single column.

TOCOL function

WRAPROWS

The WRAPROWS function converts a 1D array into a 2D array. WRAPROWS starts a new row after a specified number of elements (second argument).

WRAPROWS function in Excel 365

WRAPCOLS

The WRAPCOLS function converts a 1D array into a 2D array. WRAPCOLS starts a new column after a specified number of elements (second argument).

WRAPCOLS function

TAKE

The TAKE function extracts a subset of an array. You can start in each corner by using positive and negative arguments.

1. The TAKE function below extracts a 4 by 2 array (starting in the upper-left corner).

TAKE function

2. The TAKE function below extracts a 3 by 1 array (starting in the bottom-right corner).

TAKE function

CHOOSEROWS

The CHOOSEROWS function in Excel 365 returns specific rows from an array. The CHOOSEROWS function below returns the 3rd and the 5th row from an array.

CHOOSEROWS function

Note: use CHOOSECOLS to return specific columns from an array. For example, download the Excel file and try to return the 2nd column from this array. Practice makes perfect!