8 Awesome New Excel Formulas for 2023 | Do you know them?

in this video we'll go over 8 awesome new  features in Excel you probably didn't know   so let's get into it first up in number one  we've got the v-stock formula which allows   us to stack multiple tables into one so you can  see over here in this Excel file which you can   download for free in the video description  you can see that we have two stories one in   San Francisco and one in La we would like to  compile them together into our total inventory   so for this we can just use the v-stack function  so equals V stock hit the top key there and this   is going to be the first array this whole area so  Ctrl shift down there and Ctrl shift right comma   and then we'll select the second area again Ctrl  shift down Ctrl shift right close up parenthesis   and hit enter and you can see now it's merged  into one table similar to a v-stack which is   for vertical you can also use an H stack which is  for horizontal so over here to the side I could do   equals H stack and then I'm just gonna select the  exact same areas to show you quickly here and this   one as well and basically it's just gonna stack  them right next to each other obviously here it's   not all that useful next up number two we've got  the tech split which is fairly self-explanatory as   you can see over here we have a table with the  name and we want to split this into the first   name and the last name so we're gonna use the text  split function for that and we'll go equals text   split hit the top key there and so this is the  text that we're interested in and the column   delimiter here is what we want it to be separated  by so in our case hit the comma there to activate   it we want to separate it just by a space sign  so we're just going to put it in quotations space   quotations then we can simply close a parenthesis  and hit enter and you can see that we now have   split into first and last name similarly there  instead of splitting it by column we could split   it by separate rows so I could do something like  equals text split and this is the same text comma   and suppose instead of a column delimiter we want  it by row so we'll hit the comma again and then   here we're gonna do the same thing so quotations  space quotations and close-up parenthesis and hit   enter now you can see that it's splitted by row  instead of by column now in number three what if   there's more than two words of text then the text  before or the text after can't come handy so you   can see over here suppose that we want to extract  the ID number so this first part and the price but   we have this EU thing in the middle which we don't  quite want that's when we might be able to use   the text before here for the ID number so we'll go  equals text before this is a text for us comma and   again the separator is going to be the comma there  in this case so we'll put it in quotations comma   closer quotations close a parenthesis and hit  enter there and you can see there it's managed   to separate that same thing goes with the price  This Time It's For text after hit the top key   this is a text for us comma the delimiter in this  case is going to be an underscore so we'll put it   in quotations again underscore quotations close  up parenthesis and hit enter and now to drag this   down all across we can simply select these two  and then just drag it down here from the edge all   the way to the bottom there moving on to number  four and now we've got the image function which   allows us to bring any image from the internet  and import it into a specific cell on Excel so   if we look over here let's suppose that we want  to have the flag of each country so for that in   this case let's suppose we want to fill in France  so we'll go equals image hit the top key there and   then here we want to try to find the source for  the image so we would have to go to the Internet   for that let me just open up a browser and you can  see here that I've looked up the France flag and   I have it right here all I need to do is right  click and go to copy image address once I have   that I can go back to the Excel file and then as  a source we need to put it in quotations then I'm   gonna paste it by hitting Ctrl V and then close  the quotations and close the parenthesis and just   hit enter there and you can see that I now have  the flag of France in there and what's nice about   this method is that it stays within the cell so  you can't easily move it or change the size unless   you do the same thing for the row or column and  if you're liking this video and you want to level   up your Excel skills you can consider checking  out our Excel for business and finance course   and what makes this course different is that it's  all applied to the real world while we still cover   theoretical lessons like formatting formulas and  charts we also offer case studies that simulate   the type of work you might be assigned in your  day-to-day ranging from Financial modeling to   cleaning a data set and presenting some visual  insights and if you get stuck along the way you   can always ask us the course instructors questions  in the discussions forum we also offer several   other courses including power bi VBA and macros  and more so if you're interested in checking it   out head over to the link in the description below  alright back to the video moving up to number five   and we're still with images but this time let's  suppose that we've been given an image like this   so it's actually an image of a table and so it's  a bit tedious if we have to go line by line type   it into Excel but luckily there is a solution  here and for that we can head over to data   and then under get and transform data we  want to go from picture and picture from file   in this case I have the image right here so I'm  simply going to hit on insert you can see that   it's going to start loading it over on the side as  you can see here we can either review the data or   we can simply hit on insert so I'm just going to  go to insert anyway and you can see that just like   that it's been able to add all of the data for  us that said it does lose the formatting but it's   still better than nothing number six we've got the  two call function which is actually quite similar   to the v-stock so let's take a look over here  let's suppose that we have employees in London and   in New York and we want to put them all together  under all staff so for that we can go to equals and we simply want to select this whole area so  Ctrl shift down Ctrl shift right and just hit on   enter and you can see that it's created the whole  list in one column similar to the two call you   also have the two row so let's take a look over  here we'll go equals to row hit the top key and   it's going to be the same area there and just hit  enter and you can see that this time around it's   all in one row all right next up in number seven  we've got the take and the drop functions which   allow us to select specific areas of a table so if  we look over here you can see that we have the top   three brands by unit sold which is what we want  to try to get and the bottom three brands and we   want to do that from this data set over here which  as you can see is sorted from highest to lowest by   unit sold so to do this for the top three brands  let's go ahead and try the take function so we'll   go equals take hit the top key there and the array  is this whole area so Ctrl shift down Ctrl shift   right comma then the rows is going to be top three  rows so we're gonna select three comma and then   for the columns we want two columns the brand and  the units so we're just gonna put a two and close   our parenthesis and hit enter so you can see that  we have three rows but they're actually these two   columns B and C but rather we're interested in D  and E so to change that we can actually go back   inside and instead of starting over here we can  start with a minus tool so it's gonna go from here   and just hit enter there and now it switched it  up to Coca-Cola which is the brand and the units   opposite to the take we have the drop function  which makes sense for finding the bottom three   for example so over here you can see that we have  a total of 13 rows and from there we want to try   to get the bottom three so it's just gonna be the  last three in here so for that we're gonna go to   equals drop hit the top key there and you can see  the syntax is very similar so we're just gonna   select this whole area Ctrl shift down Ctrl shift  right comma and for the rows this time we want   it to start at row 10 because everything below is  going to get dropped which is what we want to find   so we'll put a 10 in there comma and the columns  this time it works the opposite to the take so   we're just gonna put a 2 instead of a minus two  close-up parenthesis and hit enter there and it's   basically saying to drop these three over here  which makes sense as they're the lowest in units   sold now I realized that that drop function isn't  all that intuitive so make sure you practice it   a bit more to get comfortable finally number  eight we have the expand function which makes   sense when you have a data set that's going  to continue to grow in values so let's take   a look at an example so it's a bit clearer  you can see over here that we have this row   set of data which is basically from January  all the way to July for some sales figures   and so we also have a summary this could be  in a new tab for example and then here we   have data from January all the way to December  so it doesn't actually fill everything in here   that said we can still make it Dynamic using  the expand function so we'll go equals expand   hit the top key there and the array is going  to be this area so just these first two columns   hit the comma key there then for the rows  we want it to go down by 12 rows because   we have 12 months of data comma and then for the  columns we don't need anything in there so we're   just going to hit a comma again and finally this  pad width is whatever we want to put if there is   no value so it would be nice to have something  like an A sign just gonna put it in quotations   in there close that and hit enter and you can  see that now it's gone ahead and added all the   values except that from August to December  where there are none it's stayed as an n a   then from here suppose that we do get August  figures because they get updated and we add 500   and 500 in here you can see that that's going to  update dynamically for us the reason for this is   because one this is a table now to make something  a table you would just have to hit on Ctrl T from   there it's all going to update dynamically because  of the expand function for more on Excel check out   this video over here to learn how to make an  awesome dashboard or this link over here to   take our Excel course hit the like and that  subscribe and I'll catch you in the next one

Channel: Kenji Explains

Views: 117,153

Keywords: excel formulas, excel functions, new excel functions, new excel formulas, vstack excel, hstack excel, expand function excel, expand formula excel, textsplit formula, textbefore formula, textafter formula, tocol, throw, take excel, drop excel, image to table excel, image function excel, textsplit function excel, textbefore function excel, textafter function excel, new excel formulas for 2023, new excel functions for 2023, best formulas excel, best excel functions for 2023

Id: 4LD8_hcsP_Y

Length: 11min 44sec (704 seconds)

Published: Sun Jun 18 2023