Excel shortcuts that save the most time
A handful of keys do most of the work in a spreadsheet: pointing at cells, selecting a range, filling down, AutoSum, and jumping to the edge of the data. The sheet above is the lesson, played with the keys only; this page lists every key it uses, for Windows and for Mac.
Why bother with the keyboard
Each of these replaces a reach for the mouse, a drag or a scroll. One saves a second. Across a day of spreadsheets they add up, and the selection keys are also more precise than dragging: Shift + Ctrl + ↓ selects to the last row of the data, however long the column, without overshooting.
The lesson counts your key presses against a par for each step. Modifier keys such as Shift and Ctrl don't count.
The sheet
A small monthly summary, set in the Bike shop: sales and costs per month, with profit, totals and an average to fill in.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month | Sales | Costs | Profit | Store |
| 2 | Apr | 24,000 | 14,500 | ||
| 3 | May | 31,000 | 22,000 | ||
| 4 | Jun | 24,000 | 18,000 | ||
| 5 | Jul | 31,500 | 19,000 | ||
| 6 | Aug | 29,500 | 21,000 | ||
| 7 | Sep | 23,000 | 16,000 | ||
| 8 | Total | ||||
| 9 | Average |
The keys, one by one
- Arrows inside a formula
- After typing = or an operator, an arrow key picks a cell instead of moving the cursor, and its address appears in the formula. In D2: =, ← twice, -, ←, Enter gives
=B2-C2, which is 9,500. - Shift + arrow
- Grows the selection one cell at a time. Shift + ↓ from D2 to the last month, then Ctrl + D, copies the formula into every selected row.
- Ctrl + D
- Fill down: copies the top cell of the selection into the cells below it.
- Alt + = (Windows)
- AutoSum. Writes a SUM of the numbers above or to the left, for you to check and confirm with Enter:
=SUM(B2:B7), which is 163,000. In Excel for Mac the key is Cmd + Shift + T. Browsers keep that one for reopening tabs, so on a Mac this lesson uses Option + = instead. - Ctrl + Shift + arrow
- Selects from here to the edge of the data. Inside a formula it selects a whole column of numbers in one press.
- Tab
- While you type a function name, Excel lists the functions that start with those letters and highlights one; Tab puts the highlighted name in, with its opening bracket. Type enough letters that the one you want is highlighted: after only =AV, Excel can offer AVEDEV, so the lesson types =AVER before Tab.
- Ctrl + Enter
- Puts what you typed into every selected cell at once, not only the first. Good for labels, and for formulas, which adjust per cell as if filled.
- Ctrl + arrow
- Jumps to the edge of the data in that direction. Ctrl + ↑ then Ctrl + ← goes back to A1 from anywhere in a table. On Windows, Ctrl + Home does it in one.
On a Mac, Cmd replaces Ctrl for the arrows, Return replaces Enter, and F4 (which locks a reference with $) usually needs fn. The full list for both systems is below.
Watch where the jump lands
The edge jump stops at the first empty cell, or the last full one. In step 5 the selection runs up through the heading in B1. AVERAGE skips text, so the answer is still right: =AVERAGE(B2:B7) gives 27,167, shown rounded because the column is formatted for whole numbers. A number up there, or a gap in the data, would have changed what got selected. Glance at the range before pressing Enter.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month | Sales | Costs | Profit | Store |
| 2 | Apr | 24,000 | 14,500 | 9,500 | Main |
| 3 | May | 31,000 | 22,000 | 9,000 | Main |
| 4 | Jun | 24,000 | 18,000 | 6,000 | Main |
| 5 | Jul | 31,500 | 19,000 | 12,500 | Main |
| 6 | Aug | 29,500 | 21,000 | 8,500 | Main |
| 7 | Sep | 23,000 | 16,000 | 7,000 | Main |
| 8 | Total | 163,000 | 110,500 | ||
| 9 | Average | 27,167 |
Every step, Windows and Mac
The keys exactly as the lesson shows them. ×2 means press it twice.
-
1 Point with the arrows
Windows = ← ×2 - ← Enter
Mac = ← ×2 - ← Return
In D2:
=B2-C2gives 9,500. -
2 Copy down, no drag
Windows Shift + ↓ ×5 Ctrl + D
Mac Shift + ↓ ×5 Ctrl + D
Fill D2 down over D2:D7. The last one, D7, becomes
=B7-C7.They give 9,000 6,000 12,500 8,500 7,000
-
3 Let it write the SUM
Windows Alt + = Enter
Mac Option + = Return
In B8:
=SUM(B2:B7)gives 163,000. -
4 Select a range by keyboard
Windows =SUM( ↑ Shift + ↑ ×5 ) Enter
Mac =SUM( ↑ Shift + ↑ ×5 ) Return
In C8:
=SUM(C2:C7)gives 110,500. -
5 Jump to the edge
Windows =AVER Tab ↑ ×2 Ctrl + Shift + ↑ ) Enter
Mac =AVER Tab ↑ ×2 Cmd + Shift + ↑ ) Return
In B9:
=AVERAGE(B2:B7)gives 27,167. -
6 Type once, fill them all
Windows Shift + ↓ ×5 Main Ctrl + Enter
Mac Shift + ↓ ×5 Main Ctrl + Return
E2:E7 all get
Main. -
7 Get back to the top
Windows Ctrl + ↑ Ctrl + ←
Mac Cmd + ↑ Cmd + ←
Ends on A1.
In real Excel
Each of these keys is in Microsoft's list of Excel shortcuts. On Windows: Alt + = for AutoSum, Ctrl + D, Ctrl + Enter, Ctrl + arrow, Ctrl + Shift + arrow and Ctrl + Home. In Excel for Mac: Shift + Cmd + T for AutoSum, Ctrl + D or Cmd + D, Ctrl + Return or Cmd + Return, Cmd + arrow and Shift + Cmd + arrow. Excel for the web shares many of them, but the browser keeps some keys for itself. Practise the first five until you stop thinking about them, and the rest follow.
Keep going
- 5Next up is Decisions: IF functionLet the sheet decide: IF for one condition, AND when both must hold.
- 1Totals: SUM formulaAdd up a column, then find the average, the highest, the lowest and the count.
- 2Copying: absolute referenceCopy a formula down a column, then lock one cell with $ so it stays put.