Top New Excel Functions in Office 365 (VSTACK, TEXTSPLIT, TOCOL, DROP, WRAPCOLS, CHOOSECOLS, TAKE)
Microsoft has just added 14 new Functions that make sorting your data and organizing your information much easier.
Whether you’re trying to clean up a list or arrange your data better, these new tools are here to help, making those often complicated tasks a lot simpler.
Excel just got an upgrade with some handy new features that make organizing and working with your data a breeze.
Whether you’re sorting through text or managing lists, these updates are designed to simplify your tasks. Now, separating text and combining lists can be done easily, without needing to be an Excel expert.
Plus, these improvements are now available for Excel for the Web and Office 365 users. Let’s dive into what’s new and see how it can help you with your data.
Understanding TOCOL and TOROW: Enhancing Excel Data Layout
In Excel, you can now easily move your list items into a single column or a single row. If you have items spread out across many columns and want to put them all into one column, the TOCOL function does this for you.
And if you have a long list going down and prefer to see it all in one row, you can do that too with the Excel TOROW function. This makes your list easier to look at and work with, helping you find and organize your information the way you want.
Let’s look at an example. We begin with data that is stored in an array.
How to Use the TOCOL Function
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) ) )
How to Use the TOROW Function
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
Easier Text Manipulation in Excel: TEXTSPLIT, TEXTBEFORE, and TEXTAFTER
When you’re working with text in Excel, like breaking up a sentence into parts, it used to be a bit tricky. People often needed to use several complex steps using formulas like LEFT, MID, or RIGHT to get it done.
Now, with TEXTBEFORE and TEXTAFTER, it’s much simpler. Want the part of the text before a certain character, like everything before the “@” in an email? The TEXTBEFORE function can do that. Or the part after a character, like what comes after the “@”? The TEXTAFTER function is your go-to.
And for splitting text in Excel into different pieces, TEXTSPLIT makes it a breeze. Say you have a full name and want the first and last names in separate cells. The TEXTSPLIT function does this automatically, putting each piece in its own Excel cell easily.
These new tools are all about making your life easier and getting your work done faster, without needing to juggle complicated steps.
How to Use the 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, “ ”)
How to Use the TEXTBEFORE and 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
Streamlining Data with Excel’s VSTACK and HSTACK Functions
Excel’s got two new functions to help you organize your information better. If you’ve got lists that you want to put one after the other into a single, longer list, think of VSTACK (“V” for vertical) like stacking books on a shelf, one on top of the other.
And if you want to spread your lists out side by side, like laying out photos on a table, HSTACK (“H” for horizontal) is the way to go. These new functions in Excel are all about making your lists neat and easy to look at, without needing to cut and paste everything by hand.
How to Use the VSTACK Function
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.
How to Use the HSTACK Function
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), “”)
Organizing Data with WRAPROWS & WRAPCOLS Functions in Excel
In Excel, you now have a simple way to manage long lists or wide rows of data with WRAPROWS and WRAPCOLS.
Think of WRAPROWS as a way to take a long list and automatically divide it into shorter, equal sections, just like breaking a long sentence into lines to fit a page. It stops the list from getting too long by moving part of it to start again underneath, based on how many items you want in each row.
Similarly, WRAPCOLS lets you take a wide row of information and organize it into columns, so instead of stretching too far across, it starts a new column once it hits your set limit. It’s like taking a single, stretched-out line of text and arranging it into a neat paragraph that’s easier to read.
These tools are great for when you have too much information in a line or row and want to make it tidier and more manageable, automatically arranging your data into a clear, organized grid or table that fits your screen better.
How to Use the WRAPROWS function
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)
How to Use the WRAPCOLS Function
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 Bundle
Power Excel Bundle
Simplifying Data Management with TAKE and DROP Functions in Excel
Imagine you have a long shopping list. With TAKE, you can quickly pick just the first few items you want to buy today, or maybe the last few items if you’re planning for the end of the week.
Now, let’s say your list also has things you’ve already bought or don’t need anymore. DROP lets you easily remove those items from the top or bottom of your list, so you only see what’s left to buy.
These functions make looking at your lists much easier. You can focus on what’s important right now, without all the extra stuff in the way.
How to Use the TAKE Function
For our example, let’s use 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 with the TAKE function
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)
How to Use the DROP Function
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.
Choosing Specific Data with CHOOSECOLS & CHOOSEROWS Functions in Excel
CHOOSECOLS and CHOOSEROWS that let you quickly pick just the parts you need from a list or a chart.
Imagine you have a big table with lots of different information. With CHOOSECOLS, it’s like pointing to the columns that matter to you—maybe you only want to see what items cost and nothing else. It’s as if you’re choosing only the chapters you want to read in a book.
CHOOSEROWS does the same thing but with rows. Say you have a list with lots of dates and activities; you can select just the rows that tell you what’s happening on the weekend. It’s like picking out just the scenes you want to watch in a movie.
These functions help you zero in on what’s important, making big tables of information much easier to handle, like finding your favorite parts of a story.
How to Use the CHOOSECOLS Function
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.
Let’s look at an example. 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)
How to Use the CHOOSEROWS Function
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)
Expanding Data Views with the New EXPAND Function in Excel
EXPAND lets you add more blank spots to your list or table. Think of it like when you need more room on a piece of paper, so you stick another sheet next to it. EXPAND lets you do that with your data—add more empty rows or columns where you can put in more information later.
It’s perfect for when you start with a small list and then realize you need to add more to it, making it easy to grow your list without messing up what you already have.
How to Use the EXPAND Function
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.
If you have two tables and they don’t have the same number of columns but you want to put one on top of the other, there’s a way to do this. You can adjust them so they both have the same number of columns before stacking them together. This makes sure everything lines up neatly.
=VSTACK(First_Table, EXPAND(Second_Table, , 3, "missing") )
Practice Workbook
Feel free to Download the Workbook HERE.
Author: Editor
Views: 5
No responses yet