19 Advanced Excel Shortcuts – Excel Campus
Bottom Line: Learn 19 keyboard shortcuts for advanced Excel users.
Skill Level: Advanced
Download the Excel File
The workbook that I use in the video can be downloaded here:
19 Advanced Excel Shortcuts.xlsx (422.3 KB)
Advanced Keyboard Shortcuts
If you are looking to up your Excel game and work faster using keyboard shortcuts, I recommend you give these 19 techniques a try.
1. Filter Out Items in Pivot Tables
If you want one or more items to be filtered out of your pivot table, just select the item(s) you want filtered and type Ctrl + -. This creates a filter and excludes the item(s) you selected.
To clear all filters, use Alt, then A, then C.
Mac equivalent: Ctrl + -
2. Group Rows and Columns
To group two or more columns or rows together, select the entries you want to group and then type Shift + Alt + →. To ungroup the rows, use Shift + Alt + ←.
Once the group is formed, you can expand or collapse the entries using the plus and minus buttons that Excel creates in the header section for the rows or columns. Or, if you prefer to use your keyboard to expand and collapse, you can use the following shortcuts. Expand: Alt, then A, then H. Collapse: Alt, then A, then J.
Mac equivalent: Group: Cmd + Shift + K | Ungroup: Cmd + Shift + J
3. Select Visible Cells
You may have rows or columns hidden that you don’t want selected when you are working with the surrounding data range. To select ONLY the cells that are currently visible, select the range you want and then hit Alt + ; to exclude those hidden entries.
Mac equivalent: Cmd + Shift + Z
4. Show the Active Cell
If you’ve selected a large range but want to go back to the beginning of that selection without deselecting the entry, you can show the active cell by typing Ctrl + Backspace.
Mac equivalent: Cmd + Delete
5. Select Corners of Selected Range
Use Ctrl + . (period) to navigate around the corners of your selected data range. Each time you press these keys, the active cell moves to a different corner of the selected block.
Mac equivalent: Ctrl + .
6. Open the Go To Window
If you want to go to a specific cell, you can bring up the Go To Window by pressing either F5 or Ctrl + G. Then you can type in the address of whatever cell you are looking for and press Enter to be taken to that cell.
Mac equivalent: Ctrl + G
7. Find the Next or Previous Match
You are probably familiar with the Find and Replace Window, which you can summon using Ctrl + F.
But what you may not know is that you can use Shift + F4 to find the next matching entry once you have typed in the match you are looking for. To find the previous match, just use Ctrl + Shift + F4. These keyboard shortcuts will work whether the Find and Replace Window remains open or is closed.
Mac equivalent: Next: Cmd + G | Previous: Cmd + Shift + G
8. Enter Formula/Value in All Selected Cells
This next tip helps you to copy data from one cell in a selection to the remainder of the selection. After selecting the cells you want affected, type your formula in the selected cell. But instead of hitting Enter after your formula is typed, hit Ctrl + Enter. This will copy your formula to all of the other selected cells. This trick works for values as well, not just formulas.
Mac equivalent: Ctrl + Enter
9. Fill Down
While using the previous shortcut, if you accidentally forgot and hit Enter by mistake, you can still use the Ctrl + D shortcut. This copies your data/formula down. Likewise, Ctrl + R copies your data/formula to the right.
Mac equivalent: Fill down: Ctrl + D | Fill right: Ctrl + R
10. Copy Formula Text From Above
To copy formula text from a cell above, hit Ctrl + ' (apostrophe). If you’d like to just copy the value of the cell above, use Ctrl + Shift + '.
Mac equivalent: Formula: Ctrl + ' | Value: Ctrl + Shift + '
11. Line Breaks
To enter a line break within a cell in order to make text move down a line, just place your cursor before the text that you want to move and press Alt + Enter. This line break will stay in place even if you resize the column or row .
Mac equivalent: Ctrl + Option + Enter
12. Modifying Cell Style
If you’d like to change the style of a cell, press Alt + ' (apostrophe) to bring up the Style Window. From there you can alter the formatting.
Mac equivalent: Cmd + Shift + L
13. Selecting Cells with Differences
If you select a range of cells, you can identify the cells within that range which are different from the original selected cell. With the range selected, just type Ctrl + Shift + (backslash) and Excel will instantly identify the cells with discrepancies. This is for differences within a column. If you want to identify differences within a row, use Ctrl + .
Mac equivalent: Column: Ctrl + Shift + | Row: Ctrl +
14. Selecting Precedents and Dependents
You can use the shortcut Ctrl + [ (open bracket) to select all of the the cells that appear in a formula. These are called direct precedents. If some of those precedents also have formulas that you would like to be selected, you can use Ctrl + Shift + [ to see all precedents highlighted.
The opposite of precedents are dependents. If you would like to see the cells that are directly dependent on a particular cell, type Ctrl + ] (close bracket). For all of the dependent cells, use Ctrl + Shift + ].
Mac equivalent: Direct precedents: Ctrl + [ |All precedents: Ctrl + Shift + [ | Direct dependents: Ctrl + ] | All dependents Ctrl + Shift + ]
15. Named Ranges
If you are looking to modify named ranges, just press Ctrl + F3 to bring up the Name Manager.
From here, if you want to create a new named range, you can either click on the New… button, or press Alt + N.
Mac equivalent: Fn + Ctrl + F3
16. Hide the Ribbon
If you need to see more of your worksheet all at once, you can make more room by hiding the Ribbon. To do that, just press Ctrl + F1. Repeating that shortcut will make it visible again.
Mac equivalent: Cmd + Option + R
17. Expand or Collapse the Formula Bar
When you have a really long formula that exceeds one line of text, you can expand the formula bar, so that you can see more, by using the keyboard shortcut Ctrl + Shift + U. The same shortcut will also collapse it again.
Mac equivalent: Ctrl + Shift + U
18. Resizing Columns
You can AutoFit columns to be exactly the right length by pressing Alt, then H, then O, then I. To AutoFit rows, use Alt H O A.
Mac equivalent: not applicable
19. Selection Pane
To open and close the Selection Pane, press Alt + F10. The Selection Pane is useful when working with shapes, slicers, and charts.
Mac equivalent: not applicable
If you liked these shortcuts, I suggest you check out some of these related posts on Excel Campus.
5 Keyboard Shortcuts for Rows and Columns in Excel
How to Copy and Paste Visible Cells Only
How to Select Cells with Differences in Excel
17 Excel Shortcuts
7 Uncommon Excel Shortcuts to Share with Your Coworkers
I hope these shortcuts prove useful to you and help you to shine at work! Do you have some favorite shortcuts that should be featured in a future post? Let me know what they are in the comments below.