Today is an exciting day. So, Excel recently got a
set of amazing new functions for everyday users, these are formulas just as easy and great as the
UNIQUE and SORT functions or FILTER and XLOOKUP. So, functions that we can all easily use without
thinking too much. These functions today are really for everyone. For example, with TEXTSPLIT you
can easily split your text to multiple cells, so no matter how many spaces you have in
that text you can forget about left right mid functions and that's not all. You get
access to many other useful functions, but before we jump in, remember
that it's going to take some time for these functions to get rolled out to Office
365 users. Check the description of this video for an update on availability. Now, I can't wait
to show these to you so let's just jump in. Let's start with the amazing TOCOL and TOROW functions. So here, I have names in multiple columns and I want to combine everything into
a single column. I can easily do that with a new TOCOL function. I'm just going to select my
Array, that's it! Close the Bracket, press Enter, and I have everything spilled in a single column.
Now imagine the possibilities you have right now, you could get a unique list of values
by putting these in the UNIQUE function. The TOROW function is very similar to this,
except that you get your values spilled into the rows. So, if I go with TOROW here, select my range,
close Bracket, press Enter, I get everything spilled horizontally. Now, again you can put this in the
UNIQUE function as well, but this time,we just need to specify that our values are in column. So,
we have to put True here, close bracket, press Enter, and we get a horizontal unique list. Next up, we
have the TEXTSPLIT function. So, here I have a list of full names and I want to split them into
separate columns. I'm just going to start typing TEXTSPLIT and we have this amazing function which
you can do a lot with and I'm going to create a separate video on this, but here's the simplest
use. You select your text and then you define your delimiter, basically based on what do you want
the TEXTSPLIT. In this case based on a space, I'm going to put it in quotation marks and that's it! Check this out, I press Enter, I get Walter, Tobias Miller, split into three separate columns. Let's
drag this down and I have my names split properly. Now, we also get two other similar functions that
help us work easier with text. We have TEXTAFTER and TEXTBEFORE. So, let's go with TEXTBEFORE, if
this is my text and I say the delimiter is a space, what do I get? I get the first name, so by default,
it gives you the first instance but if you want everything before the second space, you can go with
a 2 here. I'll just put this back to a 1 and drag this down. So, this is similar to the LEFT function
except that it's a bit more flexible because it allows you to define your delimiter. We also get
TEXTAFTER, here this is our text and after a space and let's say after the first instance, we get
everything after the first name. If I change this to 2, it's everything after the second instance. Now,
of course you can use a function to get this to be dynamic or use the IFERROR function to avoid
errors like this. Next up, we have VSTACK and HSTACK, these allow you to append your data together. Here
I have two separate ranges for name and salary, now these could be in separate sheets and I want
to append them on top of one another. I'm going to use VSTACK for vertical stacking. Array 1 is
this and Array 2 is this, you can of course add multiple arrays. I'm going to press Enter and I get
all of them together, so imagine the possibilities. If this was a table, I'm just going to press Ctrl + T, convert it to a table, and this was a table as well and I end up with a new name here. Check what
happens to my results, they update automatically. What does each stack do? Well it does a similar
thing except it spills the data horizontally. So, I'm just going to press
Ctrl + Z and remove these names and let's do it right here. Start off with HSTACK,
Array 1. Let's use our tables now, is this 1? Array 2, Is this 1? close bracket, press Enter
and we get them appended beside one another. Next up, we have WRAPROWS and WRAPCOLS. So, I
have my data here in a single row and I want to put these below one another. So, this should come
below the names and this should come below that. I can easily do that with the new WRAPROWS
function. You define your vector, in this case it's this range and then the WRAPCOUNT, so
from where do you want it to start wrapping? Well after the third value here, so put a 3,
close bracket, press Enter and I get my data wrapped properly. How does it look with wrap
columns? Well it does a similar thing, we're going to select our range and then decide the WRAPCOUNT
for our column. So, if I select 3 here, my names are gonna end up in the same column like
this. Next up, we have the TAKE and DROP functions. With these functions, you get to keep or drop
the parts of your data set that you don't want. So, here for example, let's say I just want to
keep the top three rows. I'm going to start off with the TAKE function, define my array, and for
rows just put in a 3, close bracket, press Enter, and I get the first three rows. If I want the last
three rows, I'm going to put a minus 3. What if I just wanna keep two columns? I'm going to add a 2
for the column, press Enter, and I get to keep the first two columns. This data isn't sorted, so let's
say I want to get the values based on the top three salaries. Well, that's not a problem because
I can put my range inside the SORT function. That's what I want to sort, I want to sort this based on
the salary column which is in the third column and I want it in descending order. I'm going to close
the bracket, press Enter, and I get the records with the top three salaries. If I only want to keep
the two columns, put a 2 here and we're done. What about DROP? Well, DROP works in a similar way
except that you get to drop whatever you define. If I select my range here and say I want to drop
3, it's going to drop the first three rows. If I say minus 3, it's going to drop the last
three rows and of course you can combine this with other functions like we did with TAKE.
Next, we have CHOOSEROWS and CHOOSECOLS. So, we CHOOSECOLS, which is probably going to
be the more commonly used one, you get to define your array and then the columns that you want
to keep. So, you can define them based on an index number. Let's say I want to keep column one and
column three only because I just want the names and salaries back. I'll put a 1 and a 3,
press Enter, and I get names and salaries back. Now, you can probably picture the potential of this,
what if I didn't want everything returned but only the salaries that are above a hundred thousand?
Well, I could combine this with a FILTER function. So, for my range here, I'm going to get it filtered
before I choose what to return. This is my array, what I want included are salaries that are greater
than a hundred thousand now close the bracket for the FILTER function and press Enter, and I get
to choose what I want returned, we CHOOSECOLS. Now, how does CHOOSEROWS work? Well, it works
in a similar way. You get to define your array, and then you get to define the rows that you want
to keep. So, let's say I want to keep the first row, the fifth row, and the sixth row, close bracket
press Enter, and that's what I get. Last, we have the EXPAND function. With EXPAND, you get to expand your range and add additional columns or rows. So, for example here, I have department and name, I want to add an extra column for salary, and I want that populated with the word "missing." So, I'm going to
start off with the EXPAND function, select my range here. For rows, I want it to be as many rows as I
have, so I'm just going to skip that argument. For columns, I want to add an extra column, I already
have two columns I'm going to put a 3, then if I don't put anything for the "pad_with", close the
bracket, press Enter, I get an extra column appended but with errors. I don't want errors, instead I
want the word "missing". So, I'm going to add "missing" in quotation marks for the last argument. When I
press Enter, I get missing there. If you wanted to add extra rows to this, so I have three currently,
so let's say I go with 4 and I press Enter, I get "missing" on the bottom here as well. In this
case I don't want, I'm just going to remove that and Enter. Now, I'm sure this is going to come in handy
when you want your ranges to be of the same size inside other functions that work with arrays. So,
just to demonstrate, a simple example based on what we have. If I want to stack this on top of another
one and I still want "missing" there, I'm just going to wrap this in the VSTACK function. That's my
Array1 and my Array2. Let's just jump quickly to another sheet, this is the TAKE sheet. I'm going
to select this range, close bracket, press Enter, and I get my results organized in a nice way. That
was a quick introduction to the new functions and I really want to know what your favorite
one is, so please share in the comments below. I have to say for me, right now, my favorite one
is probably TEXTSPLIT, let me know yours. That was it for today, thank you for watching
and I'm going to see you in the next video.
