19 Advanced Excel Shortcuts

Bottom Line: Learn 19 keyboard shortcuts for advanced Excel users.

Skill Level: Advanced

Video Tutorial

Watch on YouTube and give it a thumbs up.
YouTube Subscribe Logo Excel Campus

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.

Find and Replace Window

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.

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

Related Posts

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

Conclusion

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.

9 comments

Your email address will not be published. Required fields are marked *

  • Thanks for the all the helpful tips!! I really appreciate it!
    One question for you. I don’t know where to ask. I just write in this comments.
    I am using Office 365 provided by the company I am working for.
    In this new version of excel, the comma style is somewhat strange.
    When I click the comma, It always brings two decimals.
    I tried going in “options”->”advances” and then change decimal points settings. But it didn’t work. I guess it has something to do with Comma style….but I don’t know how to change for the default settings. I want to change from two default decimal settings to no decimals. Let me know if you can. Thanks a lot!

  • I use ctrl + ~ (tilde) to toggle between values and equations view. You can easily see which cells contain a value and which contain an equation.

  • Jon
    thanks for sharing your knowledge, I know I have used your earlier tips frequently.
    But here I find it difficult to use these shortcuts.
    I live in Belgium, have a dutch version of excel 2010 and use a ‘belgian’ keyboard (AZERTY in stead of QWERTY)
    Many of these shortcuts just don’t work. Not your fault, obviously 🙂
    Do you know any source where I can find the right ‘translations’?

    grtz
    Hugo

  • Hi Jon,

    10. Copy Formula Text From Above

    just copy the value of the cell above, use Ctrl + Shift + ‘.

    This actually doesn’t work. It should be Ctrl + Shift + ” (quotation)

    Thanks,
    Colin

  • Excellent and informative video, as all of yours are.

    In the downloaded “19-Advanced-Excel-Shortcuts” workbook, all of the hyperlinks in the TOC give me an error saying ‘Cannot open the specified file.’ The HPERLINK formula in the cell looks fine though. Why would it be doing this?

Search
Generic filters
Exact matches only
Filter by Custom Post Type

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly