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