Excel Keyboard Shortcuts used by Pros

this video is going to be jam-packed with 20 XL pro keyboard shortcuts and we're not talking about shortcuts like ctrl C and ctrl Z the shortcuts in this video usually take Excel pros years to accumulate but I've condensed them into one video for you now I kind of feel like I'm giving away my Excel secrets I've also included an excel file with each of the shortcuts documented and if you want more there's a link in the video description where you can download our PDF containing 239 Excel keyboard shortcuts alright let's get started one of the most annoying things in Excel is accidentally using the arrow keys inside a dialog box I need to find the focus is still on the worksheet cells and it's particularly annoying because it's too late once you press the arrow keys to easily undo the additional cell reference that's been added the solution is to press f2 before you use the arrow keys and if you take note in the bottom left in the status bar you can see when I press f2 it changes from point which is what it currently says to edit and now I can move my arrow keys happily without messing up my cell reference now this applies to all dialog boxes not just charts like I have here one of the most common tasks is copying and pasting we all know that to copy is ctrl C and to paste is ctrl V but one of the most common pasted tasks I perform is paste values now the official keyboard shortcut for paste values is ctrl alt V then V and then enter now I don't know about you but I reckon you need to be a contortionist to press ctrl alt and V with one hand and that's why the original keyboard shortcut which is alt and then e then s and then V for values is far easier and then simply press Enter now you can do it with one hand and even if you can't touch type after a few practices you don't even need to look at the keyboard once you know the shortcut for paste special the other options are easy another favorite of mine is paste as formulas so alt es to open the paste special and then F for formulas it's easy to remember as well now it should point out while we're here that if you want to use any of the other keyboard shortcuts in the pay special dialog box you simply look for the underlined letter so for example format is T comments is C and data validation is n in this case I want to paste formulas so it's F and then enter selecting columns and rows is dead easy control space selects a column and shift space selects a row now if you have multiple cells selected prior to pressing the shortcuts you're going to get multiple rows if you press shift space or if you have multiple cells selected across columns and press control space you get all of the columns selected so now that you've selected columns or rows you can easily insert them with ctrl + now that's the plus sign on the numeric keypad if you want to use the plus sign that shares the equal sign then it needs to be ctrl shift + + + to delete columns its ctrl - likewise with rows so if I wanted to select a row I can ctrl + to insert that row and ctrl - to delete it we can also insert individual cells or groups of cells by first selecting the cell and then ctrl + in this case we get a new dialog box which allows us to choose whether we want to shift the cells down or to the right here we can also choose to insert an entire row or an entire column so let's say we want to shift cells down I'm just using my arrow keys to choose which option and then press Enter likewise ctrl - will delete a cell so I can shift cells up or to the left and again delete entire rows or columns so I'm going to shift cells back up and press Enter ctrl-a will select a contiguous range of cells for example here I just had one cell selected and control a selects them all including these totals down here it also works if you have some cells that are empty so here I have almost an empty column and with any cell selected control a selects the entire table control a again selects the entire worksheet now control a will stop and there's a break in the entire table so if I delete that row there and then control a it only selects up to that break likewise if I had a whole column that was empty and then I control a it only selects up to that empty range of cells now sometimes you don't want to select the whole table perhaps you only want to select up to row 13 in that case holding ctrl and shift and then down arrow selects the whole column as long as the cells have data in and then the right arrow with ctrl + shift still selected selects across the columns and then I can release control and then just up arrow or back arrow to deselect rows and columns that I don't need likewise I can include more columns just holding shift and my arrow keys similar to selecting cells you can also use control a to select objects on worksheet objects can be images charts shapes like I have here form controls SmartArt basically anything that floats above the grid all you need to do is select one object with your mouse which I have done here with the blue item and then control a will select them all if you want to deselect any objects hold the shift key or the control key and click on the object you don't want selected most excel users know that you can left-click and drag to move a cell or range of cells or even a whole column or row but not many nodes that if you hold down shift at the same time and then left-click and drag that it will insert that row when you release the mouse likewise we can duplicate by holding ctrl and you get the plus sign beside the mouse and shift left click and drag now I'm going to add a row and insert it so shift will insert control shift will add and this works for columns as well and you can select a whole range so I could control shift and insert these columns here or I can just shift and insert them so ctrl shift copies and shift inserts now for some Excel users knowing this tip is even possible is a tip in itself and the keyboard shortcut will be the icing on the top so what I'm talking about here is filtering by cell value so we can right click filter and filter by selected cells values at the moment I have the brand adventureworks selected so we'll select that and now my table is filtered only for the brand adventureworks so let me undo that the keyboard shortcut for this is the menu or application key and then e and V you can see the letter is underlined in the menu so V for value and now I've filtered by brand now it's handy to know that the Menu key will work in lots of areas it's effectively the same as the right click of your mouse so you can use that when you're working with charts or pivot tables anything really the Menu key will bring up the equivalent of the right click and you'll find the Menu key is on the right hand side of your keyboard usually immediately to the left of the control key Pro users typically have multiple Excel workbooks open at one time and switching between them is dead easy with ctrl + tab and ctrl shift tab will take you back to the previous sheet similarly we can use control page up and page down to scroll through the different worksheets in our workbook now you're not a pro user if you don't use excel tables to quickly format your data in an Excel table I like to use ctrl T T for table it's easy to remember brings up the dialog box my table does have headers so we'll leave that checked and I'll click OK or you can press ENTER now the official shortcut for formatting as a table is ctrl L but that requires two hands so I like ctrl T instead now data entry isn't a common task for the Excel pro but we all need to copy data down from the row above from time to time and the easiest way to do this is with ctrl D and we can select multiple cells and copy them all in one go ctrl D I like to remember it because D is for duplicate now ctrl D also works to copy objects so charts images shapes and other objects simply select one of them and ctrl D and you get another one another option for copying is ctrl R it will copy across to the right now before you ask ctrl L does not copy to the left ctrl L will insert a table if you work with objects a lot like charts and shapes like I have here then you'll find the Alt key is super handy when you hold down alt and left click and drag you can see the object is snapping to the grid and that's really useful for aligning your objects it'll also work when you resize it so now I can resize it perfectly to the grid behind so that's alt while you left-click and drag or to resize as soon as you release alt you can move the object around as usual another key I like to use when I'm working with objects is the shift key so if I wanted to keep this object aligned to its current horizontal location I simply hold shift and as long as my mouse doesn't fear too far off the track it will stay aligned horizontally likewise vertically and as long as my mouse doesn't they're too far off the track it will stay vertically aligned but you can see there I'm moving it too much but if I just keep it roughly in the right place then shift will keep it aligned to its original position another shortcut I use all the time is control one it opens the format pane now it doesn't matter whether you've got an object selected or a cell control one will open the relevant dialog box or format pane for the item or object that you have selected so if you were working with a pivot table control one is going to open the format pane for the pivot table so it's handy for sales charts shapes images anything you want to format control one is going to open the formatting options now the official keyboard shortcut for redo is control Y but I prefer the f4 key there's only one key to press and it's just quick and easy so I can quickly reapply the formatting but f4 also applies absolute references so if we're working in a formula I can click inside the formula bar or in the cell in edit mode and pressing the f4 key will apply absolute references now before I do it just notice that I've only selected the two from the first reference and the D in the second reference but when I press f4 it will apply the absolute referencing to the whole reference so you don't need to have the whole cell reference selected I'm pretty lazy if I can get away with just selecting a bit of it then that's what I'm gonna do now f4 again will remove the absolute reference from the columns but leave the rows f4 again we'll apply it to the columns and not the rows f4 again removes it all together and so we're back to how we were originally so the first step fourth is going to apply absolute referencing to the rows and the columns you can just keep pressing it to toggle through the different options you can also just apply it to one of the cell references and not the other just depends where you select so remember f4 for repeating formatting and things like that I like to use it for formatting charts and also for absolute referencing excel pros frequently use named ranges and name formulas so it's fitting that we want a quick way to insert those names into our formulas so we can start a formula and then press the f3 key brings up a list of our names available and I can just use the arrow key to select the one that I want and press Enter it's inserted it into the formula now all I need to do is press ENTER to finish it let's say that I wanted to add text to this amount here so we'll do it in the cell below I'm going to start my formula I need to take this value multiply it by the tax rate now that's a name that I have I can't quite remember what I called it so I'm going to f3 to open the names use my arrow keys there's my tax rate name press ENTER and enter again and that's calculated the tax when you inherit a new workbook it can be really handy to quickly get an overview of where the formulas are one way we can do this is with the keyboard shortcut control and the graph accent or backtick key as it's also known and this is usually in the top left of your keyboard and it shares the key with the tilde symbol so I'm going to ctrl + backtick and now it's showing me the formulas it hasn't got rid of them it's just showing me the formulas rather than the result but notice it's also removed the date formatting from column a and the number formatting from column C so I like to use this sometimes to check if dates have been entered correctly because in column a what we see are the date serial numbers and if I press ctrl and backtick again it reverts back to how it was before so it's just a quick way to see all your formulas but also remove any formatting that's on the sheet now wait pro users don't do a lot of data entry but when we do we want it to be quick and consistent and that's why the Alt + down arrow key is super handy because it brings up this instant drop-down list it's based on the items that are in the column already so I can simply use my arrow key and then when I found one I want press enter and it enters it for me now this is one of my most used shortcuts to quickly insert a sum for a column or row or even a whole table so alt equals is going to insert a sum for the cells that it detects either directly above or to the right or even to the left its detected the values above and I press ENTER and there's my son now I can apply it for a whole row select the cells first all equals this time I didn't even need to press Enter likewise for a column o equals and it inserts my sums for me but it will also do it for a whole table so I just select all the cells including the row and column that I want the totals in and then alt equals and it's inserted the formulas for me we can see them in the formula bar like I said Pro users rarely do any data entry but from time to time we all need to enter the date all the time and when we do I like to use ctrl and then the semicolon and that enters the date or if I need the time ctrl shift and semicolon again and there's my time now this is picking up the time from your PC clot so if it doesn't look right there then you need to go into your PCs settings now there are a couple of other tips that I want to add I've already done 20 but indulge me a moment here because these are a few more that are super handy f12 will open the save as dialog box f7 for spell check and that's essential if you're creating reports no one wants to send out a report with typos in it f5 will open the go to dialog box or ctrl G for short and then alt s for special and in here you can access all the go to options and hyperlinks is another one I use a lot and ctrl K is the shortcut for hyperlinks now ctrl K will work in other programs as well so I use that all the time and my very last tip is that you don't always have to start a formula with equals you can just use the plus sign so for example I could add 10 days to this date and reference it using the plus sign and I press Enter now if I go back to that cell you'll see Excel has added the equal sign in front of the plus for me now this is a backward compatibility feature from Lotus 1-2-3 well that wraps up my 20 plus pro keyboard shortcuts I'd love you to post a comment letting you know which shortcut made you react like this and if you have a pro tip please share it and don't forget to download the file for this lesson and if you like this video please give it the thumbs up subscribe to my channel and click the belt icon to be notified when I post my next video and please share it with your friends who also love keyboard shortcuts thanks for watching

Channel: MyOnlineTrainingHub

Views: 64,374

Rating: 4.9722586 out of 5

Keywords: excel keyboard shortcuts, keyboard shortcuts, excel shortcuts

Id: 4xanM8XD058

Length: 19min 5sec (1145 seconds)

Published: Wed Mar 04 2020