You'll spend more of your career reading formulas than writing them: your own from last quarter, a colleague's, a predecessor's undocumented model. Most of the job is reading. Four built-in tools turn an opaque formula into a glass box. Learn them now, and the professional modelling standards later in the track will make them reflexive.
How do F2 and F9 help you read a formula?
F2: edit and see precedents. Select a cell and press F2 to enter edit mode. Two things happen. First, the cursor drops into the formula so you can change it. And, the underused part, every cell the formula references lights up in a matching colour, both in the formula and out on the grid. One keypress shows you, colour-coded, which cells feed this one. Press Esc to leave without changing anything. F2 is the fastest "what does this depend on?" in Excel.
F9: evaluate a selected sub-expression. This is the power tool. In a long formula you often want to know what one piece of it currently evaluates to. In edit mode (F2), select just that piece in the formula bar, say the B2>=600000 inside a big IFS, and press F9. Excel replaces the selected text, in place, with its current value: TRUE or FALSE, a number, or an array. Now you can see which branch is firing and why.
The essential safety rule is to press Esc after inspecting, which restores the formula. If you press Enter instead, Excel commits the evaluated value, and you've replaced live formula logic with a hardcoded constant without any warning. F9 is invaluable and unforgiving, so press Esc when you are done, every single time.
Advertisement
How do Trace Precedents and Evaluate Formula work?
Trace Precedents / Dependents. You find these in the Formulas ▸ Formula Auditing group. Trace Precedents draws arrows into the selected cell from every cell that feeds it. Trace Dependents draws arrows out to every cell that uses it, and Remove Arrows clears them. The keyboard route to jump straight to the feeding cells is Ctrl+[, which selects all precedents, and Ctrl+] selects all dependents. Both are fast for hopping through a model's dependency chain. Use precedents to answer "where does this number come from?" Use dependents for the question that prevents disasters: "if I change or delete this cell, what breaks?"
Evaluate Formula. Formulas ▸ Formula Auditing ▸ Evaluate Formula (keyboard Alt, M, V) opens a dialog that steps through the formula one evaluation at a time, in calculation order. You watch a complex expression resolve piece by piece. It's the guided-tour version of F9, and the safe one, since it never touches the underlying formula. When a formula gives an answer you can't explain, Evaluate Formula shows you where your mental model and Excel's arithmetic diverge.
How do you show every formula on a sheet at once?
A fifth habit costs nothing. Show Formulas (Ctrl+` `, the backtick key at the top left of most keyboards) toggles the whole sheet between showing results and showing the formulas themselves. It is the fastest way to scan a range for the one cell where someone typed a number over a formula, or forgot a $`. Toggle it on, sweep your eyes across the block, toggle it off.