This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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! Fourteen new functions coming to Excel New text functions (TEXTBEFORE, TEXTAFTER, TEXTSPLIT) New functions for array manipulation (this blog)

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!

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

Posted by Andy Brown on 20 April 2022

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).