Leila Gharani explains the new Excel functions for you
Mar 27, 2022 3:32 PM

Leila Gharani explains the new Excel functions for you

by HubSite 365 about Leila Gharani [MVP]

Pro UserExcelLearning Selection

The Microsoft Excel team has announced Excel functions (dynamic array functions) that save us from writing complex Excel formulas. These help Excel beginners, advanced users and professionals.

With TEXTSPLIT we can split text based on multiple delimiters (e.g. multiple spaces). With TEXTBEFORE we can capture the part of the text before a delimiter and with TEXTAFTER the part after a delimiter.

With TOROW and TOCOLS we can combine data from several areas into a single row or a single column. Together with the UNIQUE function, you can get a unique list of values from several columns.

CHOOSECOLS and CHOOSEROWS are also available to select which columns and rows to return from a range. An excellent use case is to combine CHOOSECOLS with the FILTER function to return columns from different ranges.

With WRAPCOLS and WRAPROWS you can take horizontal or vertical data and create multiple columns or multiple rows from it. With the TAKE & DROP functions you can select which part of a table you want to return. You can keep the first top rows or the last rows. You can combine this with the SORT function to get the best results from your data set.

Finally, the EXPAND function allows you to expand a range to a specified number of columns or rows. This is particularly useful for calculations where several ranges need to be the same size.