14 FRESH FUNCTIONS Excel Adds New Capabilities to Help Save You Time.

• Author: Lenning, Jeff

Microsoft recently introduced 14 new functions for Excel--they are sure to help you save some time. To start, see the sidebar for a short description of each and then let's jump into some demonstrations.

These functions are being rolled out over time, so depending on your version of Excel and when you're reading this, your Excel may or may not have them. The fastest way to determine if your version of Excel supports them is to navigate to an empty cell and type =VS. If the VSTACK function appears in the auto-complete, you have them all! But, even if you don't have access to these functions right now, you will definitely want to read about them so you are ready when your Excel gets them!

Note: As of the time of this writing, the new functions are available to Microsoft 365 subscribers on the Beta update channel for Windows (version 2203, Build 15104.20004 or later) and Excel for Mac (Version 16.60, Build 22030400 or later). Typically, new enhancements and functions like this do not retroactively get added back to perpetual license versions (the versions that you buy once and run forever, ie, that are not sold as a subscription).

Demonstrations

You'll notice that many of these functions have a relative that does the opposite. For example, TOROW is the opposite of WRAPROWS. TAKE is the opposite of DROP. And TEXTSPLIT is the opposite ofTEXTJOIN (which we've had in Excel for a while now).

Some functions perform the operation on rows and their counterpart operates on columns, such as TOROW and TOCOL.

Some functions also have a buddy they will often work with (in the same formula) to achieve the desired result. For example, when two tables have a different column order, you can write a formula that uses CHOOSECOLS to get them lined up and VSTACK to combine them.

Now, let's walk through the functions. I'll keep this discussion at a summary level but note that these functions have additional, optional arguments that extend the capability beyond what's presented below.

TEXTSPLIT

TEXTSPLIT splits text into as many columns as needed based on the delimiters. For example, let's say we have a column with the lull name as seen in Figure I. We can easily split it into separate last- and first-name columns by writing the following formula into B2:

=TEXTSPLIT(A2,",")

We fill it down and we gel what you see in Figure 2.

Note: Historically, we could accomplish this task with the text-to-eolumns feature.

14 New Kids on the Excel Block

TEXTSPLIT: splits text into...