Breaking News! We FINALLY get the New Excel functions we've been Wanting!

Video Statistics and Information

Video
Captions Word Cloud
Reddit Comments
Captions
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.
Info
Channel: Leila Gharani
Views: 812,233
Rating: undefined out of 5
Keywords: XelplusVis, Leila Gharani, Advanced Excel tricks, Excel online course, Excel tips and tricks, Excel for analysts, Microsoft Excel tutorials, Microsoft Excel, Excel 2016, Excel 2013, Excel 2019, XelPlus, Microsoft 365, Excel 365, office365, office 365, new excel function, excel textsplit, excel textbefore, excel textafter, excel torows, excel tocols, excel choosecols, excel chooserows, excel expand, excel take, excel drop, excel new functions, excel new formulas
Id: VWcLM6_Q_00
Channel Id: undefined
Length: 10min 51sec (651 seconds)
Published: Thu Mar 17 2022
Related Videos
Note
Please note that this website is currently a work in progress! Lots of interesting data and statistics to come.