WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
30 years in business
Wise Owl Training
30 years in business
See 519 reviews for our classroom and online training
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:

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:

CHOOSEROWS function

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:

CHOOSECOLS example

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

 

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

Nested CHOOSEROWS and CHOOSECOLS

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:

Extracting a block

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:

Dropping data

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:

Example of EXPAND function

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:

Examples of TOROW / 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:

Example of WRAPROWS

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:

Example of VSTACK

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:

Example of HSTACK

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

This blog has 0 threads Add post