# New Excel Functions We Always Wanted

It can be easy for many Excel users to feel overwhelmed when writing formulas.

Finding the right combination of tools, constructed in the correct way to yield the desired result.

If you’re a data-manipulating guru, this comes easily to you. If you’re part of the other 95% of Excel users, you pine for easier ways to solve complex problems.

*Your day of data-crunching salvation has arrived!*

Microsoft has introduced 14 new functions to Excel that are aimed at easing the pain of conjuring complex formulas to solve what would seem like simple tasks.

A bit over a year ago, Microsoft introduced a cadre of new functions aimed at making data manipulation easier. Functions like **UNIQUE**, **FILTER**, and **SORT** to name a few.

These functions were so positively embraced by Excel users that Microsoft has decided to roll out another batch of equally amazing functions.

But before you fire up Excel and start enjoying the benefits of these new functions, understand that these new functions are being released to users in phases.

What that means is that developers, testers, and early adopters will receive these new functions before the public receives them. It may be a few months before they appear in your function library. Office 365 users along with Excel for the Web users will receive the new functions over the next several months.

Let’s explore some of these amazing new functions that will no doubt change the way you use Excel.

# The TOROW & TOCOL Functions

We begin with data that is stored in an array.

We need to get the list of names in a single column. This can be accomplished with the **TOCOL** function.

The only thing we need to do is point **TOCOL** to the array of names.

=TOCOL(A4:C6)

Imagine the possibilities of this function when teamed up with other functions like **UNIQUE** and **SORT**.

Remove the duplicate names using the **UNIQUE** function.

=UNIQUE(TOCOL(A4:C6) )

Sort the results of the deduplicated list using the **SORT** function.

=SORT(UNIQUE(TOCOL(A4:C6) ) )

If you need the list of names spilled across a row instead of spilled down a column, you can use the **TOROW** in the same ways.

=TOROW(A4:C6)

Removal of duplicates and sorting works the same way.

=SORT(UNIQUE(TOROW(A4:C6),TRUE),,,TRUE)

You may have noticed that deduplicating and sorting data by rows requires a few extra arguments. This is because the **UNIQUE** and **SORT** functions default to a column-based analysis and we need to inform them that they need to examine the data in a row-based analysis.

# The TEXTSPLIT, TEXTBEFORE, & TEXTAFTER Functions

The **TEXTSPLIT**, **TEXTBEFORE**, and **TEXTAFTER** functions are more modern methods of parsing text previously accomplished using functions like **LEFT**, **MID**, and **RIGHT**.

## TEXTSPLIT Function

If we have a list of full names and we need to separate the names into columns…

…we can write the following formula using the **TEXTSPLIT** function. We just point to the cell holding the name and provide the delimiter we need to look for that separates each name within the full name, like a space character.

=TEXTSPLIT(A4, “ ”)

## TEXTBEFORE & TEXTAFTER Functions

Another way to parse the full names would be to extract everything __before__ a specific character or everything __after__ a specific character.

If we wanted only the first names, we could ask to extract all text before the first encountered space.

=TEXTBEFORE(B4, “ ”)

Likewise, if we wanted everything __after__ the first name, we could ask to extract all text after the first encountered space.

=TEXTAFTER(A4, “ ”)

If you only wanted the last names, you could instruct **TEXTAFTER** to perform the extraction after the second encountered space.

=TEXTAFTER(A4, “ ”, 2)

We can see from the results above that this could be problematic when dealing with a mixture of two and three-part names.

With a bit of help from the **IFERROR** function, we can perform two different **TEXTAFTER** functions based on whether the first attempt results in an error.

=IFERROR(TEXTAFTER(A4, “ ”, 2), TEXTAFTER(A4, “ ”) )

# The VSTACK & HSTACK Functions

**VSTACK** and **HSTACK** (*“V” for vertical and “H” for horizontal*) allow us to append lists or tables to one another, creating a single, unified list.

Take the two lists of names and salaries below.

The lists are proper Excel Tables named “**First_Table**” and “**Second_Table**”.

We want to stack the lists atop one another to create a single list that could be used for things like sorting, filtering, and perhaps even charting.

Using the **VSTACK** function, our formula looks like the following:

=VSTACK(First_Table, Second_Table)

Imagine if the tables were on separate sheets or even in separate files. How cool is that? You wouldn’t need to use more complex tools (*like Power Query*) to arrive at the same result.

Because the source tables are proper Excel Tables, when new content is added to the source tables, the combined table updates automatically.

The formula-based solution refreshes automatically, whereas you would need to manually refresh the results when using Power Query.

Likely to be a lesser-used version of **VSTACK** is **HSTACK**. This appends the tables side-to-side instead of atop one another.

=HSTACK(First_Table, Second_Table)

With the help of the **IFNA** function, we can rid ourselves of those pesky errors when tables don’t match in height.

=IFNA(HSTACK(First_Table, Second_Table), “”)

# The WRAPROWS & WRAPCOLUMNS Functions

Previously, we saw how we could take an array of data and convert it to a single column or single row of data.

What if you need to perform the exact opposite operation, taking a row (*or column*) of data and converting it into an array?

We can use the **WRAPROWS** function to limit the number of items each row can contain. The result will be a series of rows with no more than N values per row.

=WRAPROWS(A3:I3, 3)

If you need the results to be spread in a column fashion instead or a row fashion, you can use the **WRAPCOLS** function in the same way.

=WRAPCOLS(A3:I3, 3)

# The TAKE and DROP Functions

The **TAKE** and **DROP** functions allow you to keep or drop portions of a data set that you want or don’t want.

(*If you understood that sentence, you’re likely to be classified as an advanced form of life.*)

Using the following data…

Suppose we only want to keep the __first three rows__ of the table. We can use the **TAKE** function to write the following formula:

=TAKE(A4:C12, 3)

If we want the __last three rows__, we will use a __negative value__ in our selection argument.

=TAKE(A4:C12, -3)

If we only wanted the __first three rows__ and the __first two columns__, we could modify the formula like so.

=TAKE(A4:C12, 3, 2)

If we wanted the __last two columns__, we could write the formula like so.

=TAKE(A4:C12, 3, -2)

## Returning the TOP 3 Rows Based on Highest Salaries

In the previous examples, the source tables were not sorted.

If we wanted to find the top three rows based on the highest salaries, we could use the **SORT** function to sort the table before extracting the rows.

=TAKE(SORT(A4:C12, 3, -1), 3)

The **DROP** function works in the opposite manner. We define a number of rows, and **DROP** throws those rows away leaving you with whatever remains.

=DROP(A4:C12, 3)

*NOTE: All the same strategies apply to DROP as applied to TAKE in terms of column selection and sort integration.*

# The CHOOSECOLS & CHOOSEROWS Functions

The **CHOOSECOLS** and **CHOOSEROWS** functions allow you to extract subsets of rows or columns from a larger table.

For example, suppose you were given a table with 20 columns, but you only need the __3 ^{rd}__,

__8__, and

^{th}__15__columns to work with, you can use the

^{th}**CHOOSECOLS**function to extract those specific columns into a smaller table.

Below we have a 3-column table, but we only need the 1^{st} and 3^{rd} columns for our analysis.

The formula that uses the **CHOOSECOLS** function looks like so:

=CHOOSECOLS(A4:C12, 1, 3)

The **CHOOSEROWS** works in the same manner.

If we wanted to extract the 1^{st}, 4^{th}, and 8^{th} rows from a range, the formula would look like so:

=CHOOSEROWS(A4:C12, 1, 4, 8)

## Combining Column Selection with Row Filtering

What if you wanted a table that contains only the 1^{st} and 3^{rd} columns but also only those rows where salaries are greater than $100,000?

We can combine the **CHOOSECOLS** function with the **FILTER** function.

=CHOOSECOLS(FILTER(A4:C12, C4:C12 > 100000), 1, 3)

We could even throw in a bit of **SORT** action to sort the results by salary, highest to lowest.

=SORT(CHOOSECOLS(FILTER(A4:C12, C4:C12 > 100000), 1, 3), 2, -1)

# The EXPAND Function

The **EXPAND** function allows you to expand the range of a data set, adding rows and/or columns.

In our example below, we have a list of departments and names that need an additional column of salaries.

The following formula that uses the **EXPAND** function can select the range of source data, define a 3-column output structure, and fill the empty column with the text “missing”.

=EXPAND(A4:B6, , 3, "missing")

If you wanted additional rows in the output, you could add a row argument to define the number of additional rows.

=EXPAND(A4:B6, 6, 3, "missing")

If you’re wondering why you would ever need to do this, think back to the **VSTACK** function.

Suppose you have two tables with a differing number of columns. If you need to stack them atop one another and ensure that the column count matches, you could write the following.

=VSTACK(First_Table, EXPAND(Second_Table, , 3, "missing") )

## Practice Workbook

Feel free to Download the Workbook HERE.