in this video we'll go over 10 excel tricks to make you an expert level excel user and these
are going to be ranked in terms of expertise from one star all the way to five stars so let's see
how many you know starting off with level one difficulty and first up we have free spans and
you can download the excel file we'll be working with in the description let's say we've got this
database over here that we plan to send to the management team and so if you keep scrolling
down you'll notice that you can't see the headers anymore so this area up over here
and so by the time you're down over here you don't really know what this actually stands for
and that's when freeze panes comes handy so let's go all the way back up here from here we're just
going to go under view and here you're going to start to see that there's the freeze panes option
and you can see that under it you've got you've got a few different options so freezing the top
row which doesn't really work for us because we actually have the second row that we want
or freezing the first column so instead what we're gonna do is click on this split here
now you'll see that you get these gray bars and so this one's the one that you want to take
say all the way up over here for us and for the columns we don't actually want one so we'll take
it all the way out kind of like so from here once we have the area we want selected we're just going
to go on the free spins and click on freeze panes now you can see that even though we keep scrolling
down that header is still going to remain there now if you want to take this out you can just go
under freeze panes and unfreeze panes and then click on that split and now that's all gone
similar to freeze panes another way you can make people's lives easier is through grouping
so for example if you go to the second tab by pressing control page down you can see that
up over here we've got an income statement that's actually forecasted into the future
and just below that you have all of the different assumptions for it for these assumptions suppose
you don't necessarily want to show all of this area as that's not really relevant right you want
people to focus on the income statement and after that if they want to look through the assumptions
they can but it's going to look a bit daunting if it's this long so instead what we can do is select
these three areas over here you can press the shift space and then shift down arrow to select
these three we're just gonna have one for revenue one one for cogs and one for operating expenses
now you could go ahead right click and hide this but the problem with that is that it's not very
obvious that there's actually something hidden in there so if you don't know you're probably
not going to be able to spot it so press ctrl z to go back you can also delete these but the problem
is that the whole file is linked to it that's probably not a good idea press ctrl z there
instead what we're gonna do is to group them so for this you can just go under data
then here all the way in the end you're gonna find the grouping option click on that now you can see
that you get this kind of drop down and now it's very obvious that there's something hidden in
there and so you can go ahead and click on it and close it the shortcut for this if we select these
other ones so press the shift space again and then shift down arrow it's just going to be the alt
shift and then right and left to group or to ungroup you can see there that i want grouped
and then to ungroup you press the left arrow same thing down over here so ctrl shift shift
down arrow and then alt shift and let's press the right key that's going to group it for us
now you can go ahead and close these and close this one as well this looks a lot less daunting
moving on to level two and suppose we've been working on a financial model for weeks now and
we're really happy with how it's looking go to control page down over here and this is the one
that we've been working on and down over here we have the assumptions but recently we just hired
an intern they're very unpredictable and they've actually gone ahead and deleted some of these rows
before so we want to make sure that this doesn't happen again so they don't break the model like
you can see over here press ctrl z there to go back so for this to protect the sheet you
can actually just go to review and down over here you're gonna have the option to protect the sheet
so click on that and from here you can either put a password if you wanted to or you can simply
have what they can do or they can't do so in our case we really don't want them to do anything so
you just have that they can select the log cells they can select the unlocked cells but they can't
actually type anything in there so now press ok and suppose the internet has gone into our
computer and he wants to try to delete some of these so we'll press the delete key now we get
this pop-up that says that you can't make these changes hit okay there and so if you ever want to
unprotect the sheet you can just press unprotect and now you can make all the changes that you want
continuing on in level 2 with the wild intern if you go to control page down over here you've
got the different car sales and the commission that each sales person is getting for it so you
can see over here towards this other table you basically have the total payout for each of these
salespersons so over here you've got jane look and you can see that because that person's made these
two sales the total amount is going to be over here and so the wild intern is actually tasked
with writing the person's name down over here but the problem is that if they're not careful suppose
they type anna with two with two ends over here then that name is not gonna be reflected over here
and so anna's obviously going to be very angry if she doesn't get the commission that she deserves
to work around that you can use data validation so suppose we go down over here
and from there we're gonna go under data click on this icon over here that's data validation
and this is gonna allow us to create a drop down list of sorts so we'll create a list there and
the source is gonna be all of the different sales people as if it's something that's not within that
range then it's probably just a type or it's wrong somewhere and so now you can see that we've got
ana which is not going to be allowed suppose we go for joe and now let's say i type ana with two ends
kind of like so you'll notice that it says hey that doesn't match and so that's not allowed
to be in there this way we can make sure that this person this intern is going to be putting
the right person's name in there moving on to level three and here we've got sparklines
these make the most sense when you want to showcase a trend in a small amount of space
looking at the excel file go to control page down over here you can see that you have the sales by
country and you have the respective amount amounts for each month within that 2021 year
and so if you want to see the trend like see how spain's done relative to the trend let's say
in portugal for instance it's really hard to tell because of it's just numbers right there's no
trend line of sorts so quick and easy way to go about this is just go under insert then towards
the very end you're to find the sparklines option let's say we go with a line so click on that
and so the data range we want is going to be this whole range over here so for spain it's going to
be all of this so ctrl shift and then right arrow that's going to take you all the way to the end
the location so where you want the actual line to be placed is going to be an o right here hit okay
there now you can see that there's a whole trend line that showcases how it's been doing throughout
the whole year if you want to make it a bit wider you can just go under sparkline color underweight
let's say we put one and a half here then we can just go ahead and drag this down all the way to
the bottom here let's say the total is included as well now you can really start to tell what the
trend has been like as you can see spain actually seems very similar to what portugal has been like
and as for totals we've actually probably not progressed too much from january all the way
to december the growth seems to have been more or less the same this trend line is really the same
concept as a line chart except that it occupies a lot less space and if you don't know many of these
tricks but you want to learn more you can consider taking our excel for business and finance course
where we teach everything we know about excel specifically for people either looking to break
into a business or a finance role or those in it trying to level up their excel game unlike most
theoretical courses we try to make this one as practical as possible so aside from the typical
lessons on formatting formulas and charts we have case studies that much like this video replicate
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 we also have two real life excel interview tests
to help you in the recruitment process so if you're interested in checking it out go to
the link in the description below alright back to excel tricks continuing with level 3 and let's now
look at naming cells so go to control page down over here and don't worry too much about this data
other than we need to calculate the profit over here so to calculate the profit it's just revenue
minus the cost so you can go equals revenue minus the cost like so now when you look into
this formula by pressing the f2 key it's obviously not very intuitive what i3 means or what j3 means
instead what you can do is go ahead and name these cells so let's go over here press the ctrl shift
down arrow and these are all going to be revenue cells so what we want to do is name them as such
so you go under formulas then you want to define a name so that's how you'll create a name for it
the name excel is smart enough to know the it says revenue so it's going to be revenue for us
and we'll hit ok there and we want to do the same thing over here for the cost so
ctrl shift down arrow we'll define the name and we'll name it a cost hit ok there and so with
this you can just go to equals we're going to type revenue now press the tab key minus cost
press the tab key again and hit enter now when you look inside the formula it says revenue minus cost
which is obviously a lot more intuitive now this is just a small example but if you have
a very complex formula having this kind of naming is gonna make your life a lot easier
all right moving on to level four and now we're getting into serious territory for this we'll
start off with data types so go to control page down and more specifically with stock data so
over here you can see that you've got all of these different companies that suppose we want to invest
in and so we want to have this kind of different information on them ticket symbol the price the
52-week high and the 52-week low intuitively you might think of going to yahoo finance or
to bloomberg and trying to find these numbers but there is an easier way within excel for this first
let's select them so go to ctrl shift down arrow to select all of these under data you're gonna go
to stocks under data types over here hit stocks and now you can see that all of these stocks come
up and you can see that they have some kind of an icon over here if you click inside it you're
gonna get all sorts of information on the company that's actually pulled from the internet so press
ctrl shift down arrow to select all of these and we want to add all of these things we mentioned
so click inside this um small icon and from here we're going to want first we set the ticker so
let's try find it ticker symbol hit enter there similarly we said we wanted the price so we'll
just type price down over here 52 week high and low so that should be over here first the high
and then we'll select the low and it's not easy to find the stock data for a company within excel
next up in level 4 we have flash fill and this works best with data that has similar patterns
so if you go to control page down over here you'll notice that we have the first name of a person
alongside the last name and so we want to fill in all of this data so i'll type bill here then
i'll type smith the initials is just bs and then next to that it's their email that's bill.smith
at apple.com and let's say that they're all actually apple employees over here so for this you
can actually go one by one which is obviously a very tedious process or you can simply select that
first guy and press ctrl e ctrl e ctrl e and ctrl e and excel is actually smart enough to realize
that hey this is what you're actually trying to do and it does that accordingly now in this case i
did emails but the same thing applies to something like country codes on phone numbers zip codes etc
all right finally we're in level five and these are gonna be the coolest tricks yet first off
we've got text to columns so go to control page down over here and you can see that you've got
this raw data suppose it came from our database management software or something like that where
everything is separated by these underscores and over here we want to separate that into id number
first name last name the product and the price now to do so going one by one would be extremely
tedious you would have to copy the name here and then paste it over there etc so instead what we
can use is text to columns firstly we're going to go ctrl shift down arrow to select the whole area
from here we're going to go to the data tab go under text to columns
and i know this looks a bit daunting don't worry too much just put the limit delimiter for now hit
next and here under the delimiters is where we want to put another and under the other we're
going to put the underscore sign which is how we want to separate things if you go under the
data preview below press the underscore there you can see that it's now going to be all separated
hit next and here as a destination this is basically where you want the output to be
in our case we want it to be right over here that's going to be a starting one it backed into
that and now we're just gonna go to finish great now you can see that everything is being separated
just in a matter of seconds and finally let's look into the hardest trick which are excel macros
so go to control page down for this one and suppose that every week we're given this
original sales data for all of the weekdays this case say it's week 21 and we just got this data
now every time we need to do the same repetitive task which is to format this so make it look a bit
more presentable to them be able to send to the management team so suppose we need to change a bit
of the borders here make them in blue the totals we should bold in them and then just below that we
usually put the weekly average and then the weekly total so once the average and the r1 would be the
sum now to do this if you do it every week this is something that you can actually automate
where excel is going to record all of the steps that you take and then it's going to be able to
apply them in a different table so let's go ahead and go do that you can do that under the developer
tab up top if you don't find this one you can just go into any tab and then go to right click
from here you're gonna go under customize the ribbon and you're gonna wanna tick on developer
down over here i already have that ticked so i don't need to press the escape key there
so under developer we want to go to record macro once we hit record then it's going to record all
of the different steps that we take click on record there and for the macro name let's call
it something like sales and the shortcut key here let's put it ctrl j for instance
hit ok there and now it's actually recording you can see that the very top part here where it says
to stop recording now we want to do all of the different steps that we need to format so let's
select this whole area here let's go under alt h and we're going to put it in dark blue color
what i did is just select this area here with a shortcut hit hit that one and then alt h f c
that's gonna be for the actual um color of the of the text so let's go with white ctrl b that's
gonna bold in it for us then over here on the bottom side press ctrl shift and then
right arrow then press ctrl b that's gonna bold in it and then alt h b c that's gonna give us a
border of sorts as you can see over here great now down below we said we wanted to have the weekly
average and the weekly total for these the average is just the average function press the top key
when you find it and we'll select this whole range over here hit enter there and for the weekly total
it's the same idea so we'll go equals sum that's going to sum everything for us and we'll select
this area over here and hit enter let's say those are all the steps that we want to take and that
those are going to be the steps that we take for all the next weeks as well so for this we'll just
go up back to developer and say stop recording great now if you go to control page down we've
got the next week's data week 22 and so we want to essentially reformat it in the same way so we can
send to the management team now we just got to go under macros and from here you've got this macro
called sales and that's the one we're going to want to run so hit run there now you can see that
everything is actually just being created in a matter of seconds if you click inside the average
you can see that it's actually the same formula we used earlier and all the formats are correct
one final thing to note about macros is that you can't save them as regular excel files instead you
have to save them as a macro enabled work file for this you can simply go under file go under save as
and here are the options so there's the excel workbook which is the most typical one
and the one right below is the excel macro-enabled workbook which is the one that we want to be using
and that's the hardest trick we'll look at out of curiosity let me know down in the comments out
of 10 how many you knew and if you're looking to test your excel skills check out this link
over here for an excel interview test or this other link over here to learn more about excel
hit that like and that subscribe if you liked it and i'll catch you in the next one