Microsoft recently released 11 new Excel functions for shaping arrays (data).
I already covered VSTACK and HSTACK which was super popular, and in this post I’m going to use some of the other new functions to do things that used to require ninja level function wrangling.
You can see the individual tutorials covered in the video or read about them at the links below.
Note: these functions are only available to Microsoft 365 users.
Download Cheat Sheet and Workbook
Enter your email address below to download the files.
Watch the Video
|Expands or pads an array to a specified number of rows and columns.
|Returns the array in a single row. Useful for combining data across multiple columns and rows into a single row.
|Returns the array in a single column. Useful for combining data across multiple columns and rows into a single column.
|Lets you wrap (reshape) a row or column of values into rows, you specify the number of values in each row.
|Lets you wrap (reshape) a row or column of values into columns, you specify the number of values in each column.
|Remove a specified number of contiguous rows or columns from the start or end of an array.
|Extract a specified number of contiguous rows or columns from the start or end of an array.
|Extract rows from the specified column or columns.
|Extract columns from the specified rows or rows.
|Combine arrays arranged vertically (VSTACK) into a new single array.
|Combine arrays arranged horizontally (HSTACK) into a new single array.
Nested Array Shaping Functions
Individually the new array shaping functions are amazing, but team them up and you can create some super useful formulas.
In the table below I have a list of postcodes and the corresponding suburbs:
Notice how I used CHOOSEROWS to return the list of postcodes twice, hence the 1,1 in the CHOOSEROWS formula:
Handling Missing Data
If there’s an uneven number of suburbs for each postcode you end up with blanks in the result:
We can filter out the blanks with the FILTER function:
FILTER checks for blanks by repeating the second array in HSTACK returned by TOCOL: