19 Advanced Excel Shortcuts (do you know them all?)

welcome to Excel campus my name is John and in this video I'm going to share 19 advanced excel shortcuts so some of these you might have seen or heard of before but I think these are great shortcuts that you might not use every day but can definitely save you time now all of these shortcuts will work for the Windows desktop version of Excel and will list the Mac equivalents wherever possible I will also make the file I'm using available for free download and I'll put a link to that in the description below this video so the first shortcut is for filtering pivot tables this is a great little shortcut so we have a pivot table here and let's say we want to filter out this item this pivot item I can just select that cell hit ctrl - that will apply a filter and then exclude the blank item from the field there you can also do this with multiple items so we'll select these items again hit ctrl - that applies that filter to exclude those items next is grouping rows and columns so let's say we want to hide all of these columns with the months here first going to select all of those columns for the months then use shift alt right arrow to create that group then of course we can collapse and expand the group shift alt left arrow will ungroup and then we can also do the same for rows so select the rows shift alt right arrow will create a group for the rows next is selecting visible cells so here we have some rows that are hidden grouped and hidden and if we were to select these cells and then copy and paste that would include the hidden rows however after we do that selection if we use alt semicolon hold the Alt key and press semicolon that's going to select the visible cells only then we can hit ctrl C to copy come down here ctrl V to paste and that will only paste the visible cells extra few shortcuts for navigating and selecting cells so let's say we're at this cell here and we hit ctrl shift down arrow to select the entire column if we want to go back and see the top cell that active cell we can hold ctrl and hit backspace to jump up and make the active cell visible now if we have the entire range selected I'll hit ctrl a we can use control period to select the corners so that's going to select the top left corner control period again select the top right corner control period again we'll select the bottom right corner so this is a great way to navigate around the range and also see the bottom and top rows of the selection and then if you're on a phone call with someone and they're asking you to navigate to a specific cell or row you can hit f5 or ctrl G to bring up the go to window and you can just type the cell in here so maybe we want to go to a 5 6 5 hit enter and that of course will take us right down to that cell next we'll look at finding the next match so first we're going to open the find window with ctrl F and maybe we want to find product 3 in this column so I'll just type product 3 of course we can hit find next here to find the next matching value we can also close this window again with product 3 in the find what box and we can use shift f4 to find the next match so hold shift and continue to hit f4 to just find the cell with the next matching value control shift f4 we'll find the previous match now if you only want to find cells in a specific column I'm going to open the find window again hit options here and we want to change the search to by rows that will search by rows going down every single column if we change this to by columns that'll search across rows left to right next we'll look at shortcuts for entering and copying data so if I want to enter a formula in this column here I'm first going to select all those cells I'm going to type equals and then it'll say quantity times price and now I want to hit control enter to fill down the formula to all the selected cells and if this is a value here control enter will also enter the value the same value and all those selected cells now maybe you forget to do that we'll put the formula we'll select here all the cells here we'll put the formula right in here this time shipping plus net total and hit enter we forgot to hit control enter so in this case we can use ctrl D to copy down you can also do that in a cell below a formula or a value so I'll hit control D here and that will again copy the formula down from the cell above and then ctrl R will copy from the left so it's just copying the formula here from the left next we'll look at how to copy down formula text and values so in this cell we have a sum function with a relative reference and if we were to copy this down that would include the total row and we don't want that so what we can do is select the cell here and use ctrl apostrophe to just copy down the formula text from the cell above then of course we can modify this or just hit enter to enter the formula now if we just wanted to copy down the values from above and not the formula we can use ctrl shift apostrophe that again is going to put the cell in edit mode with the value there and then you can hit enter to confirm the change next we'll look at line breaks so in this example let's say we want to have FY on the next line next to 2019 now if I try and resize this cell I just can't get F Y on that line without there being this issue here with the numbers not fitting if I expand it back out FY is on this line so what we can do is hit f2 or double-click to edit this and then put the text cursor right there in front of FY then hold down the Alt key and hit enter and that will add a line break of course you can continue to do this in a cell to enter multiple line breaks or multiple paragraphs in a cell but now if we hit enter that line break will stay there even as we resize the cell so we'll always have that line break there and this formatting will stay next we'll look at modifying cell styles so this cell has a style applied to it if I hit alt apostrophe that'll bring up the style window to modify the style and of course we can just go change its style change it to something different hit OK hit OK and that will modify all these cells that that style is applied to next we'll look at selecting cells with differences so in this column here I have a formula however some of these cells in the column have a different formula and some just contain values so in order to select those cells we're first going to select all the cells in the column then use the keyboard shortcut ctrl shift backslash that will select all the cells with differences from what was the original active cell here and then of course we can go apply some kind of fill color to highlight those and the keyboard shortcut to select row differences is ctrl backslash next we'll look at selecting precedence and dependence and this is great when you're doing any type of formula auditing or modifying formulas and you want to see the potential impact that that could have so this cell here just contains a simple formula that multiplies these two cells together if we wanted to select those cells we can first select this cell then use ctrl Open bracket and that will select the direct precedence of that cell now if we want to select all the precedents meaning not only this cell here but any cells that are used for the formula of this cell like these two cells we can again select this cell then use ctrl shift open bracket that'll again select all the cells used in this formula and the cells used in the other formulas that are used in this formula and then selecting dependents is the opposite of that so if we start with this cell here and use ctrl close bracket that's going to select this cell because this cell is dependent on the cell or uses that cell in that and then if we use control shift close bracket that's not only going to select this dependent cell but all of these cells as well because these cells are also dependent on this cell here so again this is a great way to see the impact that might be made if you change a formula next we'll look at shortcuts for named ranges so ctrl f3 opens the name manager and of course you can edit and modify named ranges here you can also create named ranges and alt n is the keyboard shortcut to hit that new button to create a new named range you can modify the name hit enter and that will create the new named range then you can use escape to get out of this window next we'll look at shortcuts for the formula bar and ribbon so here we have a really long formula now of course you can drag this down to make the formula bar larger and then move it back up you can also click this button here to expand or collapse it keyboard shortcut for that is ctrl shift u so ctrl shift you will expand the formula bar hitting ctrl shift you again will collapse it now if you want to create more space for your spreadsheet you can also use ctrl f1 to hide the ribbon and then ctrl f1 again will bring it back next is resizing columns so the first thing I'm going to do if I want to resize or auto fit this column is select the column you can use control space for that make sure the entire column is selected then the keyboard shortcut is alt H oh I that's going to auto fit the column width based on the selection and one place this comes in very handy is when you're using Excel tables and maybe changing or rearranging the order of the columns now we need to autofit again I'm going to hit ctrl a first and then hit control a one more time to select all of these cells including the header row then alt H oh I to auto fit all the columns in the table and finally we'll look at the selection pane alt f10 is the shortcut to open and close the selection pane and of course the selection pane contains a list of all these shapes on the sheet including slicers and charts here you can hide and unhide these shapes you can also rename them and then change the order to either bring them in front or behind other shapes and this is a great window to have open when you're creating dashboards or working with any kinds of sheets that contain reports and multiple shapes so that's 19 advanced Excel shortcuts hopefully you learned a few new ones here of course feel free to share this with your coworkers and friends that might also benefit from some of these time-saving techniques thanks again for watching and I'll see it in the next video [Applause]

Channel: Excel Campus - Jon

Views: 174,703

Keywords: excel, excel campus, advanced excel shortcuts, advanced excel shortcuts and formulas, advanced excel shortcuts pdf, advanced excel tricks, coursedots, excel 2016, excel advanced, excel formulas, excel keyboard shortcuts, excel shortcuts, excel tips and tricks, excel tips and tricks 2019, excel tutorial, introduction to excel, keyboard shortcut keys, microsoft excel tips, microsoft office, microsoft office suite tutorial, power tips for excel, shortcutkeys

Id: 5pwV7ERbXjk

Length: 10min 38sec (638 seconds)

Published: Thu Dec 05 2019