Advanced Excel · Interactive lesson 11 of 12
Performance & maintainability
A workbook that takes ten seconds to recalculate after every keystroke usually has a handful of identifiable causes — not bad luck. Spotting them is a diagnostic skill, not a hardware upgrade.
Screens shown: Excel for Microsoft 365 for Windows. Practice works in any browser.
Written tutorialMaking Excel Workbooks Faster and Easier to MaintainSkill check
Try these first:
- Name a volatile function and explain why it slows a workbook down.
- Spot a whole-column reference and explain what it costs.
- Explain the difference between Automatic and Manual calculation mode.
- List two habits that keep a workbook fast as it grows.
All familiar? Skip ahead to A12 — Macro Recorder & Automation Awareness.
Concept teach
What you're learning
- Volatile functions
TODAY(),NOW(),OFFSET,INDIRECT, andRAND()recalculate on every change anywhere in the workbook, not just when their own inputs change.- Whole-column references
=SUM(A:A)forces Excel to consider a million-plus rows instead of the few hundred that actually have data —=SUM(A2:A500)or a Table reference does the same job for a fraction of the cost.- Excess formatting
- Conditional formatting or styling applied to entire rows or columns "just in case" adds real overhead — apply it to the range that actually has data.
- Calculation mode
- Automatic recalculates on every change; Manual (F9 to trigger it) is worth switching to temporarily on a large model while you're mid-edit.
- Pivot cache & Power Query refresh cost
- Every PivotTable keeps its own cached copy of the source data — several pivots on the same source multiply memory use; a Power Query step that re-reads a huge file on every refresh adds real wait time.
- Documentation habits
- A workbook that explains its own slow parts is one a future editor can safely speed up — undocumented cleverness is what usually survives untouched, whether it should or not.
Where to find it in Excel
Formulas›Calculation Options
| A | B | C | D | E | F | ||
|---|---|---|---|---|---|---|---|
| 1 | Region | Old formula | Faster formula | ||||
| 2 | North | =SUMIF(A:A,…) | =SUMIFS(Sales[…]) | ||||
| 3 | South | =OFFSET(…) | =INDEX(…) | ||||
| 4 | Today | =TODAY() ×5,000 | one cell, named | ||||
| 5 | |||||||
| 6 | |||||||
| 7 | |||||||
| 8 |
Swipe sideways to see the whole window.
- 1Formulas › Calculation Options.
- 2Manual stops recalculating on every edit while you work on a big model.
- 3Calculate Now (F9) recalculates when you're ready. Switch back to Automatic before sharing.
- 4Volatile functions (OFFSET, INDIRECT, TODAY) and whole-column ranges slow recalculation.
- 5Bounded table references calculate faster.
Rendered view of Excel for Microsoft 365 (Windows). Ribbon layout can differ slightly on Mac, Excel for the web or a narrow window.
Guided build
Six formulas from a real (slow) workbook. Click each one you'd flag as a performance risk, then check your answers.
Independent extension
Speed up a real workbook
Open your slowest workbook and list every volatile function, whole-column reference, and unnecessarily large formatted range you can find. Fix three of them, switch to Manual calculation while you work if it's still sluggish, and note what changed — without changing a single output value.
Spot a mistake or have an idea for this lesson? Send feedback