BEST Excel Shortcuts for Business & Finance

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

test attribution text

Add Comment