4. 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 2016 still uses many of the old Alt keyboard shortcuts from Excel 2003. 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 Excel 2003 keyboard shortcuts still work, shows you some new shortcuts, and introduces you to the keyboard accelerators.
Learning the right 10 shortcuts from this chapter can make you twice as fast in Excel.
Using Keyboard Accelerators
The goal of the Excel 2016 keyboard accelerators is to enable 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 2016 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 KeyTip above each command. In addition, numeric KeyTips appear over each icon in the Quick Access Toolbar (QAT; see Figure 4.1). Press the F10 key to display or hide the KeyTips.
It is possible to memorize the KeyTips for the ribbon tabs. Pressing Alt+F accesses the File menu in all Office 2016 applications. Alt+H accesses the Home tab in all Office 2016 applications. Alt+Q puts the cursor inside the Tell Me What You Want To Do box. 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 remain, and two new KeyTips appear for the contextual tabs: JT for PivotTable Tools Options and JY for PivotTable Tools Design (see Figure 4.2).
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 4.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 4.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 4.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. However, because 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 4.4 shows the results of pressing Alt+H+J, which is the equivalent of selecting Home, Cell Styles. This opens the gallery of cell styles. As you can see in Figure 4.4, you can invoke the New Cell Style and Merge Styles commands at the bottom of the gallery by pressing N and M, 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.
To select a cell 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 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 4.2, you might be frustrated because you can see the menu choices but no ToolTips appear 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 might 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 2016 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 4.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.
Using the Shortcut Keys
The following five tables provide what I believe to be a comprehensive list of shortcut keys. I have collected these over the many versions of Excel. For some reason, Excel Help no longer lists all the shortcut keys. I count 75 shortcut keys in the following tables that are no longer documented in Excel Help. I realize this is a mind-numbingly long list, but I want to include it here because the Excel team no longer provides a complete list.
If you decide to learn and start using one new shortcut key every week, you will quickly become very fast at using Excel. After Table 4.5, I identify my favorite shortcut keys from this list.
Table 4.1 lists the common Windows Ctrl shortcut keys.
Table 4.2 illustrates the shortcut keys you use to navigate in Excel.
Table 4.3 shows the shortcut keys you use to select data and cells.
Table 4.4 shows the shortcut keys you use to extend a selection. In extend mode, clicking any cell selects from the active cell to the clicked cell.
Table 4.5 shows the shortcut keys you use for entering, editing, formatting, and calculating data.
Using My Favorite Shortcut Keys
The problem with a list of hundreds of shortcut keys is that it is overwhelming. You cannot possibly absorb 238 new shortcut keys and start using them. The following sections cover some of my favorite shortcuts. Try to incorporate one new shortcut key every week into your Excel routine.
Quickly Move Between Worksheets
Ctrl+Page Down jumps to the next worksheet. Ctrl+Page Up jumps to the previous worksheet. Suppose that you have 12 worksheets named Jan, Feb, Mar, ..., Dec. If you are currently on the Jan worksheet, hold down Ctrl and press Page Down five times to move to Jun.
Jumping to the Bottom of Data with Ctrl+Arrow
Provided there are no blank cells in your data, press Ctrl+down arrow to move to the last row in the data set. Use Ctrl+up arrow to move to the first row in the data set.
Add the Shift key to select from the current cell to the bottom. If you have data in A2:J987654 and are in A2, you can hold down Ctrl+Shift while pressing the down arrow and then the right arrow to select all the data rows but exclude the headings in row 1.
Selecting the Current Region with Ctrl+*
Press Ctrl+* to select the current range. The current range is the whole data set, in all directions from the current cell until Excel hits the edge of the worksheet or a completely blank row and column. On a desktop computer, pressing Ctrl and the asterisk on the numeric keypad does the trick.
Jumping to the Next Corner of a Selection
You’ve just selected A2:J987654, but you are staring at the bottom-right corner of your data. Press Ctrl+period to move to the next corner of your data. Because you are at the bottom-right corner, it takes two presses of Ctrl+period to move to the top-left corner. Although this moves the active cell, it does not undo your selection. Although I always use Ctrl+period twice, I should probably learn Ctrl+backspace to bring the active cell back into view. That will be my new trick for next week.
Pop Open the Right-Click Menu Using Shift+F10
When I do my seminars, people always ask why I don’t use the right-click menus. I don’t use them because my hand is not on the mouse! Pressing Shift+F10 opens the right-click menu. Use the up/down arrow keys to move to various menu choices and the right-arrow key to open a fly-out menu. When you get to the item you want, press Enter to select it.
Crossing Tasks Off Your List with Ctrl+5
I love to make lists, and I love to cross stuff off my list. It makes me feel like I’ve gotten stuff done. Select a cell and press Ctrl+5 to apply strikethrough to the cell.
Date Stamp or Time Stamp Using Ctrl+; or Ctrl+:
Here is an easy way to remember this shortcut. What time is it right now? It is 11:21 here. There is a colon in the time. Press Ctrl+colon to enter the current time in the active cell.
Need the current date? Same keystroke, minus the Shift key. Pressing Ctrl+semicolon enters the current time.
Note that this is not the same as using =NOW() or =TODAY(). Those functions change over time. These shortcuts mark the time or date that you pressed the key, and the value does not change.
Repeating the Last Task with F4
Suppose that you just selected a cell and did Home, Delete, Delete Cells, Delete Entire Row, OK. You need to delete 24 more rows in various spots throughout your data set.
Select a cell in the next row to delete and press F4, which repeats the last command but on the currently selected cell.
Select a cell in the next row to delete and press F4. Before you know it, all 24 rows are deleted without your having to click Home, Delete, Delete Cells, Delete Entire Row, OK 24 times.
The F4 key works with 92% of the commands you will use. Try it. You’ll love it. It’ll be obvious when you try to use one of the unusual commands that cannot be redone with F4.
Adding Dollar Signs to a Reference with F4
That’s right—two of my favorites in a row use F4. When you are entering a formula and you need to change A1 to $A$1, click F4 while the insertion point is touching A1. You can press F4 again to freeze only the row with A$1. Press F4 again to freeze the column with $A1. Press again to toggle back to A1.
Choosing Items from a Slicer
It is somewhat bizarre, but you can now use shortcut keys to jump in to a slicer. Using the new Multi-Select icon, you can then select or deselect items in the slicer. To get to the slicer, use Ctrl+G to display the Go To dialog. Press S to open Go To Special. Type B for Objects and press Enter to select the first object on the worksheet. You might have to press Tab to get to the slicer, but then you can navigate through the slicer using the arrow keys. Pressing Enter or the spacebar on an item toggles that item. To exit the slicer, use Ctrl+G, type a cell address such as A1, and press Enter.
Finding the One Thing That Takes You Too Much Time
The shortcuts in this section are the ones I learned over the course of 20 years. They were all for tasks that I had to do repeatedly. In your job, watch for any tasks you are doing over and over, especially things that take several mouse clicks. When you identify one, try to find a shortcut key that will save you time.
Tip
When you perform commands with the mouse, do all the steps except the last one. Hover over the command until the ToolTip appears. Many times, the ToolTip tells you of the keyboard shortcut.
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 4.6, you can display the Edit menu by pressing Alt+E, and you can select Edit, Fill, Justify by pressing Alt+E+I+J.
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, 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 ribbon has replaced the legacy menu, 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 still work as they did in Excel 2003.
If you are an intermediate Excel user who regularly uses the Excel 2003 keyboard accelerators but has 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 are File, Edit, View, Insert, Format, Tools, Data, Window, and Help. The keyboard accelerator commands in Excel 2003 are 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 2016, 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 2016. 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.
Tip
You will have to train yourself to pause briefly after typing the first letter in the legacy shortcut key sequence. For example, press Alt+E, pause for a brief moment to allow Excel to display the Office Access Key window, and then press S, V for Edit, Paste Special, Values. If you do not pause, the second letter is lost because Excel displays the pop-up Office Key Sequence window.
Some of the keyboard shortcuts associated with the File menu in Excel 2003 continue to work in Excel 2016. 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 2016 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 2016, nothing happens. Several commands don’t make sense in the framework of Excel 2016, so they have been deprecated.
Table 4.6 lists the legacy keyboard commands and indicates which of them continue to work in Excel 2016.
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 2016. Instead, use Alt+P+R+S. 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 2016.