Chapter 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 2019 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 ten shortcuts from this chapter can make you twice as fast in Excel.
Using Keyboard Accelerators
The goal of the Excel 2019 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 2019 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. Also, 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 2019 applications. Alt+H accesses the Home tab in all Office 2019 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.
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 box launcher in Figure 4.3 makes sense in that it opens the legacy Format dialog box 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 menu. 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.
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 Menu Lists
If you press Alt+H+F+S, which is the equivalent of selecting Home, Font Size, the font size in the drop-down menu is selected. You can either type a font size and press Enter or press the down-arrow key to open the drop-down menu. 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 (previously shown 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.
Troubleshooting
There are some parts of the Excel window that seem impossible to access using keyboard shortcuts. For example, how can you rename a worksheet using only the keyboard?
It is extremely subtle, but there is something called the F6 Loop. When you press F6, the focus will move from the worksheet to the sheet tabs. Press F6 again to jump to any task pane, then to the zoom controls, and then back to the worksheet.
Once you use F6 to activate an area, you can use Tab or arrow keys to activate different controls within that area.
Say that you have cell A1 selected on Sheet1 and you want to rename Sheet2. Press F6 to move focus to the sheet tabs. Press the right arrow to move from Sheet1 to Sheet2. Press Shift+F10 (or the Program key) to open the right-click menu. Use the up arrow eight times to move to Rename. Press Enter to select the Rename command.
Starting from cell B5, press Alt, H, FD, O to begin Select Objects mode.
Press Tab once to activate the Rep slicer. At this point, you can use the arrow keys to reposition the slicer.
Press Tab a second time to move to the Multi-Select icon inside the Rep slicer. Press Spacebar to toggle multi-select on or off.
Press Tab again to move to the Clear Filter icon. Press Spacebar to clear the filter.
Press Tab again to move into the slicer items. This step is really subtle because exactly one slicer will have a dotted line. A black dotted line on a field of dark blue is really hard to see. Plus, while you would think that the selected slicer tile would always be Andy, it is not. Excel remembers your last selected slicer and starts there. Perhaps using arrows to move to the next slicer tile will allow your eye to see what is changing. Spacebar will toggle the slicer tile on or off if the Multi-Select is enabled. If Multi-Select is off, then pressing Spacebar on one slicer tile will turn all the others off and turn that tile on. By the way, you can use Shift+Tab to move back to the Clear Filter and then Shift+Tab again to move back to Multi-Select.
Pressing Tab from the Slicer Items of the Rep slicer will move to the next object in the worksheet, the Oval.
Press Tab again to move to the Star.
Press Tab again to move to the Product slicer. At this point, you are essentially at Step 2 for the next slicer.
When you are done accessing the slicers, use the Escape key to exit Select Objects mode and return to cell B5.
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 2019 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 box, press V for Values and E for Transpose, because those are the letters underlined in the dialog box. 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.1 Windows Shortcut Keys
Key Combination |
Action |
|---|---|
Ctrl+C |
Copy. |
Ctrl+X |
Cut. |
Ctrl+V |
Paste. |
Ctrl+Alt+V |
Paste Special. |
Ctrl+Z |
Undo. |
Ctrl+Y or F4 |
Redo. |
Ctrl+A or Ctrl+Shift+spacebar |
Select all. If the active cell contains data and is adjacent to other cells with data, Ctrl+A initially selects the current region. Pressing Ctrl+A again selects all. |
Ctrl+S or Shift+F12 or Alt+Shift+F2 |
Save. |
Alt+2 or F12 |
Save As. |
Ctrl+O or Ctrl+F12 |
Open. |
Ctrl+W or Ctrl+F4 |
Close workbook window. |
Ctrl+N |
New workbook. |
Ctrl+P or Ctrl+F2 or Ctrl+Shift+F12 |
Display Print tab in File menu. |
Ctrl+B |
Bold. |
Ctrl+U |
Underline. |
Ctrl+I |
Italic. |
Ctrl+F |
Find. |
Ctrl+H |
Replace. |
Ctrl+Shift+F or Ctrl+Shift+P |
Font tab of Format Cells dialog box. |
Ctrl+G or F5 |
Go To dialog. |
Ctrl+T or Ctrl+L |
Format as Table. |
Ctrl+E |
Flash Fill. |
Ctrl+Q |
Quick Analysis options. |
Table 4.2 illustrates the shortcut keys you use to navigate in Excel.
Table 4.2 Shortcut Keys for Navigation
Shortcut Key |
Action |
|---|---|
Ctrl+Home |
Moves to cell A1 or the top-left unhidden cell in the worksheet. |
Ctrl+End |
Moves to the last cell in the used range of the worksheet. If the cursor is in the formula bar, it moves to the end of the formula text. |
Page Down |
Moves one screen down in the worksheet. |
Page Up |
Moves one screen up in the worksheet. |
Alt+Page Down |
Moves one screen right in the worksheet. |
Alt+Page Up |
Moves one screen left in the worksheet. |
Ctrl+Page Up |
Moves to the previous worksheet. |
Ctrl+Page Down |
Moves to the next worksheet. |
Ctrl+Shift+F6 |
Moves to the previous window. |
Shift+F11 |
Inserts a new worksheet. |
Ctrl+F11 |
Inserts an Excel 4 macro sheet. |
Alt+Tab |
Switches to the next program. |
Alt+Shift+Tab |
Switches to the previous program. |
Ctrl+Esc |
Displays the Windows Start menu. |
Ctrl+F5 |
Restores the window size of the current workbook. |
F6 |
Switches between the worksheet, ribbon, task pane, and zoom controls. If the workbook has been split, this also switches between panes. |
Ctrl+F6 |
Switches to the next open workbook window when more than one workbook is open. |
Ctrl+Shift+F6 |
Switches to the previous workbook window. |
Ctrl+F9 |
Minimizes the window. |
Ctrl+F10 |
Maximizes the window. |
End |
Toggles into End mode. Displays End Mode in the status bar. When in End mode, press an arrow key to move to the edge of the current region. If the active cell is already at the edge of a current region or is a blank cell, this jumps to the next nonblank cell or to the edge of the worksheet. |
End Home |
Moves to the last used range in the worksheet. Similar to Ctrl+End. |
Ctrl+arrow key or End followed by an arrow key |
Moves to the edge of the current region. If the active cell is at the edge of a current region or is a blank cell, this jumps to the next nonblank cell or to the edge of the worksheet. |
Home |
Moves to the beginning of the row. |
Ctrl+backspace |
Scrolls to display the active cell. |
F5 |
Displays the Go To dialog box. |
Shift+F5 |
Displays the Find dialog box. |
Shift+F4 |
Find Next. |
Ctrl+. (period) |
Moves to the next corner of the selected range. |
Table 4.3 shows the shortcut keys you use to select data and cells.
Table 4.3 Shortcut Keys for Selecting Data and Cells
Shortcut Key |
Action |
|---|---|
Ctrl+spacebar |
When an object is selected, this selects all objects on the worksheet. |
Ctrl+spacebar |
If used outside a table, this selects the entire column. If used inside a table, it toggles between selecting the data, the data and headers, and the entire column. |
Shift+spacebar |
Selects the entire row. If inside a table, this toggles between selecting the table row and the entire row. |
Ctrl+Shift+spacebar or Ctrl+A |
Selects the entire worksheet, unless the active cell is a region of two or more nonblank cells, in which case it selects the current region. Repeat the keystroke to select the entire worksheet. When the active cell is in a table, the first press selects the data rows of the table. The second press expands to include the headings and total row. The third press selects the entire worksheet. |
Shift+backspace |
With multiple cells selected, this reverts the selection to only the active cell. |
Ctrl+ * |
Selects the current region. In a pivot table, this selects the entire table. |
Ctrl+/ |
Selects the array containing the active cell. |
Ctrl+Shift+O (letter O) |
Selects all cells that contain comments. |
Ctrl+ |
In a selected row, this selects the cells that do not match the formula in the active cell. |
Ctrl+Shift+| |
In a selected column, this selects the cells that do not match the formula in the active cell. |
Ctrl+[ (opening square bracket) |
Selects all cells directly referenced by formulas in the selection. |
Ctrl+Shift+{ (opening brace) |
Selects all cells directly or indirectly referenced by formulas in the selection. |
Ctrl+] (closing square bracket) |
Selects cells that contain formulas that directly reference the active cell. |
Ctrl+Shift+} (closing brace) |
Selects cells that contain formulas that directly or indirectly reference the active cell. |
Alt+; (semicolon) |
Selects the visible cells in the current selection. |
Ctrl+Shift+Page Down |
Adds the next worksheet to the selected sheets and makes the next worksheet the active sheet. This puts the workbook in group mode if it is not already in group mode. Pressing Ctrl+Shift+Page Down three times puts the current sheet and the next three sheets in group mode. Any changes made to the visible sheet are also made to all sheets in group mode. To exit group mode, right-click a sheet tab and choose Ungroup Sheets. |
Ctrl+Shift+Page Up |
Adds the previous worksheet to the selected sheets. This puts the workbook in group mode if it was not already in group mode. |
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.4 Shortcut Keys for Extending Selections
Shortcut Key |
Action |
|---|---|
F8 |
Turns extend mode on or off. In extend mode, EXT appears in the status line, and the arrow keys extend the selection. |
Shift+F8 |
Adds another range of cells to the selection. You can use the arrow keys to move to the start of the range you want to add. Then press F8 and the arrow keys to select the next range. |
Shift+arrow key |
Extends the selection by one cell. |
Ctrl+Shift+arrow key |
Extends the selection to the last nonblank cell in the same column or row as the active cell. |
Shift+Home |
Extends the selection to the beginning of the row. |
Ctrl+Shift+Home |
Extends the selection to the beginning of the worksheet. |
Ctrl+Shift+End |
Extends the selection to the last used cell on the worksheet in the lower-right corner. If the cursor is in the formula bar, this selects to the end of the formula. |
Shift+Page Down |
Extends the selection down one screen. |
Shift+Page Up |
Extends the selection up one screen. |
End Shift+arrow key |
Extends the selection to the last nonblank cell in the same column or row as the active cell. |
End+Shift+Home |
Extends the selection to the last used cell on the worksheet in the lower-right corner. |
End Shift+Enter |
Extends the selection to the last cell in the current row. |
Scroll Lock+Shift+Home |
Extends the selection to the cell in the upper-left corner of the window. |
Scroll Lock+Shift+End |
Extends the selection to the cell in the lower-right corner of the window. |
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
Shortcut |
Key Action |
|---|---|
Enter |
Completes a cell entry and selects the next cell. Often moves down one cell, but you can override this with File, Options, Advanced. In a data form, this moves to the first field in the next record. In a dialog box, this performs the action for the default button (often OK). After F10 is used to activate the menu bar, Enter selects the chosen menu item. |
Alt+Enter |
Starts a new line in the same cell. |
Ctrl+Enter |
Fills the selected cell range with the current entry. |
Shift+Enter |
Completes a cell entry and selects the previous cell. Often the cell above, but you can override with File, Options, Advanced. If the Move Selection Direction is set to the right, then pressing Shift+Enter will move to the left. |
Tab |
Completes a cell entry and selects the next cell to the right. Moves between unlocked cells in a protected worksheet. Moves to the next option in a dialog box. In a multicell selection, selects the next cell. |
Shift+Tab |
Completes a cell entry and selects the previous cell to the left. In a dialog box, this moves to the previous option. |
Esc |
Cancels a cell entry. Closes Full-Screen mode. Closes an open menu dialog box or message window. |
Arrow keys |
Moves one cell up, down, left, or right. If in edit mode, this moves one character up, down, left, or right. |
Home |
Moves to the beginning of the line. Moves to the cell in the upper-left corner of the window when Scroll Lock is turned on. Selects the first command on the menu when a menu is visible. |
F4 or Ctrl+Y |
Repeats the last action. When a cell reference is selected in a formula, F4 toggles between the various combinations of relative and absolute references. |
Ctrl+Alt+L |
Reapplies the Filter and re-sorts the data if you used any sort commands in the filter drop-down menus. Use when you are filtering a column of formulas whose value might have changed. |
Ctrl+Shift+F3 |
Displays the Create Names from Selection dialog box to enable you to create names from row and column labels. |
Ctrl+D |
Fills down. |
Ctrl+R |
Fills to the right. |
Ctrl+F3 |
Displays the Name Manager. |
Ctrl+K |
Inserts a hyperlink or enables you to edit the selected hyperlink. |
Ctrl+; (semicolon) |
Enters the date. |
Ctrl+Shift+: (colon) |
Enters the time. |
Alt+down arrow |
When a drop-down menu is selected, this opens the drop-down menu. Otherwise, it displays a drop-down menu of the values in the current column of a range to enable you to select a cell value from the menu. |
Ctrl+Z or Alt+backspace |
Undoes the last action. |
= (equal sign) |
Starts a formula. |
Backspace |
In the formula bar or while you’re editing a cell, this deletes one character to the left. When you’re not in edit mode, this clears the contents of the current cell and puts the cell in edit mode. |
Enter |
Completes a cell entry from the cell or formula bar. |
Ctrl+Shift+Enter |
Enters a formula as an array formula. |
Shift+F3 |
In a formula, this displays the Insert Function dialog box. |
Ctrl+A |
When the insertion point is to the right of a function name in a formula, this displays the Function Arguments dialog box. See also Select All in Table 4.1. |
Ctrl+Shift+A |
When the insertion point is to the right of a function name in a formula, this inserts the argument names and parentheses. |
F3 |
Pastes a defined name into a formula. |
Alt+= (equal sign) |
Inserts an AutoSum formula with the SUM function. |
Ctrl+Shift+” (quotation mark) |
Copies the value from the cell above the active cell into the cell or the formula bar. |
Ctrl+’ (apostrophe) |
Copies a formula from the cell above the active cell into the cell or the formula bar and places the cell in edit mode. Note that the formula is an exact copy; any references are not moved down by a row. |
Ctrl+` (backtick) |
Alternates between displaying cell values and displaying formulas. |
F9 |
Calculates all worksheets in all open workbooks. When a portion of a formula is selected, calculate the selected portion and then press Enter or Ctrl+Shift+Enter (for array formulas) to replace the selected portion with the calculated value. |
Shift+F9 |
Calculates the active worksheet. |
Ctrl+Alt+F9 |
Calculates all worksheets in all open workbooks, regardless of whether they have changed since the last calculation. |
Ctrl+Alt+Shift+F9 |
Rechecks dependent formulas and then calculates all cells in all open workbooks, including cells not marked as needing to be calculated. |
F1 |
Displays Help. |
F2 |
Edits the active cell and positions the insertion point at the end of the cell contents. If in-cell editing is turned off, this moves the insertion point to the formula bar. When you’re editing a formula or a reference in a dialog box, F2 toggles between Point and Enter modes. If pressing backspace starts inserting cell references instead of moving back a character, press F2 and try again. |
Delete |
Removes cell contents (data and formulas) from selected cells without affecting cell formats or comments. In editing mode, this deletes the character to the right of the insertion point or deletes the selection. |
Ctrl+Delete |
Deletes text to the end of the line. |
F7 |
Displays the Spelling dialog box. |
Shift+F2 |
Adds or edits a cell comment. |
Ctrl+– (minus sign) |
Displays the Delete dialog box. |
Ctrl+Shift++ (plus sign) |
Displays the Insert dialog box to insert blank cells. |
Alt+’ (apostrophe) |
Displays the Style dialog box. |
Ctrl+1 |
Displays the Format Cells dialog box when cells are selected. When a chart element or object is selected, this displays the Format task pane for that object. |
Alt+’ |
Displays the Style dialog box. |
Ctrl+2 or Ctrl+B |
Toggles bold formatting. |
Ctrl+3 or Ctrl+I |
Toggles italic formatting. |
Ctrl+4 or Ctrl+U |
Toggles underline formatting. |
Ctrl+Shift+~ |
Applies the General number format. |
Ctrl+Shift+$ |
Applies the Currency format with two decimal places (negative numbers in parentheses). |
Ctrl+Shift+% |
Applies the Percentage format with no decimal places. |
Ctrl+Shift+^ |
Applies the Scientific number format with two decimal places. |
Ctrl+Shift+# |
Applies the Date format with the day, month, and year. |
Ctrl+Shift+@ |
Applies the Time format with the hour and minute as well as AM or PM. |
Ctrl+Shift+! |
Applies the Number format with two decimal places, thousands separator, and a minus sign (–) for negative values. |
Ctrl+5 |
Applies or removes strikethrough. |
Ctrl+9 |
Hides the selected rows. |
Ctrl+Shift+( (opening parenthesis) |
Unhides any hidden rows within the selection. |
Ctrl+0 (zero) |
Hides the selected columns. |
Ctrl+Shift+) (closing parenthesis) |
Unhides any hidden columns within the selection. Although this shortcut key is shown as a ToolTip in the Home tab, it has not worked since Excel 2010, and the Excel team has no immediate plans to fix it. |
Ctrl+Shift+& |
Applies the outline border to the selected cells. |
Ctrl+Shift+_ (underscore) |
Removes the outline border from the selected cells. |
Ctrl+U |
Toggles the formula bar between collapsed and expanded. |
Ctrl+6 |
Toggles between hiding and displaying objects. |
Ctrl+8 |
In group and outline mode, this toggles the display of outline symbols. |
Ctrl+F1 |
Collapses or expands the ribbon. |
Alt or F10 |
Displays KeyTips. |
Shift+F10 |
Opens the right-click menu for the selection. |
Alt+Shift+F10 |
Displays the menu or message for an Error Checking button. |
Alt+F11 |
Opens the Visual Basic for Applications Editor. |
Alt+F8 |
Opens the Macros dialog box. |
Spacebar |
In a dialog box, this selects or clears a check box or performs the action for a selected button. |
Ctrl+Tab |
In a dialog box, this switches to the next tab. |
Ctrl+Shift+Tab |
In a dialog box, this switches to the previous tab. |
Arrow keys |
In a dialog box or open menu, this moves between options in an open drop-down menu or between options in a group of options. |
End |
When a menu is open, this selects the last item in the menu. |
Alt+Shift+F1 |
Inserts a new worksheet to the left of the current worksheet. |
F11 |
Creates a chart of the data in the current range in a new chart sheet. |
Alt+F1 |
Creates a chart of the data in the current range in the current worksheet. |
Alt+spacebar |
Opens the Control menu for the Excel window. The Control menu is attached to the XL logo in the top left of the window. |
Ctrl+F4 |
Closes the selected workbook window. |
Alt+F4 |
Closes Excel. |
Ctrl+F10 |
Maximizes or restores the selected workbook window. |
Ctrl+F7 |
When a workbook is not maximized, this moves the entire workbook window. Press Ctrl+F7. Use the arrow keys to move the window. Press Enter when you’re finished or Esc to cancel. |
Ctrl+F8 |
Performs the Size command when a workbook is not maximized. Press Ctrl+F8. Using the left or right arrow key expands the width of the window by moving the right edge of the window. Using the up or down arrow key moves the bottom edge of the window to shrink or stretch the window. |
Shift+F6 |
Moves focus between the worksheet, ribbon, status bar, and task pane. For example, when the focus is on the status bar, you can use the arrows to move between the Record Macro, Normal, Page Layout, Page Break Preview, and Zoom icons. |
Ctrl+F6 |
Moves between windows of a workbook. This would apply only if you used View, New Window. |
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.
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 percent 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 into 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 box. 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 have advanced Excel skills, 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 regularly use the Excel 2003 keyboard accelerators but have 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 2019, 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 2019. 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 2019. 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 2019 also performs File, Open.
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.
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.
Determining Which Commands Work in Legacy Mode
If you try a command that no longer works in Excel 2019, nothing happens. Several commands don’t make sense in the framework of Excel 2019, so they have been deprecated.
Table 4.6 lists the legacy keyboard commands and indicates which of them continue to work in Excel 2019.
Table 4.6 Excel Legacy Keyboard Commands
Legacy Shortcut |
Excel 2019? |
Command |
|---|---|---|
Alt+F+N |
Yes |
File, New |
Alt+F+O |
Yes |
File, Open |
Alt+F+C |
Yes |
File, Close |
Alt+F+S |
Yes |
File, Save |
Alt+F+A |
Yes |
File, Save As |
Alt+F+G |
No |
File, Save as Web Page |
Alt+F+W |
No |
File, Save Workspace |
Alt+F+H |
No |
File, File Search |
Alt+F+M |
No |
File, Permission |
Alt+F+E |
No |
File, Check Out or Check In (toggle) |
Alt+F+R |
No |
File, Version History |
Alt+F+B |
No |
File, Web Page Preview |
Alt+F+U |
No |
File, Page Setup |
Alt+F+T+S |
No |
File, Print Area, Set Print Area |
Alt+F+T+C |
No |
File, Print Area, Clear Print Area |
Alt+F+V |
No |
File, Print Preview |
Alt+F+P |
Yes as Alt+F+P+P |
File, Print |
Alt+F+D+M |
No |
File, Send To, Mail Recipient |
Alt+F+D+S |
No |
File, Send To, Original Sender |
Alt+F+D+C |
No |
File, Send To, Mail Recipient (for Review) |
Alt+F+D+A |
No |
File, Send To, Mail Recipient (As Attachment) |
Alt+F+D+R |
No |
File, Send To, Routing Recipient |
Alt+F+D+E |
No |
File, Send To, Exchange Folder |
Alt+F+D+O |
No |
File, Send To, Online Meeting Participant |
Alt+F+D+X |
No |
File, Send To, Recipient Using Internet Fax Service |
Alt+F+I |
No |
File, Properties |
Alt+F+1 |
Yes |
File, 1 |
Alt+F+2 |
Yes |
File, 2 |
Alt+F+3 |
Yes |
File, 3 |
Alt+F+4 |
Yes |
File, 4 |
Alt+F+5 |
Yes |
File, 5 |
Alt+F+6 |
Yes |
File, 6 |
Alt+F+7 |
Yes |
File, 7 |
Alt+F+8 |
Yes |
File, 8 |
Alt+F+9 |
Yes |
File, 9 |
Alt+F+T |
No |
File, Options |
Alt+F+X |
Yes |
File, Exit |
Alt+E+U |
Yes |
Edit, Undo |
Alt+E+R |
Yes |
Edit, Repeat |
Alt+E+T |
Yes |
Edit, Cut |
Alt+E+C |
Yes |
Edit, Copy |
Alt+E+B |
Yes |
Edit, Office Clipboard |
Alt+E+P |
Yes |
Edit, Paste |
Alt+E+S |
Yes |
Edit, Paste Special |
Alt+E+H |
No |
Edit, Paste as Hyperlink |
Alt+E+I+D |
Yes |
Edit, Fill, Down |
Alt+E+I+R |
Yes |
Edit, Fill, Right |
Alt+E+I+U |
Yes |
Edit, Fill, Up |
Alt+E+I+L |
Yes |
Edit, Fill, Left |
Alt+E+I+A |
Yes |
Edit, Fill, Across Worksheets |
Alt+E+I+S |
Yes |
Edit, Fill, Series |
Alt+E+I+J |
Yes |
Edit, Fill, Justify |
Alt+E+A+A |
Yes |
Edit, Clear, All |
Alt+E+A+F |
Yes |
Edit, Clear, Formats |
Alt+E+A+C |
Yes |
Edit, Clear, Contents |
Alt+E+A+M |
Yes |
Edit, Clear, Comments |
Alt+E+D |
Yes |
Edit, Delete |
Alt+E+L |
Yes |
Edit, Delete Sheet |
Alt+E+M |
Yes |
Edit, Move or Copy Sheet |
Alt+E+F |
Yes |
Edit, Find |
Alt+E+E |
Yes |
Edit, Replace |
Alt+E+G |
Yes |
Edit, Go To |
Alt+E+K |
Yes |
Edit, Links |
Alt+E+O |
No |
Edit, Object |
Alt+E+O+V |
No |
Edit, Object, Convert |
Alt+V+N |
Yes |
View, Normal |
Alt+V+P |
Yes |
View, Page Break Preview |
Alt+V+K |
No |
View, Task Pane |
Alt+V+T+C |
No |
View, Toolbars, Customize |
Alt+V+F |
Yes |
View, Formula Bar |
Alt+V+S |
No |
View, Status Bar |
Alt+V+H |
Yes |
View, Header and Footer |
Alt+V+C |
Yes |
View, Comments |
Alt+V+V |
Yes |
View, Custom Views |
Alt+V+U |
Yes |
View, Full Screen (Caution: Use the maximize button to return.) |
Alt+V+Z |
Yes |
View, Zoom |
Alt+I+E |
Yes |
Insert, Cells |
Alt+I+R |
Yes |
Insert, Rows |
Alt+I+C |
Yes |
Insert, Columns |
Alt+I+W |
Yes |
Insert, Worksheet |
Alt+I+H |
Yes |
Insert, Chart |
Alt+I+S |
Yes |
Insert, Symbol |
Alt+I+B |
Yes |
Insert, Page Break |
Alt+I+A |
Yes |
Insert, Reset All Page Breaks |
Alt+I+F |
Yes |
Insert, Function |
Alt+I+N+D |
Yes |
Insert, Name, Define |
Alt+I+N+P |
Yes |
Insert, Name, Paste |
Alt+I+N+C |
Yes |
Insert, Name, Create |
Alt+I+N+A |
Yes |
Insert, Name, Apply |
Alt+I+N+L |
Yes |
Insert, Name, Label |
Alt+I+M |
Yes |
Insert, Comment (Legacy) |
Alt+I+A |
Yes |
Insert, Ink Annotations |
Alt+I+P+C |
Yes |
Insert, Picture, Clip Art |
Alt+I+P+F |
Yes |
Insert, Picture, From File |
Alt+I+P+S |
Yes |
Insert, Picture, From Scanner or Camera |
Alt+I+P+D |
Yes |
Insert, Picture, Ink Drawing and Writing |
Alt+I+P+A |
No |
Insert, Picture, AutoShapes |
Alt+I+P+W |
No |
Insert, Picture, WordArt |
Alt+I+P+O |
No |
Insert, Picture, Organization Chart |
Alt+I+G |
No |
Insert, Diagram |
Alt+I+O |
Yes |
Insert, Object |
Alt+I+I |
Yes |
Insert, Hyperlink |
Alt+O+E |
Yes |
Format, Cells |
Alt+O+R+E |
Yes |
Format, Row, Height |
Alt+O+R+A |
Yes |
Format, Row, AutoFit |
Alt+O+R+H |
Yes |
Format, Row, Hide |
Alt+O+R+U |
Yes |
Format, Row, Unhide |
Alt+O+C+W |
Yes |
Format, Column, Width |
Alt+O+C+A |
Yes |
Format, Column, AutoFit Selection |
Alt+O+C+H |
Yes |
Format, Column, Hide |
Alt+O+C+U |
Yes |
Format, Column, Unhide |
Alt+O+C+S |
Yes |
Format, Column, Standard Width |
Alt+O+H+R |
Yes |
Format, Sheet, Rename |
Alt+O+H+H |
Yes |
Format, Sheet, Hide |
Alt+O+H+U |
Yes |
Format, Sheet, Unhide |
Alt+O+H+B |
Yes |
Format, Sheet, Background |
Alt+O+H+T |
Yes |
Format, Sheet, Tab Color |
Alt+O+A |
No |
Format, AutoFormat |
Alt+O+D |
Yes |
Format, Conditional Formatting |
Alt+O+S |
Yes |
Format, Style |
Alt+T+S |
Yes |
Tools, Spelling |
Alt+T+R |
Yes |
Tools, Research |
Alt+T+K |
Yes |
Tools, Error Checking |
Alt+T+H+H |
No |
Tools, Speech, Speech Recognition |
Alt+T+H+T |
No |
Tools, Speech, Show Text to Speech Toolbar |
Alt+T+D |
Yes |
Tools, Shared Workspace |
Alt+T+B |
Yes |
Tools, Share Workbook |
Alt+T+T+H |
Yes |
Tools, Track Changes, Highlight Changes |
Alt+T+T+A |
Yes |
Tools, Track Changes, Accept or Reject Changes |
Alt+T+W |
Yes |
Tools, Compare and Merge Workbooks |
Alt+T+P+P |
Yes |
Tools, Protection, Protect Sheet |
Alt+T+P+A |
Yes |
Tools, Protection, Allow Users to Edit Ranges |
Alt+T+P+W |
Yes |
Tools, Protection, Protect Workbook |
Alt+T+P+S |
Yes |
Tools, Protection, Protect and Share Workbook |
Alt+T+N+M |
Yes |
Tools, Online Collaboration, Meet Now |
Alt+T+N+S |
Yes |
Tools, Online Collaboration, Schedule Meeting |
Alt+T+N+W |
Yes |
Tools, Online Collaboration, Web Discussions |
Alt+T+N+N |
Yes |
Tools, Online Collaboration, End Review |
Alt+T+G |
Yes |
Tools, Goal Seek |
Alt+T+E |
Yes |
Tools, Scenarios |
Alt+T+U+T |
Yes |
Tools, Formula Auditing, Trace Precedents |
Alt+T+U+D |
Yes |
Tools, Formula Auditing, Trace Dependents |
Alt+T+U+E |
Yes |
Tools, Formula Auditing, Trace Error |
Alt+T+U+A |
Yes |
Tools, Formula Auditing, Remove All Arrows |
Alt+T+U+F |
Yes |
Tools, Formula Auditing, Evaluate Formula |
Alt+T+U+W |
Yes |
Tools, Formula Auditing, Show Watch Window |
Alt+T+U+M |
Yes |
Tools, Formula Auditing, Formula Auditing Mode |
Alt+T+U+S |
No |
Tools, Formula Auditing, Show Formula Auditing Toolbar |
Alt+T+V |
Yes |
Tools, Solver |
Alt+T+M+M |
Yes |
Tools, Macro, Macros |
Alt+T+M+R |
Yes |
Tools, Macro, Record New Macro |
Alt+T+M+S |
Yes |
Tools, Macro, Security |
Alt+T+M+V |
Yes |
Tools, Macro, Visual Basic Editor |
Alt+T+M+E |
No |
Tools, Macro, Microsoft Script Editor |
Alt+T+I |
Yes |
Tools, Add-Ins |
Alt+T+C |
No |
Tools, COM Add-Ins |
Alt+T+A |
Yes |
Tools, AutoCorrect Options |
Alt+T+C |
No |
Tools, Customize |
Alt+T+O |
No |
Tools, Options |
Alt+T+D |
No |
Tools, Data Analysis |
Alt+D+S |
Yes |
Data, Sort |
Alt+D+F+F |
Yes |
Data, Filter, AutoFilter |
Alt+D+F+S |
Yes |
Data, Filter, Show All |
Alt+D+F+A |
Yes |
Data, Filter, Advanced Filter |
Alt+D+O |
Yes |
Data, Form |
Alt+D+B |
Yes |
Data, Subtotals |
Alt+D+L |
Yes |
Data, Validation |
Alt+D+T |
Yes |
Data, Table |
Alt+D+E |
Yes |
Data, Text to Columns |
Alt+D+N |
Yes |
Data, Consolidate |
Alt+D+G+H |
Yes |
Data, Group and Outline, Hide Detail |
Alt+D+G+S |
Yes |
Data, Group and Outline, Show Detail |
Alt+D+G+G |
Yes |
Data, Group and Outline, Group |
Alt+D+G+U |
Yes |
Data, Group and Outline, Ungroup |
Alt+D+G+A |
Yes |
Data, Group and Outline, Auto Outline |
Alt+D+G+C |
Yes |
Data, Group and Outline, Clear Outline |
Alt+D+G+E |
Yes |
Data, Group and Outline, Settings |
Alt+D+P |
Yes |
Data, PivotTable and PivotChart Report |
Alt+D+D+D |
Yes |
Data, Import External Data, Import Data |
Alt+D+D+W |
Yes |
Data, Import External Data, New Web Query |
Alt+D+D+N |
Yes |
Data, Import External Data, New Database Query |
Alt+D+D+E |
Yes |
Data, Import External Data, List |
Alt+D+I+D |
No |
Data, List, Discard Changes and Refresh |
Alt+D+I+B |
No |
Data, List, Hide Border of Inactive Lists |
Alt+D+X+I |
Yes |
Data, XML, Import |
Alt+D+X+E |
Yes |
Data, XML, Export |
Alt+D+X+R |
Yes |
Data, XML, Refresh XML Data |
Alt+D+X+X |
Yes |
Data, XML, XML Source |
Alt+D+X+P |
Yes |
Data, XML, XML Map Properties |
Alt+D+X+Q |
Yes |
Data, XML, Edit Query |
Alt+D+X+A |
Yes |
Data, XML, XML Expansion Packs Edit Query |
Alt+D+D+A |
Yes |
Data, Import External Data, Data Range Properties |
Alt+D+D+M |
Yes |
Data, Import External Data, Parameters |
Alt+D+I+C |
Yes |
Data, List, Create List |
Alt+D+I+R |
Yes |
Data, List, Resize List |
Alt+D+I+T |
Yes |
Data, List, Total Row |
Alt+D+I+V |
Yes |
Data, List, Convert to Range |
Alt+D+I+P |
Yes |
Data, List, Publish List |
Alt+D+I+L |
No |
Data, List, View List on Server |
Alt+D+I+U |
No |
Data, List, Unlink List |
Alt+D+I+Y |
No |
Data, List, Synchronize |
Alt+D+R |
Yes |
Data, Refresh Data |
Alt+W+N |
No |
Window, New Window |
Alt+W+A |
No |
Window, Arrange |
Alt+W+B |
No |
Window, Compare Side by Side with Filename |
Alt+W+H |
No |
Window, Hide |
Alt+W+U |
No |
Window, Unhide |
Alt+W+S |
No |
Window, Split |
Alt+W+F |
No |
Window, Freeze Panes |
Alt+W+1 |
No |
Window, 1 |
Alt+W+2 |
No |
Window, 2 |
Alt+W+3 |
No |
Window, 3 |
Alt+W+4 |
No |
Window, 4 |
Alt+W+5 |
No |
Window, 5 |
Alt+W+6 |
No |
Window, 6 |
Alt+W+7 |
No |
Window, 7 |
Alt+W+8 |
No |
Window, 8 |
Alt+W+9 |
No |
Window, 9 |
Alt+W+M |
No |
Window, More Windows |
Alt+H+H |
No |
Help, Microsoft Excel Help |
Alt+H+O |
No |
Help, Show the Office Assistant |
Alt+H+M |
No |
Help, Microsoft Office Online |
Alt+H+C |
No |
Help, Contact Us |
Alt+H+L |
No |
Help, Lotus 1-2-3 Help |
Alt+H+K |
No |
Help, Check for Updates |
Alt+H+R |
No |
Help, Detect and Repair |
Alt+H+V |
No |
Help, Activate Product |
Alt+H+F |
No |
Help, Customer Feedback Options |
Alt+H+A |
No |
Help, About Microsoft Office Excel |
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 2019. 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 2019.