Navigate Using Keyboard Shortcuts and the Quick Access Toolbar

If you spend a lot of time using Excel, it’s worth investing a few minutes to learn how to quickly navigate around it and have the functionality that you use commonly in an easily accessible location.

The first rule with this is that using the keyboard to navigate is almost always quicker than using the mouse so if you can get used to using Keyboard shortcuts instead of mouse movements/clicks then your productivity will increase. Most people use a couple of keyboard shortcuts to navigate (that they’ve most likely been shown by friends or colleagues) but when searching for a comprehensive list they get overwhelmed by the number and the daunting task of committing them all to memory. So I’ve included a handful of the ones I find most useful below – split into those used to navigate (‘Jump to’) and those used to access some standard Excel functionality.

Even for accessing Excel functionality on the ribbon for which you don’t know the shortcut (or there isn’t one available), you can access them via the keyboard. When In Excel, clicking on Alt displays the letters for all the ribbon tabs – pressing the key for the tab you want will take you to that tab and show the next letter you need to press to access the feature you require. So in this way you can access the ribbon feature via a keystroke. For example if you copy and paste special values a lot then you can use the keyboard shortcut Ctrl + C to copy and then the keyboard keystroke sequence Alt, H, V, V to paste special values. If you do this a few times you’ll remember it and then it’ll be much quicker than using the mouse to do the same thing.

Regardless of how you access it, locating functionality through the ribbon can sometimes take time and in Excel 2007 (unlike in Excel 2003 and Excel 2010), there is no standard feature available to insert your own tabs or customise what appears on them. However, what you can customise is the Quick Access toolbar – the series of icons that appears next to the ribbon button in the top left hand corner (above the tab names). Adding your own shortcuts into here will allow you to access them quicker than locating them through the ribbon. It’s also the only way to access the ‘hidden’ Excel functionality which doesn’t appear on the ribbon!

Jump To:

• Next/Previous Worksheet Ctrl + PgUp / PgDn
• Other Files currently open Ctrl + Tab
• Column A in current sheet Home
• Cell A1 on current sheet Ctrl + Home
• Edge of Current Data set (Joined by consecutive rows/columns) Ctrl +
• Last cell used in current worksheet Ctrl + End
• Entire Row of current cell/range Shift + SPACE
• Entire Column of cell/range Ctrl + SPACE
• Formula Edit mode for current cell F2
• Format cells dialog for current cell/range Ctrl + 1

Quick Shortcuts for Excel Functionality:

• Change to Absolute Row/Column References (When In Edit Mode for a cell) F4
• Evaluate Selected element of formula (When In Edit Mode for a cell) F9 (be sure to press Esc afterwards!)
• Find and Replace Ctrl + F / Ctrl + H
• Copy Ctrl + C
• Paste Ctrl + V
• SUM all cells in row to the left of current cell Alt + =
• Display formulas in worksheet Ctrl + ¬
• Delete selected cell/range Ctrl + –
• Repeat Ctrl + Y
• Save current workbook Ctrl + S
• Close current workbook Ctrl + W
• Close application Alt + F4

The Quick Access Toolbar is the series of icons located by default at the top of the screen shown in this screenshot:

Originally this contains just the save, undo and redo icons though others can be added by customising the QAT. To do this, click on the downward arrow at the end of the toolbar – this will show some more commonly used functionality (such as print) which you can add by selecting it. However, to see the full list of everything that is in the ribbon (and what isn’t!) select ‘More Commands’

This then displays the dialog box below. The left hand list box shows all the functions available (in alphabetical order) – to add something to the Quick Access toolbar select it, click on ‘Add >>’ and it will move to the right hand list box.
By default the list shows all the ‘Popular Commands’ though clicking on the drop down arrow allows you to see instead the functionality within in a particular tab or alternatively ‘All Commands’ or ‘Commands Not in the Ribbon’.

It is worth spending some time reviewing the functionality that can be unlocked through the QAT, using it either to access features that you use regularly, or those commands that are not in the ribbon. For the latter type, here are some potentially useful features which are not in the ribbon:

• Camera – allows you to Create a Live snapshot of your data in much the same way as the Paste as picture option.
• Create Microsoft Office Outlook Task – This creates a task in MS Outlook with a copy of the Excel file embedded within the body of the task. This is useful if you use Microsoft Outlook tasks to keep track of the tasks that you have assigned (either to yourself or others that you have delegated to).
• Document Location – This shows the full path of the current workbook in the QAT. This is very useful if you want to send a link to the workbook to someone, or just see where the file you’re currently working on is located.
• E-mail: Allows you to quickly e-mail a copy of the current workbook (note this is available through clicking on File/The Office button and then clicking send)
• Speak cells – this is Excel’s text-to-speech functionality which will read out text written in a cell
• Select Visible Cells – When Copying and pasting a range it will by default include rows/columns which are hidden. After selecting your range, press this and it will exclude any hidden or grouped rows/columns.

An example of customising the QAT is shown in the video clip below

YouTube Video – Excel 2007 Tutorial 24: Shortcut Keys