Excel Formula Shortcuts: F2, F4, F9, Ctrl+` and Alt+= Explained

Part of the free Module 1: Excel Shortcut Keys · Lesson 4 of 8 · Full Excel course

Updated on 5 September 2026 · Works in Excel 365, 2021, 2019 and 2016 unless noted. Dynamic array notes apply to Excel 365 and 2021.

Excel formula shortcuts are the keys you use while writing, checking and recalculating formulas: F2 to edit a cell, F4 to lock a reference with dollar signs, Alt + = to insert AutoSum, F9 to recalculate or evaluate part of a formula, and Ctrl + ` to show every formula on the sheet. Together they remove most of the clicking from formula work and make errors far easier to trace.

Entering and editing formulas

Press F2 on a cell to edit it in place. The status bar at the bottom left shows the mode: Enter when you are typing fresh, Edit after F2, and Point when you are picking cells with the Arrow keys inside a formula. In Enter mode the Arrow keys insert cell references; in Edit mode they move the cursor through the text. Press F2 again to switch between the two while editing.

Esc abandons the edit. Ctrl + Enter confirms the formula and keeps the same cell active, which saves a trip back up when you want to check the result. Excel suggests function names as you type; press Tab to accept the highlighted suggestion, which inserts the opening bracket. After typing the function name, press Ctrl + Shift + A to insert placeholder argument names such as =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup), or press Ctrl + A to open the Function Arguments dialog with one box per argument. Shift + F3 opens the Insert Function dialog when you cannot remember the name at all. Inside a formula, Alt + Down opens a drop-down list for structured references and data validation lists.

F4 for absolute and relative references

While the cursor is on a reference inside a formula, F4 cycles through the four reference types.

Press F4 Reference Fixed part
Start A1 Nothing: both row and column shift when copied
Once $A$1 Row and column: the reference never moves
Twice A$1 Row only: copy across a row of totals
Three times $A1 Column only: copy down a column of rates
Four times A1 Back to relative

Suppose a VAT rate sits in F1 and you write =C2*F1. Copy it down and row 3 becomes =C3*F2, which is wrong. Put the cursor on F1, press F4 once, and the formula becomes =C2*$F$1, which copies correctly to every row. Outside edit mode F4 has a second job: it repeats the last action, whether that was applying a fill colour, inserting a row or deleting a sheet.

AutoSum and quick totals with Alt + =

Alt + = inserts a SUM formula for the numbers above or to the left of the active cell. Excel guesses the range and shows it with a moving border; press Enter to accept or adjust the range with Shift + Arrow first. The shortcut becomes powerful when you select a block first. Select the data plus one blank row below and one blank column to the right, press Alt + =, and Excel fills every row total and every column total in one step. For a quick check without a formula at all, select any numbers and read Sum, Average and Count on the status bar.

Calculation, evaluation and auditing keys

F9 recalculates every open workbook. Shift + F9 recalculates only the active sheet. Ctrl + Alt + F9 forces a full recalculation of everything, even cells Excel thinks are up to date, which is the fix when a volatile function or a linked value looks stale. These matter only when calculation is set to Manual (Formulas > Calculation Options); in Automatic mode Excel recalculates as you type.

F9 has a second use inside the formula bar. Select part of a formula, for example the B2*C2 inside =B2*C2*(1+$F$1), and press F9. Excel replaces the selected text with its current value so you can see what that piece returns. Press Esc afterwards to restore the formula; if you press Enter the value becomes hard-coded. The ribbon’s Evaluate Formula tool on the Formulas tab steps through a whole formula in the same way.

Ctrl + ` (the grave accent, usually top-left under Esc) toggles between showing results and showing formulas in every cell. Use it to audit a sheet at a glance, and press it again to return. Ctrl + [ selects every cell the active formula refers to (its precedents), and Ctrl + ] selects every formula that refers to the active cell (its dependents). F5, Enter jumps back to where you started. The ribbon sequence Alt, M, P draws precedent arrows and Alt, M, A, A removes all arrows.

Names, arrays and the formula bar

Ctrl + F3 opens Name Manager to create, edit or delete named ranges. Ctrl + Shift + F3 creates names from the selected labels, for example turning a header row into one name per column in a single step. F3 pastes an existing name into the formula you are typing. Ctrl + Shift + U expands and collapses the formula bar so long formulas are readable.

Ctrl + Shift + Enter confirms a legacy array formula, which Excel wraps in curly braces. In Excel 365 and 2021 you do not need it: any formula that returns several values spills into neighbouring cells automatically, and the #SPILL! error tells you when something is in the way. Only use Ctrl + Shift + Enter in Excel 2019 and earlier, or when editing an old CSE formula.

Worked example: a price list with VAT, built and checked by keyboard

Start with this data in A1:C5, and the VAT rate 0.2 typed in F1.

Row A: Product B: Qty C: Unit price D: Total inc. VAT
1 Product Qty Unit price Total inc. VAT
2 Desk 2 150
3 Chair 4 80
4 Lamp 3 25
5 Shelf 1 110
  1. In D2 type =B2*C2*(1+F1, and with the cursor still on F1 press F4 once so it reads $F$1. Close the bracket and press Ctrl + Enter. D2 shows 360.
  2. Press Ctrl + Shift + Down? Not here, because D3:D5 are blank. Instead press Shift + Down three times to select D2:D5, then Ctrl + D to fill. D3 shows 384, D4 shows 90, D5 shows 132.
  3. Move to D6 and press Alt + =, then Enter. The total is 966.
  4. Press Ctrl + ` to confirm every row still points at $F$1, then Ctrl + ` again to return to values.
  5. Select D2, press F2, highlight B2*C2 with Shift + Arrow, and press F9. Excel shows 300, the pre-VAT amount. Press Esc to keep the formula.

Change F1 to 0.05 and every total updates instantly, because the reference was locked with F4. Without the dollar signs, rows 3 to 5 would have pointed at F2, F3 and F4, which are empty, and returned the price with no VAT at all.

Formula shortcut reference

Action Windows Mac
Edit the active cell F2 Ctrl + U or F2
Toggle absolute / relative reference F4 ⌘ + T or F4
Repeat the last action F4 (outside edit mode) ⌘ + Y
AutoSum Alt + = ⌘ + Shift + T
Confirm and stay in the cell Ctrl + Enter ⌘ + Return
Insert Function dialog Shift + F3 Shift + F3
Function Arguments dialog (after the name) Ctrl + A Ctrl + A
Insert argument names (after the name) Ctrl + Shift + A Ctrl + Shift + A
Accept the AutoComplete suggestion Tab Tab
Recalculate all workbooks F9 ⌘ + = or F9
Recalculate the active sheet Shift + F9 ⌘ + Shift + =
Force full recalculation Ctrl + Alt + F9 Ctrl + Option + F9
Evaluate the selected part of a formula F9 (in the formula bar) F9
Show formulas / show results Ctrl + ` Ctrl + `
Select precedents / dependents Ctrl + [ / Ctrl + ] Ctrl + [ / Ctrl + ]
Trace precedent arrows Alt, M, P no default
Name Manager Ctrl + F3 Ctrl + F3
Create names from selection Ctrl + Shift + F3 Ctrl + Shift + F3
Paste a name into a formula F3 F3
Legacy array formula (2019 and earlier) Ctrl + Shift + Enter ⌘ + Shift + Return
Expand / collapse the formula bar Ctrl + Shift + U Ctrl + Shift + U
Insert a new line inside a formula Alt + Enter Option + Return

Tips and common mistakes

  • F4 only cycles references while you are editing. Press F2 first, or click into the formula bar, then put the cursor on the reference.
  • Lock only what needs locking. A rate in one cell needs $F$1; a header row you copy across needs A$1; a column of labels you copy down needs $A1.
  • Alt + = guesses the range. Check the moving border before pressing Enter, especially when a blank cell splits the column.
  • Do not press Enter after F9 in the formula bar. That hard-codes the value. Press Esc to keep the formula.
  • Ctrl + ` widens columns. Formula view shows the full text of each formula; press Ctrl + ` again to restore the layout.
  • Use Alt + Enter to break long formulas into lines. Excel ignores the line breaks when calculating, and nested IFs become readable.
  • Manual calculation is set per session, not per file. If one open workbook is Manual, every workbook you open next is too, so a formula that ignores your edits usually needs F9 or a switch back to Automatic.

Errors and how to fix them

Problem Cause Fix
F4 inserts nothing or repeats an action The cell is not in edit mode Press F2, move the cursor onto the reference, then F4
Formulas stop updating Calculation is set to Manual Press F9, or choose Formulas > Calculation Options > Automatic
Every cell shows the formula text Show Formulas is on, or the cell is formatted as Text Press Ctrl + `; if only one cell is affected, set the format to General and re-enter the formula
#SPILL! A dynamic array needs cells that are not empty (Excel 365 and 2021) Clear the cells below or to the right, or wrap the formula in INDEX to return one value
Circular reference warning on the status bar A formula refers to its own cell, often after a careless Alt + = Use Formulas > Error Checking > Circular References to find the cell and correct the range
Copied formula returns 0 or #REF! A relative reference moved off the intended cell Edit the original, press F4 on the fixed reference and refill with Ctrl + D

Practise these five this week

Shortcut What it does When to use it
F2 Edits the cell in place Every formula correction
F4 Adds or removes dollar signs on a reference Any formula you plan to copy
Alt + = Inserts AutoSum Totals under a column or across a block
Ctrl + ` Shows all formulas Checking a sheet before you send it
F9 in the formula bar Evaluates the selected part Finding which piece of a long formula is wrong
  1. Build a multiplication table: type 1 to 10 in B1:K1 and A2:A11, write =$A2*B$1 in B2 using F4 to set the mixed references, then fill the block with Ctrl + R and Ctrl + D.
  2. Select a block of numbers plus one empty row and column and press Alt + = to total both directions at once.
  3. Take any nested formula you already have, press F2, select an inner function and press F9 to see its value, then Esc.

Key takeaways

  • F2 opens a cell for editing and switches between Edit and Enter mode; the status bar tells you which you are in.
  • F4 cycles A1, $A$1, A$1, $A1 while editing, and repeats the last action otherwise.
  • Alt + = inserts SUM, and totals a whole block when you select the block plus a blank row and column.
  • F9 recalculates, and inside the formula bar it evaluates the selected part; always follow with Esc.
  • Ctrl + ` shows formulas, Ctrl + [ and Ctrl + ] trace precedents and dependents.
  • Excel 365 and 2021 spill arrays automatically; Ctrl + Shift + Enter is for older versions only.

Related lessons

Frequently asked questions

What does F4 do in Excel?

While you are editing a formula, F4 cycles the reference under the cursor through absolute, mixed and relative forms: A1, $A$1, A$1, $A1. When you are not editing, F4 repeats your last action, such as applying a format or inserting a row. On a Mac the reference toggle is ⌘ + T and repeat is ⌘ + Y.

What is Alt + = in Excel?

Alt + = is the AutoSum shortcut. It inserts a SUM formula that covers the numbers directly above or to the left of the active cell and shows the range before you confirm with Enter. If you select a block of numbers together with one empty row below and one empty column to the right, it writes all the row and column totals at once.

Why is my Excel formula not calculating until I press F9?

Calculation is set to Manual, either in this workbook or in another workbook that was opened first in the same session. F9 recalculates on demand. To restore normal behaviour choose Formulas > Calculation Options > Automatic, then save the workbook so the setting travels with it.

How do I show all formulas in Excel?

Press Ctrl + `, the grave accent key below Esc on most keyboards. Every cell displays its formula text instead of its result and columns widen to fit. Press the same shortcut again to return to normal view. The ribbon equivalent is Formulas > Show Formulas.

Do I still need Ctrl + Shift + Enter in Excel 365?

No. Excel 365 and Excel 2021 use dynamic arrays, so a formula that returns several values spills into the cells next to it as soon as you press Enter. Ctrl + Shift + Enter is only required in Excel 2019 and earlier, and Excel still accepts it there for compatibility, showing the formula in curly braces.

Want the finished version? Ready-made Excel dashboards, trackers and VBA systems are available at NextGenTemplates.com.

← PreviousExcel Formatting Shortcuts: Ctrl+1 and the Number Format KeysAll lessonsExcel Shortcut KeysNext →Excel Data Shortcuts: Sort, Filter, Tables, Pivot Tables and Charts