Why does changing one cell instantly update ten others? Why does pressing Enter sometimes recalculate everything—and sometimes nothing? Why does Excel freeze when you open a file that looks empty but has 200 hidden formulas?
The answer lies in how Microsoft Excel works—not as a static spreadsheet, but as a dynamic dependency graph with lazy evaluation, volatile function triggers, and an invisible calculation chain that runs before your eyes even register the change.
Quick Answer
Excel works by building and maintaining a real-time dependency tree: every formula points to its inputs (cells, ranges, or external references), and when any input changes, Excel walks that tree to determine which formulas need recalculating—and in what order. It doesn’t calculate top-to-bottom; it calculates *topologically*, based on dependencies. That’s why =B1+A1 in C1 recalculates before =C1*2 in D1—but =NOW() in E1 forces a full recalculation every second, regardless of dependencies.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| Automatic Calculation | Default mode. Excel tracks dependencies and recalculates affected cells after edits. | Most day-to-day work—dashboards, reports, models where accuracy matters. | Slows down with >50k formulas; volatile functions (TODAY, OFFSET) trigger unnecessary full recalcs. |
| Manual Calculation | Alt + M + X → toggle to Manual. Press F9 to recalc all, Shift+F9 for active sheet only. | Large models (100k+ formulas), debugging circular references, batch data entry. | Easy to forget to recalc—results become stale without warning. |
| Iterative Calculation | File → Options → Formulas → Enable iterative calc. Set max iterations & precision. | Financial models with circular logic (e.g., interest-on-interest, margin-based pricing). | Can mask real errors; results depend on iteration count, not mathematical convergence. |
| External Data Queries | Data → Get Data → From File/Database → Load to worksheet or Data Model. | Live dashboards pulling from SQL, CSV, or SharePoint lists. | Refresh timing is separate from formula recalc—requires manual or scheduled refresh (Alt + F5). |
| Power Query Transformation Chain | Each step (filter, merge, pivot) becomes a cached, immutable node. Only reruns steps that changed. | ETL-heavy workflows—cleaning messy sales logs, standardizing vendor names, merging invoices. | No cell-level dependencies—refreshes entire query, not individual outputs. |
Method 1 Deep Dive
Let’s watch automatic calculation in action—with real numbers and real consequences.
Enter this data into Sheet1:
| A | B | C | D |
|---|---|---|---|
| Sales Rep | Q1 Revenue | Q2 Revenue | Growth % |
| Sarah Chen | $142,800 | $156,300 | =IF(B2=0,0,(C2-B2)/B2) |
| David Lin | $98,500 | $104,200 | =IF(B3=0,0,(C3-B3)/B3) |
| Maya Rodriguez | $210,600 | $225,100 | =IF(B4=0,0,(C4-B4)/B4) |
| Total | =SUM(B2:B4) | =SUM(C2:C4) | =IF(B5=0,0,(C5-B5)/B5) |
Now change B2 from $142,800 to $139,200. Watch closely: D2 updates first. Then B5 recalculates (since it depends on B2–B4). Then C5 updates. Then D5 follows—because it depends on B5 and C5. Excel didn’t scan row-by-row. It walked the dependency tree: B2 → D2, B2 → B5 → C5 → D5. The beauty of this approach is that Excel skips D3 and D4 entirely—they’re unaffected.
Try typing =RAND() in E2. Instantly, *every* cell with a formula recalculates—even ones with no RAND reference. That’s because RAND is volatile. So is CELL(), INFO(), and INDIRECT(). What makes this elegant is that Excel knows volatility at parse time—not runtime—so it tags the whole workbook for full recalc before evaluating anything else.
Method 2 Deep Dive
Manual calculation isn’t just “off” mode—it’s a surgical tool. Here’s how to use it like a pro.
Open a new workbook. Paste this into A1:C6:
| A | B | C |
|---|---|---|
| Product | Cost | Markup % |
| Acme Corp Router | $89.99 | 12% |
| Nexus Switch Pro | $245.50 | 18% |
| CloudLink Adapter | $32.75 | 22% |
| List Price | =B2*(1+C2) | =B3*(1+C3) |
| Avg Markup | =AVERAGE(C2:C4) | =SUM(B2:B4) |
Now go to Formulas → Calculation Options → Manual. Notice the status bar says “Calculation Mode: Manual”. Change C2 from 12% to 15%. Nothing updates—not even the List Price in D2. That’s intentional. Now press F9. Everything updates at once—in dependency order. No partial states. No flickering.
Here’s the counterintuitive tip: Turn on manual calculation before opening large files. Go to File → Options → Advanced → uncheck “Update links on save” and set calculation to Manual *before* opening. Excel won’t try to resolve external links or recalc 50k formulas while loading. You’ll open in under 3 seconds instead of 47.
Cheat Sheet
| Action | Shortcut | Notes |
|---|---|---|
| Toggle calc mode | Alt + M + X | Toggles between Automatic and Manual |
| Recalculate all sheets | F9 | Resolves all dependencies, including external links |
| Recalculate active sheet only | Shift + F9 | Faster than F9—ignores other sheets |
| Recalculate selected formulas only | F2 → Enter | Edit then confirm any cell—recalculates just that cell and its direct dependents |
| Force full recalc (ignore dependencies) | Ctrl + Alt + F9 | Use when formulas behave oddly—bypasses dependency cache |
| Recalculate now + rebuild dependency tree | Ctrl + Alt + Shift + F9 | Nuclear option—use after editing named ranges or moving sheets |
