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.
Featured Course
Master NEW Excel Functions in Office 365 & Office 2021
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, “ ”) )
Featured Course
Excel Essentials for the Real World
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)
Featured Course
Power Excel Bundle
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 3rd, 8th, and 15th columns to work with, you can use the CHOOSECOLS function to extract those specific columns into a smaller table.
Below we have a 3-column table, but we only need the 1st and 3rd 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 1st, 4th, and 8th 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 1st and 3rd 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.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
