here's some of the shortcuts you're about to learn and we'll use this messy sheet on excel as
the example firstly we'll write the emails for each country manager and for that we can just
use the flash fill function secondly we're gonna refill everything so it's more pleasing to the eye
then thirdly we're gonna do an autosum to fill the totals for the year
and lastly we're gonna rearrange the titles a little bit so make them bold and put an
underlying border on it what's up everyone it's kenji here and today i thought i'd go over some
of the most useful excel shortcuts specifically they're going to be for people either in business
or finance fields whether that be as a student or a full-time working professional and you can
download the file i'll be working with for free as i'll link it in the description as a google sheets
now if you want to edit it you can go on file download microsoft excel so you can follow along
and if this video does well i'm considering doing a part two so make sure you hit that like and that
subscribe down below alright let's get into it and thank you to financial edge for sponsoring
a part of this video so the first type of shortcut we'll be looking at is navigation so moving around
data sets like this one we have here with the gdp growth of each country so to move to the bottom of
the data set instead of going one by one pressing the down arrow you can press ctrl down arrow
to go all the way down ctrl down arrow again to go all the way down to the end of excel control
up arrow to go all the way back up control right arrow to go right and left obviously and then
all the way back to the other side so the control key is going to explain the whole process for you
now if you want to navigate while selecting everything in the data set you can do ctrl shift
and then the arrow keys so arrow down arrow to the side then arrow down to get everything now if you
want to deselect a specific area just remove the control key so shift arrow and that should let
you select whatever area you want in particular now if you want to select a particular column
like this one with the country names you can do control space if you want to add a column to that
you just do control plus from there control plus again ctrl minus to delete some similarly if let's
say we want to add a row over this one firstly we'll select it by pressing shift space and then
ctrl plus to add some control plus control minus to delete some as well so if you want to hide a
particular column first you press ctrl space and from there you press ctrl 0 and that should hide
it now you can see between a and c the b is no longer there now if you look closely into the the
area and then double click it there you're going to see that it comes back out now the shortcut to
unhide the column is actually ctrl shift 0. i will say that sometimes that doesn't work if that's the
case you can try alt ocu and that should open it up for you so for rows it's quite a similar
process firstly to select it we'll press shift space and then from there you'll press the ctrl 9
and that should hide it for you you can see the number two is no longer there now if you want to
open it up you can obviously double click it again but the other way to go about it is to press ctrl
shift nine and you might wonder why it's necessary to be hiding rows or columns and that's a fair
point it usually has to do with assumptions for example if you're presenting to a management team
you don't necessarily want to be showing all of those assumptions that you've made you would just
hide them but do be careful with this especially if somebody new comes into a model and doesn't
know that there's hit themselves in there it can obviously be very frustrating for them instead
what i think is a better option is grouping so let's look into that so suppose we want to hide
the 2012 13 and 14 years so for that we'll select them by pressing ctrl space and then shift arrow
shift arrow again and now we'll press shift alt and then right arrow to make a group and you can
see that there's a selection up here now you can close it and open it and it's obviously a lot more
visible that there is something hidden in there as opposed to just hiding it without telling anyone
now in case you want to remove it for that you'll just press the alt shift and left arrow to remove
it alt shift right to add it and remove again another useful way to highlight an assumption is
to use the note feature so let's look into that the shortcut for that is actually just shift f2
and here you can write whatever you want obviously and you can see that it's going to remain there
in red it is noticeable but good enough that you can see the other data now if you want to navigate
between the different sheets you have down here you can press the control up and down lastly if
you want to copy a whole shield and let's say we want to double that navigation sheet you can press
the control and then just drag it along to the other side and there you go you have the double
all right so moving on to inputting data and we're going to be using this table over here
so that's the second sheet on inputs with some other products and some information about them
but first let's talk about the sponsor of today's video financial edge so if you're interested in
learning more about excel i definitely recommend you check out financial edge's certified online
finance courses they're the instructors that teach the new hires at the top four investment banks
so you can get the exact same training online at your own pace for example their business toolkit
course pack goes over all the relevant skills to become more efficient in the workplace using
microsoft office from financial modeling in excel all the way to creating professional decks in
powerpoint and presenting them effectively they've also got plenty of other relevant courses for
those interested in careers in business or finance like investment banking asset management private
equity and much more so if you're interested check out the link in the description below to get 25
off by using code kenji25 all right back to the video so starting off with the very basics if
you want to copy something ctrl c if you want to paste it ctrl v now if you want to copy this
entire table instead of just one cell what you can do is press ctrl a to select it all
if you press ctrl a again it selects the whole actual excel sheet but this case we just want
the table here so press ctrl a once then ctrl c and just ctrl v over here if you want to go
back you can just press the ctrl z button ctrl z again to delete all of that some of the other very
common ones include ctrl b which is just going to bold in something ctrl u to underline it and then
ctrl i to italicize you can press them again if you want to remove it or you can also press ctrl z
now sometimes when you copy and paste something you don't necessarily want the same formatting
so for that you can use the paste special so let's say we want to copy this revenue cell but we don't
want to copy the same formatting if you press ctrl v usually it's going to give you the same
formatting but we don't want that so let's press ctrl z for now and what we're going to do is press
the ctrl alt v instead and what you're going to see here is a paste special tab and in it you have
different pasting formats that you can use like formulas only values only or formats etc in this
case let's say we want the value and not the same format so we'll press the down arrow to the values
and press enter and now we don't have the blue formatting and instead we only have the values
in it so let's press ctrl z to go back now you can see that this one's selected just press escape if
you want to deselect it now to actually get into the cell let's say we want to write revenue total
instead of just revenue when i put total in there it deletes whatever i had previously which is
quite annoying press escape to take that out so instead what you want to do is press the f2 key
which is going to let you get inside the cell so revenue total we can just write it in here now
now if you press f2 again to navigate within the cell instead of having to press the arrow
keys like that you can press the control arrow to skip to specific words ctrl shift arrow to
actually highlight specific areas ctrl a to select everything and so on now moving on to the bottom
of the table here suppose we know that the next products are all going to be apple so we're going
to press the ctrl d once we're here and you can see that that just copies whatever you had above
so in mouse it's going to press mouse hong kong hong kong and so on that's the control d button
if you want to fill to the right instead so you can press the ctrl r hong kong hong kong hong kong
now suppose you want to write something longer for hong kong one thing you'll notice is that the cell
just continues on but what if you'd like it to be two lines within that same cell for that you'll
press the f2 key and then we'll press ctrl arrow to get over here and then you're gonna press the
alt enter that's gonna make it all within that one cell now moving on to formatting data and for that
go to the third sheet that's titled formatting and you can see that we have this whole table
here that's completely unformatted now one of the common ways to go about formatting is to press the
ctrl 1 key that's going to give you all of the different options that you have for formatting
from numbers to alignments to fonts and so on and so forth and you can press the control tab key to
navigate between them i personally don't really like to use this one so you can press the escape
what i prefer is to actually use the alt key so if you press alt once you'll notice that there's
all these letters that come up on the top of the ribbon so you press alt h and the more letters
come up but these are basically all the shortcuts that you can do so you can see that one is for the
b here for bold so you press one there and it's gonna make the company bold press ctrl z to remove
that so let's try to format this using the alt key and make it a bit prettier so for that we'll just
press the ctrl shift and right arrow to select the titles you can either press ctrl b which is the
shortcut we learned earlier for the bold but let's use the alt ones for now so we'll press the alt
h 1 and that's going to make it bold and you can see that there's actually many different ways to
go about shortcuts this is just one of them and then you'll press alt h b for border and
then bottom border let's say so we'll press o for that or you can also do all borders by pressing a
let's do let's do a instead then once we have that let's select them again ctrl shift arrow
and then we'll fill them in a blue color for instance so alt h h and blue so we're gonna just
with the arrows go to the blue one that we like and then here let's say that we don't like that
the price and the revenue are not in dollar signs so let's change that so we'll press ctrl
shift down arrow and then shift right arrow to select both areas and then we'll press alt h a n
and then press enter and that's going to give us us dollars now you can see that there's actually
two more decimals than we would like so we'll press alt h9 alt h9 again that's going to remove
the decimals for us now doing these formatting shortcuts for the first time you might notice that
you're actually quite slow and that's perfectly normal maybe you're even faster using a mouse
but if you invest enough time in them you start to memorize them and it's definitely going to make
you a lot faster in the long run another thing you'll notice here is this format painter button
you can select that obviously by clicking on it or you can press the alt h fp and you can see that
this cell is now selected and if for instance we go on this one the i1 here and we click it
you'll notice that the same formatting appears on this one so it essentially copies any format so
let's say we want to copy the dollar format to the quantity over here we'll press the alt hfp
and then just select this one here now let's move on to the next sheet by pressing the control page
down button so once you're here you'll notice that it's a complete mess all of the columns and rows
are in inconsistent lengths so you can either go one by one and actually change them or there's
actually a better way to go about it so you press the ctrl a key and then you'll do alt h o
and then the i here is going to give you the autofit column width so first we'll do the
columns and then we'll press alt h o a for the row and there you go it's looking a lot better now all
right moving on to shortcut formulas and for that we'll be using the formula sheet and we have this
information here on apple products sold by the a-team which consists of these people over here
so let's say we want to find the sum of whatever they sold in 2018 so for that you can either do
the equals sum formula which is the way that most people go about it and then you select everything
that you need and press enter but let me delete that quickly the shortcut for that is the alt
arrow key that's just going to select everything and do it quickly for you if you want to take it
a step further let's say we want to select all of these years you can just select them first
by pressing the shift arrow key and then from there you just have to press the alt equals and
you'll get all of them at once same thing applies over to the horizontal side it's smart enough to
calculate that as well just press the alt equals again there you go if you ever want to see what
the formula is grabbing you can press the f2 key and it's going to show you and the last one i'll
go over is flash fill for that we'll go over to the slider table that we have down here and first
we want to find the first name of this person so it's sum in this case we'll just type sum once
and then from there you can actually just shift down arrow all the way to the bottom and press
the ctrl e and that's going to fill everything for you same thing applies here so you type smith once
and then shift down arrow and press ctrl e same with the emails let's say that some dot smith
gmail.com and assuming all the other users are also gmail users you can just shift down arrow
and ctrl e as well so it is quite smart in that sense so in this case we just use the
flash fill for the first name the last name and the email but as you can imagine this
formula is actually quite versatile for example if there's employees working at the same company
you can use it for that for country codes for the first initial numbers of a cell phone and so on
at some point along the way maybe you realize that the way you do a shortcut is different
to the way i do it and that's something quite normal in excel there's actually
many different ways to go about it for example let's say i type 0.33 here and here as well
and we want to change that number into a percentage i could do ctrl shift percentage sign
or alternatively i could also do alt hp like we saw earlier that's all for this video i hope
you found it helpful if you want more excel videos do let me know down in the comments
and hit that like hit that subscribe it really helps out and i'll catch you in the next one