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