5. Keyboard Shortcuts

If you do a lot of typing, being able to access commands from the keyboard is faster than moving your hand to the mouse. Excel 2010 introduces new keyboard accelerators accessed using the Alt key. In addition, many of the old Alt keyboard shortcuts still work and all the old Ctrl shortcut keys are still functional. For instance, Ctrl+C still copies a selection, Ctrl+X cuts a selection, and Ctrl+V pastes a selection.

This chapter points out which of the old keyboard shortcuts still work, shows you some new shortcuts, and introduces you to the new keyboard accelerators.

Using New Keyboard Accelerators

The goal of the new Excel 2010 keyboard accelerators is to allow you to access every command by using only the keyboard. In legacy versions of Excel, many popular commands had keyboard accelerators, but other commands did not. Excel 2010 tries to ensure that every command can be invoked from the keyboard.

To access the new accelerators, press and release the Alt key. Notice that Excel places a ToolTip above each command, with an associated accelerator key.

Note that an arcane command exists in the Excel Options dialog that can cause the new keyboard accelerators not to work for you. It is possible that you turned on this setting in Excel 1995 and each successive upgrade of Excel has inherited the setting. You should check the setting before proceeding. To do so, select the Office icon and then select Excel Options. In the Advanced category, scroll to near the bottom for Lotus Compatibility. If Transition Navigation Keys is selected, then the slash character shown in the Microsoft Office Menu Key will be used instead of Alt to invoke shortcuts. If you prefer using the Alt key, you should clear the Transition Navigation Keys check box. Keep in mind that if you prefer using the slash key, you must use / in place of Alt with new keyboard accelerators.

Tiny letter ToolTips appear over each tab of the ribbon. In addition, number ToolTips appear over each icon in the Quick Access toolbar. Figure 5.1 shows the ToolTips.

Figure 5.1. Type the letters in the ToolTips along the top to open various tabs. Type the numbers in the numeric keytips to access the Quick Access Toolbar.

It is possible to memorize the keytips for the ribbon tabs.

Pressing Alt+F always accesses the File menu in all Office 2010 applications.

Alt+H always accesses the Home tab in all Office 2010 applications. The accelerator definitions for each tab remain constant even if new ribbon tabs are displayed. When you activate a pivot table, the original keytip letters (F, H, N, P, M, A, R, W, L, and X) remain, and two new keytips appear for the two new tabs: JT for PivotTable Tools Options and JY for PivotTable Tools Design (see Figure 5.2).

Figure 5.2. New ribbon tabs get new letters, making sure the old letters remain constant.

Unfortunately, the keytips for the Quick Access toolbar change every time you add new buttons or rearrange buttons on the Quick Access toolbar. If you want to memorize those keytips, you need to make sure you do not add a new Quick Access toolbar icon at the beginning of the list.

Selecting Icons on the Ribbon

After you press the Alt key, you can press one of the keytip letters to bring up the appropriate tab. You now see that every icon on the ribbon has a keytip.

When you choose a ribbon tab, the keytips on the Quick Access toolbar disappear, so Microsoft is free to use the letters A through Z and the numbers 0 through 9.

On very busy Ribbon tabs, some commands require two keystrokes: for example, A+C for Align Center in the Alignments Group of the Home tab, as shown in Figure 5.3. Note that after you press Alt to display the accelerators in the ToolTips, you do not have to continue holding down the Alt key.

Figure 5.3. After pressing the letter to switch to the ribbon, type the letter or letters to invoke a particular command.

Some shortcut keys seem to make sense: AT for Align Top, AM for Align Middle, AB for Align Bottom, AL for Align Left, W for Wrap Text, and M for Merge. Other shortcut keys seem to be assigned at random. Some take a little pondering: FA for the dialog launcher in Figure 5.3 makes sense in that it opens the legacy Format dialog and moves to the Alignment tab. Others have a historical precedent. In Excel 2003, F was used for File so O was used for Format. Similarly, in the Home tab, O now opens the Format drop-down, although since Microsoft no longer underlines the accelerator key in the menu name, O will never make sense to someone new to Excel. There might be some arcane, logical reason why 5 and 6 are used for increase and decrease indent, but it is unknown by most people.

Selecting Options from a Gallery

Figure 5.4 shows the results of pressing Alt+H+T, which is the equivalent of selecting Home, Format Table. This opens the gallery of possible table styles. As you can see in Figure 5.4, you can invoke the New Table Style and New Pivot Style commands at the bottom of the gallery by pressing N and P, respectively. However, there are no letters on the table style choices in the gallery.

Figure 5.4. After opening a gallery, you use the arrow keys to navigate through the gallery and press Enter to select a style.

To select a table style using the keyboard, use the arrow keys to move through the gallery. Because this gallery is two-dimensional, you can use the up arrow, down arrow, right arrow, left arrow, Page Down, Page Up, Home, and End keys to navigate through the gallery. When you have the desired table style highlighted, press the Enter key to select it.

Navigating Within Drop-Down Lists

If you press Alt+H+F+S, which is the equivalent of selecting Home, Font Size, the font size in the drop-down is selected. You can either type a font size and press Enter or press the down-arrow key to open the drop-down list. You can then use the down arrow, up arrow, Page Down, Page Up, Home, and End keys to navigate to a choice in the list. When you have the desired item highlighted, press Enter to select that item.

Backing Up One Level Through a Menu

Suppose that you press Alt+H to access the Home tab and then realize you are in the wrong tab. You can press the Esc key to move back to display the ToolTips for the main menu choices.

If you want to clear the ToolTips completely, press Alt again.

Dealing with Keyboard Accelerator Confusion

If you want to select something on the Home tab in Figure 5.2, you may be frustrated because you can see the menu choices, but there are no ToolTips for most commands. For icons in the top of the ribbon, it appears that the main keytips apply to the menu items. For example, you may think that the H keytip applies to Cut. Even though you are already on the Home tab, you need to press the H key to force Excel to show the ToolTips for the individual menu items on the Home tab.

Selecting from Legacy Dialog Boxes

Some commands lead to legacy dialog boxes like the ones in previous editions of Excel. These dialog boxes do not display the Excel 2010 keytips. However, most of the dialog boxes do use the convention of having one letter of each command underlined, which is called a hotkey in Microsoft parlance. In this case, you can press the underlined letter to select the command.

For example, press Alt+H+V+S instead of selecting Home, Paste, Paste Special. You are then presented with the Paste Special dialog box, as shown in Figure 5.5. To select Values and Transpose in this dialog, press V for Values and E for Transpose, because those are the letters underlined in the dialog. You can then press Enter instead of clicking the default OK button.

Figure 5.5. In a legacy dialog box, type the underlined letters to select options.

Using the Shortcut Keys

Excel 2010 automatically recognizes all the Ctrl shortcut keys that were used in legacy versions of Excel. In fact, many of these keys are consistent across all Windows applications. Table 5.1 lists the common Windows Ctrl shortcut keys.

Table 5.1. Windows Shortcut Keys

Table 5.2 illustrates the shortcut keys that you use to navigate.

Table 5.2. Shortcut Keys for Navigation

Table 5.3 shows the shortcut keys you use to select data and cells.

Table 5.3. Shortcut Keys for Selecting Data and Cells

Table 5.4 shows the shortcut keys you use to extend a selection.

Table 5.4. Shortcut Keys for Extending Selections

Table 5.5 shows the shortcut keys you use for entering, editing, formatting, and calculating data.

Table 5.5. Shortcut Keys for Data Entry, Formatting, and Calculating Data

There are shortcut keys specifically for using the Border tab in the Format Cells dialog. Press Ctrl+1 to display the Format Cells dialog. Press Ctrl+PgDn until you arrive at the Border tab. Then you can use the shortcut keys shown in Table 5.6.

Table 5.6. Shortcut Keys for Borders

Using Excel 2003 Keyboard Accelerators

In legacy versions of Excel, most menu items included one underlined letter. In those versions, you could hold down the Alt key while pressing the underlined letter to invoke the menu item. In the Excel 2003 screen shown in Figure 5.6, you can display the Edit menu by pressing Alt+E, and you can select Edit, Fill, Justify by pressing Alt+E+I+J.

Figure 5.6. Pressing Alt+E+I+J performs Edit, Fill, Justify.

Instead of pressing Alt+E+I+J all at once, when the Edit menu is displayed, you can display the Fill flyout menu by pressing I. Then you can perform the Justify command by pressing J.

If you are a power Excel user, you probably have a few of these commands memorized, such as Alt+E+I+J for Edit, Fill, Justify; Alt+E+S+V for Edit, Paste Special, Values; and Alt+D+L for Data Validation. If you have some of these commands memorized, when you hear that the menu in Excel 2010 is completely gone, you might be worried that you have to relearn all the shortcut keys. However, there is good news for the power Excel gurus who have favorite Alt shortcut keys burned into their minds—most of them will continue to work as they did in Excel 2003.

If you were an intermediate Excel user who regularly used the Excel 2003 keyboard accelerators but had to look at the screen to use them, you should start using the new keyboard accelerators discussed at the beginning of this chapter.

Invoking an Excel 2003 Alt Shortcut

In Excel 2003, the main menus were File, Edit, View, Insert, Format, Tools, Data, Window, and Help. The keyboard accelerator commands in Excel 2003 were Alt+F, Alt+E, Alt+V, Alt+I, Alt+O, Alt+T, Alt+D, Alt+W, and Alt+H.

If you are moving from Excel 2003 to Excel 2010, you will have the best success when trying to access commands on the Edit, View, Insert, Format, Tools, and Data menus. None of the keyboard accelerators associated with Window or Help work in Excel 2010. Alt+H takes you to the Home tab instead of the few commands on the Help menu, and Alt+W takes you to the View tab.

Some of the keyboard shortcuts associated with the File menu in Excel 2003 continue to work in Excel 2010. Pressing Alt+F opens the File menu. In Excel 2003, pressing Alt+F+O performs File, Open. It happens that O is the shortcut on the File menu for Open, so pressing Alt+F+O in Excel 2010 also performs File, Open.

For the shortcut keys Alt+E, Alt+V, Alt+I, Alt+O, Alt+T, and Alt+D, Excel switches into Office 2003 Access Key mode. In this mode, a ToolTip appears over the ribbon, indicating which letters you have typed so far (see Figure 5.7). When you have entered enough letters, the command is invoked. If you have forgotten the sequence, you can press Esc to exit the Excel 2003 Access Key mode.

Figure 5.7. The Office 2003 access key ToolTip shows which keys you have used so far while entering a legacy shortcut.

Determining Which Commands Work in Legacy Mode

If you try a command that no longer works in Excel 2010, nothing happens. Several commands don’t make sense in the framework of Excel 2010, so they have been deprecated.

Table 5.7 lists the legacy keyboard commands and shows which of them continue to work in Excel 2010.

Table 5.7. Excel Legacy Keyboard Commands

Some people liked using Alt+F+T+S in Excel 2003 for File, Print Area, Set Print Area. If you are one of those people, you will be unhappy to hear that your favorite shortcut key is not supported in Excel 2010. However, most of the powerful and common shortcut keys are still available, so there is a good chance that your knowledge of past shortcut keys will help when you upgrade to Excel 2010.