ShortcutdrillExcel shortcuts / Excel formula shortcuts
Excel formula shortcuts
Formula shortcuts are where modeling speed comes from. F4 alone turns a thirty-second edit into a key press, and the tracing keys replace the Formula Auditing ribbon entirely.
Most of these use the function keys. On a Mac laptop you may need Fn with them, or change the keyboard setting so F-keys work without it.
| Action | Windows | Mac | Practice |
|---|---|---|---|
| Lock a reference with F4 | F2, F4 | Fn+F4 (or Cmd+T) | Free drill |
| Make a reference absolute | F2, F4, Enter | Fn+F4, Enter | Free drill |
| Evaluate part of a formula | F2, select the part, F9, Esc | Fn+F9, Esc | Pro drill |
| Enter one formula into many cells | Select, type, Ctrl+Enter | Select, type, Cmd+Return | Pro drill |
| Total a whole block at once | Select the block plus a blank row and column, Alt+= | Cmd+Shift+T | Pro drill |
| Show formulas instead of values | Ctrl+` | Ctrl+` | Free drill |
| Create names from labels | Select labels and values, Ctrl+Shift+F3, Enter | Cmd+Shift+F3 | Pro drill |
| Insert a function by name | Shift+F3, type the name, Enter | Fn+Shift+F3 | Pro drill |
| Add a note to a cell | Shift+F2, type, Esc | Fn+Shift+F2 | Pro drill |
| Repeat the last action | F4 (or Ctrl+Y) | Cmd+Y | Free drill |
Lock a reference with F4 F2, F4
While editing a formula, F4 cycles the reference under the cursor through $B$13, B$13, $B13 and back to B13. The mixed forms are what let one formula fill a whole grid: lock the column to fill right, the row to fill down. Practice: Lock the column only.
Make a reference absolute F2, F4, Enter
One press of F4 on a reference turns B12 into $B$12, fixed in both directions. That is what a tax rate, a growth rate or a price needs before the formula is filled down or across. Practice: Lock everything.
Evaluate part of a formula F2, select the part, F9, Esc
In edit mode, select any part of a formula and press F9 to replace it with its value. It shows what a nested piece returns without breaking the formula apart. Esc puts the formula back; Enter would keep the number. Practice: Evaluate part of a formula.
Enter one formula into many cells Select, type, Ctrl+Enter
Select the range, type the formula once and press Ctrl+Enter. Every selected cell gets it, with references adjusted as if filled. It skips the separate fill step. Practice: Fill a block with one formula.
Total a whole block at once Select the block plus a blank row and column, Alt+=
Select the numbers together with the empty row below and the empty column to the right, then Alt+=. Excel fills the edges with SUMs, including the grand total in the corner. Practice: Sum across a block.
Show formulas instead of values Ctrl+`
Ctrl+` (the key above Tab) switches the whole sheet between showing values and showing formulas. One press reveals the hard-coded number hiding in a column of formulas; a second press switches back. Practice: Show the formulas.
Create names from labels Select labels and values, Ctrl+Shift+F3, Enter
Select a block of labels with their values, press Ctrl+Shift+F3 and Enter, and each value is named after its label. Formulas can then read =Price*Volume, and the Go To box jumps to any input by name. Practice: Name the inputs.
Insert a function by name Shift+F3, type the name, Enter
Shift+F3 opens Insert Function with the search box ready. Type the name, press Enter, and the formula starts with the function and its argument prompts. Useful for a function whose arguments you half remember. Practice: Insert a function by name.
Add a note to a cell Shift+F2, type, Esc
Shift+F2 opens a note on the active cell. Type, then Esc twice to close it with the text kept. A reviewer can leave twenty notes in a model without touching the mouse. Practice: Add a comment.
Repeat the last action F4 (or Ctrl+Y)
Outside a formula, F4 repeats whatever you just did on the new selection: a format, a border, an insert, a fill color. Format one cell the long way, then F4 across the sheet. Practice: Repeat the last action.