Advanced Excel Keyboard Shortcuts

Microsoft Excel is a program that is useful across many industries and professions. In the first part of this guide, we took a look at the basic function shortcuts for both PCs and Macs. In this guide, we'll examine the more complicated functions and their shortcuts for users who need Excel for more advanced applications. 

MORE: Basic Excel Keyboard Shortcuts

Formatting data

Excel is useful in the organization of data, including dates, times, percentages and other numbers. In this section, learn shortcuts to better format the cells and their respective data.

WINDOWS

Ctrl + 1

Format cells dialog

Ctrl + b
(or ctrl + 2)

Apply / remove bold

Ctrl + i
(or ctrl + 3)

Apply / remove italic

Ctrl + u
(or ctrl + 4)

Apply / remove underline

Ctrl + 5

Apply / remove strikethrough

Ctrl + Shift + f

Display the Format Cells with Fonts Tab active. Press tab 3x to get to font-size

Alt + ' (apostrophe)

Display the Style dialog box

Number Formats

Ctrl + Shift + $

Apply the Currency format with two decimal places

Ctrl + Shift + ~

Apply the General number format

Ctrl + Shift + %

Apply the Percentage format with no decimal places

Ctrl + Shift + #

Apply the Date format with the day, month, and year

Ctrl + Shift + @

Apply the Time format with the hour and minute, and indicate am or pm

Ctrl + Shift + !

Apply the Number format with two decimal places, thousands separator, and minus sign (-) for negative values

Ctrl + Shift + ^

Apply the Scientific number format with two decimal places

F4

Repeat last formatting action: Apply previously applied Cell Formatting to a different Cell

Apply Borders to Cells

Ctrl + Shift + &

Apply outline border from cell or selection

Ctrl + Shift + _ (underscore)

Remove outline borders from cell or selection

Ctrl + 1, then Ctrl + Arrow Right/Arrow Left

Access border menu in 'Format Cell' dialog. Once border was selected, it will show up directly on the next Ctrl + 1

Alt + t

In Cell Format in 'Border' Dialog Window, set top border

Alt + b

In Cell Format in 'Border' Dialog Window, set bottom border

Alt + l

In Cell Format in 'Border' Dialog Window, set left border

Alt + r

In Cell Format in 'Border' Dialog Window, set right border

Alt + d

In Cell Format in 'Border' Dialog Window, set diagonal and down border

Alt + u

In Cell Format in 'Border' Dialog Window, set diagonal and up border

Align Cells

Alt + h, ar

Align Right

Alt + h, ac

Align Center

Alt + h, al

Align Left

MAC

⌘ (Command) + Shift + l

Display the Style dialog box

⌘ + 1

Display the Format Cells dialog box

Ctrl + Shift + ~

Apply the general number format

Ctrl + Shift + $

Apply the currency format with two decimal places (negative numbers appear in red with parentheses)

Ctrl + Shift + %

Apply the percentage format with no decimal places

Ctrl + Shift + ^

Apply the exponential number format with two decimal places

Ctrl + Shift + #

Apply the date format with the day, month, and year

Ctrl + Shift + @

Apply the time format with the hour and minute, and indicate A.M. or P.M.

Ctrl + Shift + !

Apply the number format with two decimal places, thousands separator, and minus sign (-) for negative values

⌘ + Option + 0 (zero)

Apply the outline border around the selected cells

⌘ + Option + Arrow Right

Add an outline border to the right of the selection

⌘ + Option + Arrow Left

Add an outline border to the left of the selection

⌘ + Option + Arrow Up

Add an outline border to the top of the selection

⌘ + Option + Arrow Down

Add an outline border to the bottom of the selection

⌘ + Option + -

Remove outline borders

⌘ + b

Apply / remove bold

⌘ + i

Apply / remove italic

⌘ + u

Apply / remove underscoring

⌘ + Shift + x

Apply / remove strikethrough

Ctrl + 9

Hide rows

Ctrl + Shift + (

Unhide rows

Ctrl + 0 (zero)

Hide columns

Ctrl + Shift + )

Unhide columns

⌘ + Shift + w

Add or remove the shadow font style

⌘ + Shift + d

Add or remove the outline font style

Ctrl + u

Edit the active cell

Esc

Cancel an entry in the cell or the formula bar

Delete

Edit the active cell and then clear it, or delete the preceding character in the active cell as you edit the cell contents

⌘ + v

Paste text into the active cell

Return

Complete a cell entry

Ctrl + Shift + Return

Enter a formula as an array formula

Ctrl + a

Display the Formula Builder after you type a valid function name in a formula

Creating and working with formulas

Excel spreadsheets are often used to crunch numbers. To do this, one must understand the basics of formulas. Whether calculating sums or averages, these shortcuts will help users be more efficient in formula creation and use.

WINDOWS

=

Start a formula

Alt + =

Insert the AutoSum formula

Shift + F3

Display the Insert Function dialog box

Ctrl + a

Display Formula Window after typing formula name

Ctrl + Shift + a

Insert Arguments in formula after typing formula name

Shift + F3

Insert a function into a formula

Ctrl + Shift + Enter

Enter a formula as an array formula

F4

After typing cell reference (e.g. =E3) makes reference absolute (=$E$4)

F9

Calculate all worksheets in all open workbooks

Shift + F9

Calculate the active worksheet

Ctrl + Alt + F9

Calculate all worksheets in all open workbooks, regardless of whether they have changed since the last calculation

Ctrl + Alt + Shift + F9

Recheck dependent formulas, and then calculates all cells in all open workbooks, including cells not marked as needing to be calculated

Ctrl + Shift + u

Toggle expand or collapse formula bar

Ctrl + `

Toggle Show formula in cell instead of values

Names

Ctrl + F3

Define a name or dialog

Ctrl + Shift + F3

Create names from row and column labels

F3

Paste a defined name into a formula

MAC

Delete

Edit the active cell and then clear it, or delete the preceding character in the active cell as you edit the cell contents

Return

Complete a cell entry

Ctrl + Shift + Return

Enter a formula as an array formula

Esc

Cancel an entry in the cell or formula bar

Ctrl + a

Display the Formula Builder after you type a valid function name in a formula

⌘ + k

Insert a hyperlink

Ctrl + u

Edit the active cell and position the insertion point at the end of the line

Shift + F3

Open the Formula Builder

⌘ + =

Calculate all sheets in all open workbooks

⌘ + Shift + =

Calculate the active sheet

=

Start a formula

⌘ + t

Toggle the formula reference style between absolute, relative, and mixed

⌘ + Shift + t

Insert the AutoSum formula

Ctrl + ; (semicolon)

Enter the date

⌘ + ; (semicolon)

Enter the time

Ctrl + Shift + " (quotation marks)

Copy the value from the cell above the active cell into the cell or the formula bar

Ctrl + ` (grave accent)

Alternate between displaying cell values and displaying cell formulas

Ctrl + ' (apostrophe)

Copy a formula from the cell above the active cell into the cell or the formula bar

Ctrl + Option + Arrow Down

Display the AutoComplete list

Ctrl + l

Define a name

Working with data forms

Excel offers a built-in data entry form as an easy way to enter data into an Excel database. The form allows users to start new databases, add new records to existing databases, scroll through records, search for records and edit or delete individual records. The shortcuts below will enable the user to better work with data forms.

WINDOWS

Tab

Move to the next field that can be edited

Shift + Tab

Move to the previous field that can be edited

Enter

Move to the first field in the next record

Shift + Enter

Move to the first field in the previous record

Page Down

Move to the same field 10 records forward

Page Up

Move to the same field 10 records back

Ctrl + Page Down

Move to a new record

Ctrl + Page Up

Move to the first record

Home

Move to the beginning of a field

End

Move to the end of a field

MAC

Arrow Down

Move to the same field in the next record

Arrow Up

Move to the same field in the previous record

Tab

Move to the next field that you can edit in the record

Shift + Tab

Move to the previous field that you can edit in the record

Return

Move to the first field in the next record

Shift + Return

Move to the first field in the previous record

Page Down

Move to the same field 10 records forward

Page Up

Move to the same field 10 records back

Ctrl + Page Down

Move to a new record

Ctrl + Page Up

Move to the first record

Create a new thread in the Apps General Discussion forum about this subject
This thread is closed for comments
No comments yet
Comment from the forums
    Your comment