Excel Shortcut Keys - Appendixes - Microsoft Excel 2016 BIBLE (2016)

Microsoft Excel 2016 BIBLE (2016)

Part VII
Appendixes

Appendix B
Excel Shortcut Keys

Many users have discovered that using their keyboard is often much more efficient than using their mouse. This appendix lists the most useful shortcut keys available in Excel. The shortcuts are arranged by context.

The keys listed assume that you're not using the Transition Navigation Keys, which are designed to emulate Lotus 1-2-3. You can select the Transition Navigation Keys option of the Advanced tab of the Excel Options dialog box (in the Lotus Compatibility section).

Note

On the surface, the Ribbon interface appears to be designed for a mouse. However, you can access nearly all the Ribbon commands by using the keyboard. Press the Alt key, and Excel displays “keytips” next to each command. Just press the key that corresponds to the command you need. For example, the command to toggle worksheet gridlines is View image Show image Gridlines. The keyboard equivalent is Alt, followed by WVG. Note that you don't need to keep the Alt key depressed while you type the subsequent letters.

Table B.1 Moving through a Worksheet

Key(s)

What It Does

Navigation keys (←, →, ↑, ↓)

Moves left, right, up, or down one cell

Navigation keys* (←, →, ↑, ↓)

Scrolls left, right, up, or down one cell

Home

Moves to the beginning of the row

Home*

Moves to the upper-left cell displayed in the window

End*

Moves to the lower-left cell displayed in the window

PgUp

Moves up one screen

PgDn

Moves down one screen

Ctrl+PgUp

Moves to the previous sheet

Ctrl+PgDn

Moves to the next sheet

Alt+PgUp

Moves one screen to the left

Alt+PgDn

Moves one screen to the right

Ctrl+Home

Moves to the first cell in the worksheet (A1)

Ctrl+End

Moves to the last nonempty cell of the worksheet

Ctrl+navigation key

Moves to the edge of a data block; if the cell is blank, moves to the first nonblank cell

Ctrl+Backspace

Scrolls to display the active cell

End, followed by Home

Moves to the last nonempty cell on the worksheet

F5

Prompts for a cell address to go to

F6

Moves to the next pane of a window that has been split

Shift+F6

Moves to the previous pane of a window that has been split

Ctrl+Tab

Moves to the next window

Ctrl+Shift+Tab

Moves to the previous window

Ctrl+F6

Moves to the next window

Ctrl+Shift+F6

Moves to the previous window

* With Scroll Lock on

Table B.2 Selecting Cells in the Worksheet

Key(s)

What It Does

Shift+navigation key

Expands the selection in the direction indicated.

Shift+spacebar

Selects the entire row(s) in the selected range.

Ctrl+spacebar

Selects the entire column(s) in the selected range.

Ctrl+Shift+spacebar

Selects the entire worksheet.

Ctrl+Shift+spacebar

If the active cell is within a table, selects the table without the header row and totals row. Pressing Ctrl+Shift+spacebar again selects the complete table. Pressing Ctrl+Shift+spacebar again selects the entire worksheet.

Shift+Home

Expands the selection to the beginning of the current row.

Ctrl+*

If the active cell is within a multicell range, selects the block of data surrounding the active cell.

F8

Extends the selection as you use navigation keys. Press F8 again to return to normal selection mode.

Shift+F8

Adds other nonadjacent cells or ranges to the selection; pressing Shift+F8 again ends Add mode.

F5

Prompts for a range or range name to select.

Ctrl+G

Prompts for a range or range name to select.

Ctrl+A

Selects the entire worksheet.

Ctrl+A

If the active cell is within a table, selects the table without the header row and totals row. Pressing Ctrl+Shift+spacebar again selects the complete table. Pressing Ctrl+Shift+spacebar again selects the entire worksheet.

Shift+Backspace

Cancels a range selection and selects only the active cell.

Table B.3 Moving Within a Range Selection

Key(s)

What It Does

Enter

Moves the cell pointer. The direction depends on the setting in the Edit tab of the Options dialog box.

Shift+Enter

Moves the cell pointer up to the preceding cell in the selection.

Tab

Moves the cell pointer right to the next cell in the selection.

Shift+Tab

Moves the cell pointer left to the preceding cell in the selection.

Ctrl+. (period)

Moves the cell pointer to the next corner of the current cell range.

Shift+Backspace

Collapses the cell selection to just the active cell.

Table B.4 Editing Keys in the Formula Bar

Key(s)

What It Does

F2

Begins editing the active cell

Navigation keys

Moves the cursor one character in the direction of the arrow

Home

Moves the cursor to the beginning of the text

End

Moves the cursor to the end of the text

Ctrl+→

Moves the cursor one word to the right

Ctrl+←

Moves the cursor one word to the left

F3

Displays the Paste Name dialog box when you're creating a formula

F4

Switches between absolute and relative cell references

Ctrl+A

Displays the Function Arguments dialog box (after you type a function name in a formula)

Del(ete)

Deletes the character to the right of the cursor

Ctrl+Del(ete)

Deletes all characters from the cursor to the end of the line

Backspace

Deletes the character to the left of the cursor

Esc

Cancels the editing

Table B.5 Formatting Keys

Key(s)

What It Does

Ctrl+1

Displays the Format dialog box for the selected object

Ctrl+B

Sets or removes boldface

Ctrl+I

Sets or removes italic

Ctrl+U

Sets or removes underlining

Ctrl+2

Sets or removes boldface

Ctrl+3

Sets or removes italic

Ctrl+4

Sets or removes underlining

Ctrl+5

Sets or removes strikethrough

Ctrl+Shift+˜

Applies the general number format

Ctrl+Shift+!

Applies the comma format with two decimal places

Ctrl+Shift+#

Applies the date format (day, month, year)

Ctrl+Shift+@

Applies the time format (hour, minute, a.m./p.m.)

Ctrl+Shift+$

Applies the currency format with two decimal places

Ctrl+Shift+%

Applies the percent format with no decimal places

Ctrl+Shift+&

Applies outside borders to a range selection

Ctrl+Shift+_

Removes all borders for the range selection

Table B.6 Other Shortcut Keys

Key(s)

What It Does

Alt+=

Inserts the AutoSum formula

Alt+Backspace

Undo

Alt+Enter

Starts a new line in the current cell

Ctrl+;

Enters the current date

Ctrl+:

Enters the current time

Ctrl+0 (zero)

Hides columns

Ctrl+6

Cycles among various ways of displaying objects on a worksheet

Ctrl+8

Toggles the display of outline symbols

Ctrl+9

Hides rows

Ctrl+[

Selects direct precedent cells

Ctrl+]

Selects directly dependent cells

Ctrl+C

Equivalent to Home imageClipboard imageCopy

Ctrl+D

Equivalent to Home imageEditing imageFill imageDown

Ctrl+F

Equivalent to Home imageEditing imageFind & Select imageFind

Ctrl+H

Equivalent to Home imageEditing imageFind & Select image Replace

Ctrl+K

Equivalent to Insert image Links image Hyperlink

Ctrl+N

Creates a new workbook

Ctrl+O

Equivalent to File image Open

Ctrl+P

Equivalent to File image Print

Ctrl+R

Equivalent to Home image Editing image Fill image Fill Right

Ctrl+T

Equivalent to Insert image Tables image Table

Ctrl+Shift+T

Toggles the Total row in a table

Ctrl+Shift+L

Toggles the AutoFilter controls in a table or range

Ctrl+S

Equivalent to File image Save

Ctrl+Alt+V

Equivalent to Home image Clipboard image Paste image Paste Special

Ctrl+Shift+(

Unhides rows in the selection

Ctrl+Shift+)

Unhides columns in the selection

Ctrl+Shift+A

Inserts the argument names and parentheses for the function (after you type a valid function name in a formula)

Ctrl+V

Equivalent to Home image Clipboard image Paste

Ctrl+X

Equivalent to Home image Clipboard image Cut

Ctrl+Z

Undo

Table B.7 Function Keys

Key(s)

What It Does

F1

Displays Help

Alt+F1

Inserts default chart object that uses the selected range

Ctrl+F1

Toggles the display of the Ribbon

Ctrl+Shift+F1

Toggles auto-hiding the Ribbon

Alt+Shift+F1

Inserts a new worksheet

F2

Edits the active cell

Shift+F2

Edits a cell comment

Alt+F2

Equivalent to File image Save As

Alt+Shift+F2

Equivalent to File image Save

F3

Pastes a name into a formula

Shift+F3

Pastes a function into a formula

Ctrl+F3

Equivalent to Formulas image Defined Names image Name Manager

Ctrl+Shift+F3

Equivalent to Formulas image Defined Names image Create From Selection

F4

Repeats the last action

Shift+F4

Repeats the last Find (Find Next)

Ctrl+F4

Closes the window

Alt+F4

Equivalent to File image Exit

F5

Equivalent to Home image Editing image Find & Select image Go To

Shift+F5

Equivalent to Home image Editing image Find & Select image Find

Ctrl+F5

Restores a minimized or maximized workbook window

Alt+F5

Refreshes active query or pivot table

F6

Moves to the next pane

Shift+F6

Moves to the previous pane

Ctrl+F6

Activates the next window

Ctrl+Shift+F6

Activates the previous workbook window

F7

Equivalent to Review image Proofing image Spelling

Ctrl+F7

Allows moving the window with the arrow keys

F8

Extends a selection (toggle)

Shift+F8

Adds to the selection (toggle)

Ctrl+F8

Allows resizing the window with the arrow keys

Alt+F8

Equivalent to View image Macros image Macros, or Developer image Code image Macros

F9

Calculates all sheets in all open workbooks

Shift+F9

Calculates the active worksheet

Ctrl+Alt+F9

Global calculation

Ctrl+F9

Minimizes the workbook window

Ctrl+Alt+Shift+F9

Rebuilds all formula dependencies and recalculates

F10

Displays keytips for the Ribbon

Alt+F10

Displays or hides the Selection task pane

Shift+F10

Displays a shortcut menu for the selected object (equivalent to right-clicking)

Ctrl+F10

Maximizes or restores the workbook window

F11

Creates a chart in a chart sheet

Shift+F11

Inserts a new worksheet

Ctrl+F11

Inserts an Excel 4.0 macro sheet

Alt+F11

Equivalent to Developer image Code image Visual Basic

F12

Equivalent to File image Save

Shift+F12

Equivalent to File image Save As

Ctrl+F12

Equivalent to File image Open

Ctrl+Shift+F12

Equivalent to File image Print