if you find yourself in front of a computer looking at spreadsheets often these 20 must know
Excel shortcuts I will hopefully save you hours of time let's go and thank you to HubSpot for
sponsoring this video more on them later alright so here's the Excel file that we'll be working
with which you can download for free in the video description below so let's get started with number
20. as you can see over here in the table we have all of this data down below which when you start
to scroll down you can actually no longer see the header so we're not really quite sure what this is
referring to and same thing with all of these other ones and so what we can do here instead
is actually just do a shortcut which is going to freeze that top row now to do this you're gonna
press alt wfr and now when we start to scroll down you'll notice that the top line is frozen
which makes things a lot easier for us if you ever want to remove that you can always go over
here under free screens in the view option just click on it and for from here just hit unfreeze
panes and that's going to remove that for us next up at number 19 we have the control arrow keys so
as you can see over here to navigate across this data set because it's very long you can actually
press the shortcut control and then just use the arrows so control down arrow to get all the way
to the bottom control up arrow and right and left to go to the sides similarly you can do Ctrl shift
down arrow and Ctrl shift right that basically allows you to select certain areas if you only
want to select column B and C just stop pressing the control and just go shift right arrow and you
can see that allows you to do that let's go back up to the top of the data set to look at number
18 so control up Arrow all the way to the top and over here you can see that we've got talk
about sales and let's say we want to continue this on now to do so you can just press Ctrl R that's
basically going to copy what you had towards the left and similarly you can go to control D that's
going to drag everything that you had above down one going back down towards the bottom so Ctrl
down arrow let's suppose over here that we want to add some new rows and so instead of actually
going going ahead and typing everything in again what you can do is hit the alt down arrow that's
basically going to show you the drop down or down arrow of all of the things that you already had
above and so it's somewhat faster for you to just paste it in there at number 16 we have selecting a
row or a column now to do so all you need to do is hover over a specific area and go to shift space
and similarly for the column it's just going to be controlled space like so now that on its own just
isn't very useful so that's when the next trick comes in and combined it can be quite handy so
again we'll do the shift space and now if you want to add the row you just need to go to Ctrl shift
Plus again Ctrl shift plus and to remove some you have to go to control minus and Ctrl minus
similarly for columns you can go control space and then Ctrl shift Plus and control minus now what if
we just want to move a specific row up a bit now to do so we'll go to shift space again and instead
of having to add new rows Etc we can just press the shift key and when you hover over the area
you'll see that the cursor changes to these arrows and from there you just want to click and drag it
let's say we drag it up over here and you can see how that's moved it all up similarly we can take
it back down so shift and then just drag it all the way down over here say to undo that we're just
going to press Ctrl Z and Ctrl Z again now let's go all the way back up so control up arrow and you
can see that beverage brand here let's say that we think it's a bit too long and so we want to
space it out a bit maybe put it into a different line now to do so we're just gonna get inside of
it over here now what we're gonna press is the Alt Enter and hit enter again and now you can see how
it's spaced it out across two lines but it stayed within that same cell coming in at number 12 we've
got adding comments so let's suppose that the interns worked on this file but we don't really
trust him so we just want to make sure that this number does make sense now one way to let him know
is to go hover over the cell you're not sure about and just hit the shift F2 now you can see that
this pop-up is going to show up with your name and then over here let's say can you check this
awesome now you can see that it's left this red sign which when you hover over it's gonna show
you the actual comment as we've seen here this data set is not so small so suppose we want to
reach a row 100 we can hover over there which might be a bit tedious but the shortcut there
is just clicking the Ctrl G that's going to show you the go to pop-up So within this pop-up the
reference that we want is we want to go to row 100 right so it's we're just going to put a 100 that's
going to take us to that specific cell so we'll hit OK there and you'll notice that it's taken us
exactly there right here and speaking of shortcuts a great way to save time is with templates like
the ones HubSpot the sponsor of this video is kindly providing Us for free using the link in
the description below you can get multiple free Excel graph templates within the download you'll
find an Excel file with instructions on using the template alongside all of the chart types
you might need to visualize your data on top of that you can easily modify the data and the charts
will automatically change these templates have either one column of data or multiple depending
on your needs I personally find this useful when deciding which chart showcases my data best as I
can see multiple graphs at the same time and see which one looks better so if you want to
check these out go to the link in the description below where you can download these free templates
from HubSpot to level up your Excel game alright back to the video great that's the first part done
and now let's get into the top 10 so we'll just go to the next tab by going to control page down
from here you can see that we've got the salesperson alongside some missing data
here for the first name and the last name now to fill this in we can go one by one so hit Max there
and then Charles and so on or the shortcut here is just to go to control e and then you can see that
it's going to fill all of these in in a heartbeat same thing goes with the last name see verstappen
and again Ctrl e if we look over to the side where it says total sales here let's say that
we want to find out the total of that now to do so let's just go up towards the bottom here and
all we need to do is set the alt equals that's going to sum up all of the totals and just hit
enter just like that we have the full number next up at number eight suppose we want to copy the
whole sale amount and paste it somewhere else so we'll go to control up here and all we're going
to do is control space that's going to copy that whole column for us then we'll go Ctrl C let's say
we want to paste it over here under column O say Ctrl V but the problem is that it all goes to zero
and that's because it was actually a formula so beforehand it was actually calculating the total
sales which was the price times the unit sold but now because it's not referencing that anymore
it no longer works so instead what we're gonna do is copy it again so go to control space and
then Ctrl C and this time instead of just Ctrl V to paste we're gonna press the control alt V
and you can see that this is going to show this paste special dialog box and within it you can
just paste the formulas the formats Etc in our case we want to paste the values that's what's
going to give us the actual numbers so we just click on that and hit OK and now you can see
that it's based at the values and if we look inside of them they no longer have the formula
either at number seven we've got formatting shortcuts so suppose over here that we want
to change this from a dollar sign to a pound so a British pound to do so we'll go to control one
and this is going to open the format cell dialog box and within it we want to go under currencies
and we want to change this to a pound so this one is the one that works for us and
let's say we also want to add one decimal place and basically that would do it for us but as you
can see within format cells you can also change a lot of other things like the font The Fill
Etc and so we go for an orange fill color as well to identify the pound signs hit OK there
awesome now you can see that it's all reformatted and now if you want to replicate this this
um this step what you can do is actually press the F4 key which is another shortcut which is
basically going to replicate the previous step so it's going to keep that same um the number
that was there originally but it's just going to change the formatting all right now moving into
the top five and over here let's just go back to this side of the data set and within the state of
all of these here you'll notice that Texas has a typo where it's got two x's now to get rid of that
instead of going one by one which could be quite tedious especially if you have a long data set you
just gotta go to control h which is the replace feature defined and replace so we wanna find the
textures with two x's so Texas there with the two x's and we want to replace that with a regular
Texas as it should be spelled correctly then from here we're just going to go to replace all
and it's gonna say okay we made six Replacements hit okay there and we're gonna close out of that
now that we've fixed that let's say that we just want to filter by Texas so instead of going ahead
and having to apply a full filter all we need to do is just right click on Texas go under filter
there and we want to filter by the selected cells value so just click on that and from here you can
see that we have all of the Texas State's ones that's been filtered like so now if you want to
go to the filter the usual way you would just have to go to the Home tab from here under sort
and filter and go to filter there let's just remove the filter for now another great way to
filter is using slicers now to do so first we're going to convert this into a table so go to Ctrl t
and then it's going to select the data set make sure it's all correct there and hit OK
now from here what we're gonna do is under table design we're gonna go to insert slicer so just
click on that then we want a slicer specifically for the states this is going to allow us to select
different states so hit OK there and so if we want to select Texas again all we need to do
is click on Texas and that's going to filter it for us same thing goes with any other state
similarly if you want to select more than one you've got to go to the stick sign here
and from there let's say I want to select Florida and California as well
in number two we have a common issue that people encounter which let's say first let's
delete the state here and let's suppose that we want to find out the top three units sold
so we'll just type that here top three units sold and so for the top one it's simply going
to be the max function which is going to take the highest one right so we're just going to go Max
hit the top key and we're just going to select all of these here so go to control shift and
down arrow to the very bottom and hit enter there but for the second and third we can
no longer use the max function so instead there's what's known as the large function
hit the top key there and the array is that same area so Ctrl shift down
comma and the key here is the actual ranking so we want the second highest so it's going to be the
two close those brackets and hit enter and for the third we're just gonna press the F4 key which is
gonna copy that same action and then within it we just got to change the number from A3 from a 2 to
a three sorry and just like that we have the top three and finally as the number one let's suppose
that it was our intern that sent us this file and we don't really trust him so we just want to make
sure that the formulas all make sense now to do so what we can do is actually hit the control in this
accent key of sorts it's basically going to allow us to see all of the formulas and where they're
going now if you want to do a bit more than just that and see where exactly they're going to what
we can do is hit the alt t u t which is going to allow us to trace the Precedence so basically
allows us to see where the formula is actually coming from same thing if you hover over any other
formula and go to alt Tut that's going to allow us to do that same thing if you ever want to remove
these arrows you just need to go to the formulas tab up over here and then go to remove arrows for
more on Excel check out this video over here to learn some awesome Excel tricks for this link over
here to take our Excel course hit the like and that subscribe and I'll catch you in the next one