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 + 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
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 |
| F4 | Repeat 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 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
Swipe to scroll horizontally
| Alt + h, ar | Align Right |
| Alt + h, ac | Align Center |
| Alt + h, al | Align Left |
MAC
Swipe to scroll horizontally
| ⌘ (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
Swipe to scroll horizontally
| = | 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
Swipe to scroll horizontally
| 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
Swipe to scroll horizontally
| 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
Swipe to scroll horizontally
| 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
Swipe to scroll horizontally
| 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 |
