14 Fresh Functions

Microsoft recently introduced 14 new functions for Excel—they are sure to help you save some time. We’ll start with a short description of each and then jump into some demonstrations.

1. TEXTSPLIT: splits text into columns and/or rows based on one or more delimiters.
2. TEXTBEFORE: returns the text before the specified delimiter.
3. TEXTAFTER: returns the text after the specified delimiter.
4. VSTACK: combines multiple tables into one table by stacking them vertically.
5. HSTACK: combines multiple tables into one table by stacking them horizontally.
6. TOROW: converts a table into a single row.
7. TOCOL: converts a table into a single column.
8. WRAPROWS: converts a single column (or row) into a table, going across then down.
9. WRAPCOLS: converts a single column (or row) into a table, going down then across.
10. TAKE: returns only the specified number of rows (or columns) from a table (beginning or end).
11. DROP: returns all but the specified number of rows (or columns) from a table (beginning or end).
12. CHOOSEROWS: returns the selected row or rows from a table (in any order).’
13. CHOOSECOLS: returns the selected column or columns from a table (in any order).
14. EXPAND: expands a table to a specified number of rows and columns.

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 of TEXTJOIN (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 full name as seen in Figure 1.

Figure 1

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 get what you see in Figure 2.

Figure 2

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

TEXTBEFORE

TEXTBEFORE is similar to TEXTSPLIT, except that it only returns the text before the delimiter. In the example above, it would return the last name.

Note: historically, we could accomplish this task by nesting LEFT and FIND.

TEXTAFTER

TEXTAFTER is similar to TEXTSPLIT, except that it only returns the text found after the delimiter. In the example above, it would return the first name.

Note: historically, we could accomplish this by nesting RIGHT, LEN and FIND.

VSTACK

VSTACK combines multiple tables by stacking them vertically. For example, we have two separate tables of data. One for January and another for February. If the tables are named “Jan” and “Feb,” we can combine them by writing the following formula into E1:

=VSTACK(Jan[#All], Feb)

We hit Enter and you get what’s seen in Figure 3.

Figure 3

This works on both tables and ordinary ranges.

Note: historically, we could accomplish this with a manual copy/paste or Power Query.

HSTACK

HSTACK is similar to VSTACK, except that it combines the tables side-by-side, horizontally.

TOROW

TOROW converts a table of values into a single row. For example, in Figure 4 is a simple table of four values.

Figure 4

We can use the TOROW function to convert these values into a single row, like in Figure 5.

Figure 5

If the table had duplicate values, you could include the UNIQUE function in your formula to remove duplicates. If you wanted the resulting values to be ordered, you could include the SORT function as well.

TOCOL

TOCOL is similar to TOROW, but it converts the table into a single column.

WRAPROWS

This function converts a single row (or column) of values into a table of a specified number of columns. This is essentially the opposite of TOROW. WRAPROWS builds the table across, and then down. For example, it can help us convert the single list of values we saw in Figure 5 into a table like Figure 6 (fills it left to right, then down).

Figure 6

WRAPCOLS

This function is similar to WRAPROWS, except that it builds the table down and then across (fills it top to bottom, then to the right). It can convert the same single list of values from Figure 5 into a table like seen in Figure 7.

Figure 7

TAKE

This function retrieves the first or last number of rows (or columns) from a table. This is useful if we just want the header rows, or perhaps the total row.
If the table contained a bunch of transactions, and we wanted to retrieve the most recent, we could use TAKE to retrieve the last row. Or, if we kept adding new months as new columns, we could use TAKE to return the most current month (ie, the last column).

DROP

This function is essentially the opposite of TAKE. Whereas TAKE retrieves the first or last rows/columns, DROP retrieves everything but the first or last rows/columns. For example, if we wanted to retrieve only the data rows, we could use DROP to remove the header row and the total row.

CHOOSECOLS

This function allows you to pick and choose which table columns you want to return and in which order. In practice, this function can help align columns in multiple tables that you want to combine with VSTACK. For example, let’s say our January table has columns in this order: “ID,” “Period,” “Amount.” But the February table columns are in a different order: “ID,” “Amount,” “Period.” We could write the following formula in E1:

=VSTACK(Jan[#All], CHOOSECOLS(Feb,1,3,2))

And, bam, Figure 8.

Figure 8

Pretty cool!

CHOOSEROWS

This function is the counterpart to CHOOSECOLS and does the same thing for rows.

EXPAND

This function enables you to increase the size of a table by adding additional rows or columns. In practice, this is likely to be used as an intermediate helper function.

Conclusion

This new set of functions helps us manipulate text and reshape data with formulas. Any solutions we create with them are dynamic and will automatically update as dependent values change. Nice! They have more arguments and options than what I’ve discussed, so you’ll definitely want to explore them further.

These functions add to the arsenal of Excel tools that can help us get our work done faster. And remember, Excel rules!