Exploring 14 new Excel functions: Part 2

You can't accuse Microsoft of not thinking big with its upcoming introduction of 14 new Excel functions.

The new batch of functions is so large that we are covering them in a two-part article. Part 1 looks at the functions that either manipulate text or combine arrays. Part 2 explores new functions that shape arrays or resize them.

As a reminder, these functions are far from generally available. In fact, at the time of this writing, these functions are currently available only to users running Beta Channel, Version 2203 (Build 15104.20004) or later on Windows and Version 16.60 (Build 22030400) or later on Mac.

Shaping arrays

Changing the "shape" of data in Excel, especially from arrays to lists and vice versa, is a popular request with our clients and difficult to achieve formulaically. (Power Query makes it nice and easy though!) This is where the next four functions come into play:

1. TOCOL: Converts a two-dimensional array into a single column (list) of data.
2. TOROW: Converts a two-dimensional array into a single row (list) of data.
3. WRAPCOLS: Creates a two-dimensional array of a specified height by wrapping data from a column (list) of data once the prescribed height is achieved.
4. WRAPROWS: Creates a two-dimensional array of a specified width by wrapping data from a row (list) of data once the prescribed width is achieved.

The TOCOL function

The TOCOL function returns a column vector containing all of the items in the source array. It has the following syntax:

TOCOL(array, [ignore], [scan by column])

The TOCOL function has the following arguments:

• array: This is required and denotes the array or reference to return as a column.
• ignore: This is optional and identifies whether to ignore certain types of values; by default, no values are ignored. The omissions are governed as follows:

The TOROW function

The TOROW function is essentially the same as the TOCOL function except it returns a row vector, instead of a column vector, containing all of the items in the source array. It has the following syntax:

TOROW(array, [ignore], [scan by column])

The TOROW function's arguments are the same as the TOCOL function's arguments except that the array argument denotes the array or reference to return as a row instead of as a column.

Here are some illustrations:

The WRAPCOLS function

The WRAPCOLS function wraps the provided vector by columns after a specified number of elements. It has the following syntax:

WRAPCOLS(vector, wrap count, [pad with])

The WRAPCOLS function has the following arguments:

• vector: This is required and denotes the row or column vector/reference to wrap.
• wrap count: This is also required and represents the maximum number of values (depth/height) for each column.
• pad with: This is optional and defines the value with which to pad. The default is N/A.

It should be noted that:

• The elements of the vector are placed into a two-dimensional array by column.
• Each column has wrap count elements.
• The column is padded with pad width if there are insufficient elements to fill it.
• If wrap count is greater than or equal to the number of elements in vector, then the vector is simply returned as the column vector result of the function.
• Excel returns a #VALUE! error when vector is not a one-dimensional array.
• Excel returns a #VALUE! error when wrap count is less than one [1] or is not an integer.

Please see the following examples:

The WRAPROWS function

• The WRAPROWS function has the same syntax, arguments, and notes as WRAPCOLS except for the following (differences shown in italics):
• The wrap count argument represents the maximum number of values (width) for each row.
• The elements of the vector are placed into a two-dimensional array by row.
• Each row has wrap count elements.
• The row is padded with pad width if there are insufficient elements to fill it.
• If wrap count is greater than or equal to the number of elements in vector, then the vector is simply returned as the row vector result of the function.

More examples:

Resizing arrays

The final array manipulation covered by this myriad of new functions concerns resizing. This is where the last five functions prove useful:

1. CHOOSECOLS: Returns the specified rows from an array.
2. CHOOSEROWS: Returns the specified columns from an array.
3. DROP: Drops rows or columns from an array start or end.
4. EXPAND: Expands an array to the specified dimensions.
5. TAKE: Returns rows or columns from the start or end of an array.

The CHOOSECOLS function

The CHOOSECOLS function returns the specified columns from an array. It has the following syntax:

CHOOSECOLS(array, column number 1, [column number 2, …])

The CHOOSECOLS function has the following arguments:

• array: This is required and represents the selected array.
• column number 1: This is also required and denotes the column number of the first column to be returned.
• column number 2: This and subsequent arguments are optional. This/these represent(s) the second and subsequent column numbers to be returned.

It should be noted that Excel will return a #VALUE! error if the absolute value of any of the column number arguments is zero or exceeds the number of columns in the array.

Some examples:

The CHOOSEROWS function

The CHOOSEROWS function returns the specified rows from an array. It has the following syntax:

CHOOSEROWS(array, row number 1, [row number 2, …])

The CHOOSEROWS function has the following arguments:

• array: This is required and represents the selected array.
• row number 1: This is also required and denotes the row number of the first row to be returned.
• row number 2: This and subsequent arguments are optional. This/these represent(s) the second and subsequent row numbers to be returned.

It should be noted that Excel will return a #VALUE! error if the absolute value of any of the row number arguments is zero or exceeds the number of rows in the array.

Illustrations:

The DROP function

The DROP function excludes a specified number of contiguous rows or columns from either the start or the end of an array. It has the following syntax:

DROP(array, rows, [columns])

The DROP function has the following arguments:

• array: This is required and represents the selected array from which to drop the rows or columns.
• rows: This is also required and denotes the number of rows to drop (exclude) from the top. If this number is negative, the values drop from the bottom of the array.
• columns: This is optional and denotes the number of columns to drop (exclude). If this number is negative, the values drop from the end of the array.

It should be noted that:

• When rows or columns are not provided or missing, all rows and columns are returned.
• If the absolute value of rows or columns is greater than the number of rows or columns in the array, then all rows or columns are supposed to be returned, but presently #VALUE! appears to be the favoured treatment.
• Excel returns a #CALC! error to indicate an empty array when rows or columns is zero [0].
• Excel returns a #NUM! error when array is too large.

Please see the examples below:

The EXPAND function

The EXPAND function expands (or pads) an array to specified row and column dimensions. It has the following syntax:

EXPAND(array, rows, [columns], [pad with])

The EXPAND function has the following arguments:

• array: This is required and represents the selected array to be expanded.
• rows: This is also required and denotes the number of rows in the expanded array. If this argument is missing (not bad for a required argument!), rows will not be expanded.
• columns: This is optional and denotes the number of columns in the expanded array. Again, should columns not be specified, this dimension will not be expanded.
• pad with: This is an optional value with which to pad. The default is N/A.

It should be noted that:

• If rows isn't provided or is empty, the default value is the number of rows in the array argument (as aforementioned).
• If columns isn't provided or is empty, the default value is the number of columns in the array argument.
• If pad with is not provided and array has one value for that dimension, then that value is used. This operation is commonly referred to as array "broadcasting"; however, this does not appear to work presently.
• Excel returns a #VALUE! error when the rows or columns argument is less than the rows or columns in the array argument.
• Excel returns an #N/A error when pad with is greater than a single column or row.
• Excel returns a #NUM! error when array is too large.

Please see our penultimate examples below: 

The TAKE function

The TAKE function returns a specified number of contiguous rows or columns from either the start or the end of an array. It has the following syntax:

TAKE(array, rows, [columns])

The TAKE function has the following arguments:

• array: This is required and represents the selected array from which to take (extract) the rows or columns.
• rows: This is also required and denotes the number of rows to take from the top. If this number is negative, the values are taken from the bottom of the array.
• columns: This is optional and denotes the number of columns to take. If this number is negative, the values take from the end of the array also.

It should be noted that:

• When rows or columns are not provided or missing, all rows and columns are returned.
• If the absolute value of rows or columns is greater than the number of rows or columns in the array, then all rows or columns are supposed to be returned, but presently #VALUE! appears to be the favoured treatment.
• Excel returns a #CALC! error to indicate an empty array when rows or columns is zero [0].
• Excel returns a #NUM! error when array is too large.

Please see the final examples below:

Word to the wise

These functions are "hot off the press" and presently in beta, so it is possible they may change and/or their behaviour may be modified. This should not deter you from trying these out. Compared to the recent onslaught of LET and LAMBDA related functions, the concepts in play are reasonably simple to understand and could prove highly useful for those looking to work with arrays more in Excel.

Bring on the next batch!

— Liam Bastick, FCMA, CGMA, FCA, is director of SumProduct, a global consultancy specialising in Excel training. He is also an Excel MVP (as appointed by Microsoft) and author of Introduction to Financial Modelling. Send ideas for future Excel-related articles to him at liam.bastick@sumproduct.com. To comment on this article or to suggest an idea for another article, contact Jeff Drew at Jeff.Drew@aicpa-cima.com.

The WRAPROWS function

• The WRAPROWS function has the same syntax, arguments, and notes as WRAPCOLS except for the following (differences shown in italics):
• The wrap count argument represents the maximum number of values (width) for each row.

• The elements of the vector are placed into a two-dimensional array by row.
• Each row has wrap count elements.
• The row is padded with pad width if there are insufficient elements to fill it.
• If wrap count is greater than or equal to the number of elements in vector, then the vector is simply returned as the row vector result of the function.

More examples: