Excel Formula Shortcuts That Save Time Every Day

A handful of Excel formula shortcuts do most of the work: Alt+= for AutoSum, F4 for $ signs, Ctrl+D and Ctrl+Enter for filling formulas, F2 for editing and Ctrl+` for checking. Each one replaces several mouse clicks, and together they make a real difference in a timed job test or a long day of reports. This guide shows what each shortcut does, when to use it, and the Mac keys where they differ.

The formula shortcuts worth learning first#

Learn these eight first. They cover writing, copying and checking formulas.

On many laptops, the F keys control brightness and volume by default. If F2 or F4 does nothing, hold fn and try again. The Excel keyboard shortcuts cheat sheet has the full list beyond formulas.

Start formulas faster: Tab and AutoSum#

The fastest way to write a function is to let Excel finish the name. Type =SU and a list of matching functions appears. Press the Down arrow to pick one, then Tab. Excel writes =SUM( with the opening bracket, so you can go straight to the range. This also prevents spelling mistakes that cause #NAME?.

For totals, AutoSum is even faster. Click the empty cell under a column of numbers and press Alt+= (Mac: ⌘⇧T). Excel writes a SUM formula and guesses the range:

=SUM(B2:B6)

Check the highlighted range before you press Enter. AutoSum stops at the first blank cell or text, so it can pick too few rows. Select several empty total cells first and press Alt+= once to fill them all. The guide to the AutoSum shortcut covers how it picks the range, the SUM function guide covers SUM itself, and you can practise the SUM function in a live sheet.

Lock references with F4#

F4 adds $ signs to the reference your cursor is on, which turns it into an absolute reference that won't move when you copy the formula. Each press cycles through four types:

Here is a common use. To show each region's share of total sales, type =B2/SUM(B2:B6) in C2, click inside B2:B6 in the formula, and press F4. The formula becomes:

=B2/SUM($B$2:$B$6)

With sales of 420, 380, 515, 290 and 610 in B2:B6, the total is 2,215, and C2 shows 0.19 (about 19% once formatted as a percentage). When you fill down, B2 changes to B3, B4 and so on, but the total range stays put. On a Mac, use ⌘T or fn+F4. The guide to what F4 does in Excel covers the details, and you can practise absolute references.

Fill formulas down and right#

Once one formula is right, copy it to the rest of the range with the keyboard:

• Ctrl+D (Mac: ⌘D) fills down. Select the formula cell and the cells below, then press it.
• Ctrl+R (Mac: ⌘R) fills right, from the left-most cell across the selection.
• Double-click the fill handle (the small square at the bottom-right of the cell) to fill down to the end of the data in the next column.
• Ctrl+Shift+Down (Mac: ⌘⇧Down) selects from the current cell to the end of the data, which pairs well with Ctrl+D.

Ctrl+Enter (Mac: ⌃Return) is the other way round: select the whole range first, type the formula once, and press Ctrl+Enter. Every selected cell gets the formula, with references adjusted as if you had filled it. It is the fastest way to fill a range that has gaps or isn't next to other data.

Practise fill down and fill right to get these into your hands. The article on fill down and fill right shows more cases.

Edit and check formulas#

These shortcuts help you find mistakes before anyone else does:

• F2 (Mac: ⌃U) opens the active cell for editing, with the cursor at the end. Excel colours each reference and draws a matching box around its cells, so you can see what the formula uses.
• Ctrl+** (Mac: **⌃) shows every formula on the sheet instead of its result. A typed number in a column of formulas stands out at once. Press it again to switch back. The guide to showing formulas explains more.
• F9 on part of a formula (Windows): in the formula bar, select one piece, such as SUM($B$2:$B$6), and press F9 to see its value. Press Esc afterwards, or the value replaces that part of the formula.
• Ctrl+[ (Windows) jumps to the cells the formula uses. On a Mac, use Formulas > Trace Precedents.
• Esc cancels an edit and restores the cell as it was. Ctrl+Z (Mac: ⌘Z) undoes a change you already entered.

For deeper checks, the guide to formula auditing with Trace Precedents and Evaluate Formula shows how to follow a formula step by step.

A 30-second routine for any formula task#

Put the shortcuts together and most formula tasks follow the same pattern:

1. Type =, the first letters of the function, and Tab to accept it.
2. Select the range with the mouse or Shift+Arrow keys.
3. Press F4 on any reference that must stay fixed.
4. Press Enter, then check the result makes sense.
5. Fill down with Ctrl+D or a double-click on the fill handle.
6. Press Ctrl+` to scan the column, then press it again.

This routine is what timed Excel tests reward. If you want to see how fast you are, try the basic Excel test, or start with the Excel level test to find your level.

Practise it#

Shortcuts only stick when you use them on real tasks. Practise absolute references in a live spreadsheet, then practise fill down and fill right: you type each formula and ExcelReady checks it instantly. Start with the share-of-total exercise on this page and press F4 instead of typing the $ signs.