How to Evaluate and Debug a Formula with F9 in Excel

To evaluate part of a formula in Excel, enter Edit mode with F2, select the portion you want to inspect, then press F9 to convert it to its calculated value in place. Press Esc to revert without changing the formula, or Enter to commit the substitution. On a Mac use Fn+F9 if function keys map to media controls.

What F9 actually does inside a formula

Pressing F9 outside Edit mode recalculates all open workbooks. The far more useful behavior, and the one analysts rely on for debugging, is the in-formula version: highlight any self-contained sub-expression while editing a cell and F9 replaces that fragment with the value it currently returns.

This lets you peel a long formula apart one argument at a time. Instead of guessing why =IF(AND(B2>0,C2<D2),E2*F2,0) returns the wrong number, you can select just AND(B2>0,C2<D2) and see whether it evaluates to TRUE or FALSE.

The critical rule: press Esc, not Enter, when you are done inspecting. Enter commits the substituted value and permanently hardcodes that part of the formula, which silently breaks the model.

1. Click the cell containing the formula and press F2 to enter Edit mode.
2. Use the mouse or Shift+Arrow keys to select a complete, balanced sub-expression (a whole argument or function call).
3. Press F9. Excel replaces the selection with its evaluated result.
4. Read the value, then press Esc to discard the change and restore the original formula.
5. Only press Enter if you deliberately want to convert that fragment to a literal value.

Always exit with Esc when debugging so you do not accidentally hardcode a value.

Debugging a nested formula step by step

Nested IF, INDEX/MATCH, and SUMPRODUCT formulas are where F9 earns its keep. The trick is to evaluate from the inside out, confirming each layer before moving up.

Consider =INDEX(Revenue,MATCH(B2,Periods,0)). Select MATCH(B2,Periods,0) and press F9 to see which row position it resolves to. If it returns #N/A, the lookup value is the problem, not the INDEX. If it returns a sensible integer, the issue is the data range. This isolates the failure in seconds.

• Evaluate the innermost function first, then work outward.
• Select a balanced unit: a full function call or a complete argument, never a half-open parenthesis.
• If F9 returns an error like #REF! or #N/A, you have found the failing layer.
• For array fragments, F9 shows the full array in braces, for example {1;2;3}, which reveals spill and dimension issues.

F9 vs the Evaluate Formula dialog

Excel also ships a guided debugger at Formulas > Evaluate Formula. It steps through the formula in calculation order and underlines the next expression to be resolved. Click Evaluate repeatedly to watch each piece collapse to a value.

Evaluate Formula is safer because it never alters the cell, and it shows precedence you might misjudge by hand. F9 is faster and more surgical when you already suspect a specific fragment. Most analysts use both: the dialog for an unfamiliar formula, inline F9 for a quick hypothesis check.

1. Select the cell with the suspect formula.
2. Go to Formulas > Evaluate Formula.
3. Click Evaluate to resolve the underlined expression one step at a time.
4. Use Step In to drill into a referenced cell's own formula, then Step Out to return.
5. Click Close when finished. The original formula is untouched.