WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
Wise Owl Training
See 519 reviews for our classroom and online training
If you found this blog useful and youâ€™d like to say thanks you can click here to make a contribution. Thanks for looking at our blogs!

BLOGS BY TOPIC

BLOGS BY AUTHOR

BLOGS BY YEAR

Fourteen new functions coming to Excel
Part three of a three-part series of blogs

Get a preview of the 3 text functions and 11 array functions coming to your version of Excel very soon!

1. Fourteen new functions coming to Excel
2. New text functions (TEXTBEFORE, TEXTAFTER, TEXTSPLIT)
3. New functions for array manipulation (this blog)

Posted by Andy Brown on 20 April 2022

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

New functions for array manipulation

There are 11 new functions in Excel for manipulating arrays, all illustrated using the current state (at the time of writing) of the Northwest Counties Division 5 Central South squash league:

The range A1:F15 shown has been given the name League.

Picking out certain rows and columns using CHOOSEROWS / CHOOSECOLS

You can use CHOOSEROWS  to pick out only certain rows:

You can specify one or more rows to pick out. This function will show the first row (the title row), and rows 4 and 5.

Or CHOOSECOLS to pick out only certain columns:

This would give the second and third column of the range only.

You can nest these functions to get exactly what you want:

This formula would pick out rows 1, 4 and 5, and columns 2 and 3.

Picking or dropping blocks of rows or columns using TAKE / DROP

You can use the TAKE or DROP function to do this.  Here's an example of choosing rows/columns to keep:

This function picks out the first 5 rows and 3 columns from the block of data (if you omit the number of columns you would get all of them).

You could achieve the same effect by dropping the last 10 rows and last 4 columns:

If you put negative numbers in, Excel will start dropping from the bottom/right rather than the default top/left.

Expanding blocks of data using EXPAND

If you want to make ranges bigger you can expand them to fill a certain range (optionally specifying what to use as padding).  Here's an example:

This formula takes an array which is 3 rows high and 2 columns wide, and expands it to fill an array 4 rows high by 4 columns wide, putting XXX (rather than the default #N/A) in any unused cells.

The number of rows and columns must each be at least as big as the respective number of rows and columns in the original range.

Resizing ranges using TOROW / TOCOL / WRAPROWS / WRAPCOLS

This is sometimes called broadcasting.  You can flatten arrays to a single row or single column using TOROW or TOCOL:

The first formula collapses the mini-league to a single row; the second to a single column.

Both TOROW and TOCOL take three arguments, of which only the first is compulsory:

Argument Name Type Contains
1 array Compulsory The block of cells you're collapsing into one dimension.
2 ignore Optional Whether to ignore certain types of values (see table below - by default Excel includes all values).
3 scan_by_column Optional Whether to scan across and then down (the default, as above) or down and then across.

The possible things you can ignore are as follows:

Value Ignore
0 Include everything (the default)
1 Ignore blank cells
2 Ignore errors
3 Ignore blank cells and errors

The opposite of collapsing down to a single row/column is to expand a single row or column into a two-dimensional array using WRAPROWS or WRAPCOLS:

This example creates as many rows as are needed to accommodate the 5 films in the given range, subject to having a maximum of 3 values in each row.

Both functions take up to three arguments:

Argument Name Type Contains
1  vector Compulsory The range (normally row or column) of cells you want to reshape.
2 wrap_count Compulsory The maximum number of values you can have in each row (WRAPROWS) or column (WRAPCOLS).
3 pad_with Optional What to put in any empty cells (this defaults to #N/A).

Combining ranges using VSTACK and HSTACK

You can join ranges together vertically:

This formula combines the two ranges, one underneath the other. Because they're not the same width, any blank cells are filled with #N/A.

Or horizontally:

Exactly the same formula, but stacking across rather than down.