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

Swipe to scroll horizontally
Ctrl + 1Format 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 + 5Apply / remove strikethrough
Ctrl + Shift + fDisplay the Format Cells with Fonts Tab active. Press tab 3x to get to font-size
Alt + ' (apostrophe)Display the Style dialog box

Number Formats

Swipe to scroll horizontally
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
F4Repeat last formatting action: Apply previously applied Cell Formatting to a different Cell

Apply Borders to Cells

Swipe to scroll horizontally
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 LeftAccess border menu in 'Format Cell' dialog. Once border was selected, it will show up directly on the next Ctrl + 1
Alt + tIn Cell Format in 'Border' Dialog Window, set top border
Alt + bIn Cell Format in 'Border' Dialog Window, set bottom border
Alt + lIn Cell Format in 'Border' Dialog Window, set left border
Alt + rIn Cell Format in 'Border' Dialog Window, set right border
Alt + dIn Cell Format in 'Border' Dialog Window, set diagonal and down border
Alt + uIn Cell Format in 'Border' Dialog Window, set diagonal and up border

Align Cells

Swipe to scroll horizontally
Alt + h, arAlign Right
Alt + h, acAlign Center
Alt + h, alAlign Left

MAC

Swipe to scroll horizontally
⌘ (Command) + Shift + lDisplay the Style dialog box
⌘ + 1Display 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 RightAdd an outline border to the right of the selection
⌘ + Option + Arrow LeftAdd an outline border to the left of the selection
⌘ + Option + Arrow UpAdd an outline border to the top of the selection
⌘ + Option + Arrow DownAdd an outline border to the bottom of the selection
⌘ + Option + -Remove outline borders
⌘ + bApply / remove bold
⌘ + iApply / remove italic
⌘ + uApply / remove underscoring
⌘ + Shift + xApply / remove strikethrough
Ctrl + 9Hide rows
Ctrl + Shift + (Unhide rows
Ctrl + 0 (zero)Hide columns
Ctrl + Shift + )Unhide columns
⌘ + Shift + wAdd or remove the shadow font style
⌘ + Shift + dAdd or remove the outline font style
Ctrl + uEdit the active cell
EscCancel an entry in the cell or the formula bar
DeleteEdit the active cell and then clear it, or delete the preceding character in the active cell as you edit the cell contents
⌘ + vPaste text into the active cell
ReturnComplete a cell entry
Ctrl + Shift + ReturnEnter a formula as an array formula
Ctrl + aDisplay 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

Swipe to scroll horizontally
=Start a formula
Alt + =Insert the AutoSum formula
Shift + F3Display the Insert Function dialog box
Ctrl + aDisplay Formula Window after typing formula name
Ctrl + Shift + aInsert Arguments in formula after typing formula name
Shift + F3Insert a function into a formula
Ctrl + Shift + EnterEnter a formula as an array formula
F4After typing cell reference (e.g. =E3) makes reference absolute (=$E$4)
F9Calculate all worksheets in all open workbooks
Shift + F9Calculate the active worksheet
Ctrl + Alt + F9Calculate all worksheets in all open workbooks, regardless of whether they have changed since the last calculation
Ctrl + Alt + Shift + F9Recheck dependent formulas, and then calculates all cells in all open workbooks, including cells not marked as needing to be calculated
Ctrl + Shift + uToggle expand or collapse formula bar
Ctrl + `Toggle Show formula in cell instead of values

Names

Swipe to scroll horizontally
Ctrl + F3Define a name or dialog
Ctrl + Shift + F3Create names from row and column labels
F3Paste a defined name into a formula

MAC

Swipe to scroll horizontally
DeleteEdit the active cell and then clear it, or delete the preceding character in the active cell as you edit the cell contents
ReturnComplete a cell entry
Ctrl + Shift + ReturnEnter a formula as an array formula
EscCancel an entry in the cell or formula bar
Ctrl + aDisplay the Formula Builder after you type a valid function name in a formula
⌘ + kInsert a hyperlink
Ctrl + uEdit the active cell and position the insertion point at the end of the line
Shift + F3Open the Formula Builder
⌘ + =Calculate all sheets in all open workbooks
⌘ + Shift + =Calculate the active sheet
=Start a formula
⌘ + tToggle the formula reference style between absolute, relative, and mixed
⌘ + Shift + tInsert 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 DownDisplay the AutoComplete list
Ctrl + lDefine 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

Swipe to scroll horizontally
TabMove to the next field that can be edited
Shift + TabMove to the previous field that can be edited
EnterMove to the first field in the next record
Shift + EnterMove to the first field in the previous record
Page DownMove to the same field 10 records forward
Page UpMove to the same field 10 records back
Ctrl + Page DownMove to a new record
Ctrl + Page UpMove to the first record
HomeMove to the beginning of a field
EndMove to the end of a field

MAC

Swipe to scroll horizontally
Arrow DownMove to the same field in the next record
Arrow UpMove to the same field in the previous record
TabMove to the next field that you can edit in the record
Shift + TabMove to the previous field that you can edit in the record
ReturnMove to the first field in the next record
Shift + ReturnMove to the first field in the previous record
Page DownMove to the same field 10 records forward
Page UpMove to the same field 10 records back
Ctrl + Page DownMove to a new record
Ctrl + Page UpMove to the first record
Tom's Guide Staff

Tom's Guide upgrades your life by helping you decide what products to buy, finding the best deals and showing you how to get the most out of them and solving problems as they arise. Tom's Guide is here to help you accomplish your goals, find great products without the hassle, get the best deals, discover things others don’t want you to know and save time when problems arise. Visit the About Tom's Guide page for more information and to find out how we test products.