Chapter 4. Keyboard Shortcuts

In this chapter

Using the New Keyboard Accelerators 66

Using the Shortcut Keys 70

Using Excel 2003 Keyboard Accelerators 76

If you are doing a lot of typing, being able to access commands from the keyboard is faster than moving your hand to the mouse. Excel 2007 introduces new keyboard accelerators, which are accessed using the Alt key, and many of the old Alt keyboard shortcuts still work. In addition, 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.

In this chapter, I point out which of the old keyboard shortcuts still work, show you some new shortcuts, and introduce you to the new keyboard accelerators.

Using the New Keyboard Accelerators

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

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

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

Figure 4.1. Type the letters in the ToolTips along the top to open various ribbons. Typing the numbers in the numeric keytips access the Quick Access toolbar.

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

Pressing Alt+F always accesses the Office icon menu in all Office 2007 applications.

Alt+H always accesses the Home ribbon in all Office 2007 applications. The accelerator definitions for each ribbon will 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 ribbons: JT for PivotTable Tools Options and JY for PivotTable Tools Design (see Figure 4.2).

Figure 4.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 don’t 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 ribbon. You now see that every icon on the ribbon has a keytip.

When you have chosen a ribbon, 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 ribbon, as shown in Figure 4.3). Note that once you have typed Alt to display the accelerators in the ToolTips, you do not have to continue holding down the Alt key.

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

Selecting Options from a Gallery

Figure 4.4 shows the results of pressing Alt+H+F+T, which is the equivalent of selecting Home, Format Table. This opens the gallery of possible table styles. As you can see in Figure 4.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 4.4. After opening a gallery, you use the arrow keys to navigate through the gallery and press Enter to select a style.

In order to select a table style by using the keyboard, you need to 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, you 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, you press Enter to choose that item.

Backing Up One Level Through a Menu

Say that you press Alt+H to access the Home ribbon and then realize that you are in the wrong ribbon. 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, you can press Alt again.

Dealing with Keyboard Accelerator Confusion

If you want to select something on the Home ribbon in Figure 4.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, I keep thinking that the H keytip applies to Cut. Even though you are already on the Home ribbon, you need to press the H key to force Excel to show the ToolTips for the individual menu items on the Home ribbon.

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 2007 keytips. However, most of the dialog boxes do use the convention of having one letter of each command underlined (called a hotkey, in Microsoft parlance). In this case, you can press the underlined letter to select the command.

For example, you can 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 4.5. In order to select Values and Transpose in this dialog, you press V for Values and E for Transpose (because those are the letters underlined in the dialog). You could then press Enter instead of clicking the default OK button.

Figure 4.5. In a legacy dialog box, you can type the underlined letters to select options.

Using the Shortcut Keys

Excel 2007 automatically recognizes all the Ctrl shortcut keys that were used in previous editions of Excel. In fact, many of these keys are consistent across all Windows applications. Table 4.1 lists the common Windows Ctrl shortcut keys.

Table 4.1. Windows Shortcut Keys

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

Table 4.2. Shortcut Keys for Navigation

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

Table 4.3. Shortcut Keys for Selecting Data and Cells

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

Table 4.4. Shortcut Keys for Extending Selections

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

Table 4.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. You press Ctrl+1 to display the Format Cells dialog, and then you select the Border tab. Then you can use the shortcut keys shown in Table 4.6.

Table 4.6. Shortcut Keys for Borders

Using Excel 2003 Keyboard Accelerators

In previous 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 4.6, you can see that you can display the Edit menu by pressing Alt+E, and you can select Edit, Fill, Justify by pressing Alt+E+I+J.

Figure 4.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 fly-out 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. I always use 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 2007 is completely gone, you might be worried that you have to relearn all the shortcut keys. But 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, then 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, and 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 2007, 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 2007. Alt+H takes you to the Home ribbon instead of the few commands on the Help menu, and Alt+W takes you to the View ribbon.

Some of the keyboard shortcuts associated with the File menu in Excel 2003 continue to work in Excel 2007. Pressing Alt+F opens the Office icon menu. In Excel 2003, pressing Alt+F+O would perform File, Open. It happens that O is the shortcut on the Office icon menu for open, so pressing Alt+F+O in Excel 2007 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 4.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 4.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 2007, nothing happens. Several commands don’t make sense in the framework of Excel 2007, so they have been deprecated. For example View, Toolbars, Customize has no equivalent in Excel 2007 because the user interface does not allow for the customization of the ribbon; therefore, that command does not exist in Excel 2007.

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

Table 4.7. Excel Legacy Keyboard Commands

I am sure there are some people who liked using Alt+F+T+S in Excel 2003 for File, Print Area, Set Print Area. If you are one of those people, I’m sure you’re unhappy that your favorite shortcut key is no longer supported. However, most of the powerful and common shortcut keys are still available, so there is a good chance that for most people who upgrade, their knowledge of past shortcut keys will help them in Excel 2007.