# Exploring 14 new Excel functions: Part 2

In this second of a two-part article, Excel MVP Liam Bastick introduces you to new functions that shape and resize arrays.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:

**TOCOL:**Converts a two-dimensional array into a single column (list) of data.**TOROW:**Converts a two-dimensional array into a single row (list) of data.**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.**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:

**scan by column:**This is optional and sets the scan of the array by column. However, by default, the**array**is scanned by row.

It should be noted that:

- Excel returns a
**#VALUE!**error when an**array**constant contains one or more numbers that are not a whole number. - Excel returns a
**#NUM!**error when**array**becomes too large.

Here are some examples:

**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:

**CHOOSECOLS:**Returns the specified rows from an array.**CHOOSEROWS:**Returns the specified columns from an array.**DROP:**Drops rows or columns from an array start or end.**EXPAND:**Expands an array to the specified dimensions.**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: