Every office has one: the workbook people open and then go and make tea. Ten seconds on every edit, a minute to open, fans roaring. And because slowness arrives gradually, everyone treats it as weather — unfortunate, unfixable, endured.
It’s not weather. Excel slowness has about four real causes, they’re diagnosable in minutes, and — better — the habits that prevent them are things this whole syllabus has already taught you for other reasons. This lesson connects them.
Cause one: the used range has bloated
Excel tracks a rectangle from A1 to the furthest cell ever touched — the used range — and sizes everything (calculation, saving, scrollbars) to it. One accidental format applied to column entirety, one stray keystroke parked in row 1,048,000, and the sheet’s rectangle is a million rows of nothing.
Diagnose with Ctrl+End: where does Excel think the data ends? If it lands far beyond reality, select the excess rows and columns, delete them (delete, not just clear), and save. This one fix has “repaired” more legendary slow files than any other — and it’s why the habit of formatting ranges rather than whole columns pays quietly forever.
Cause two: volatile formulas, everywhere
Most formulas recalculate only when their inputs change. A small
set — NOW(), TODAY(), OFFSET, INDIRECT, RAND() — are
volatile: they recalculate on every edit, and drag every
formula that depends on them along. One TODAY() is fine.
TODAY() copied down
an ageing column of 80,000 rows, each feeding three more
formulas, means every keystroke anywhere re-runs a quarter of a
million calculations.
The fixes are old friends: one TODAY() in a
named cell
(as_of), referenced everywhere; INDIRECT/OFFSET retired in
favour of structured references
and XLOOKUP,
which do their jobs without the volatility.
Cause three: formulas doing a database’s job
Ten thousand whole-column lookups,
SUMIFS over A:A
instead of a Table column, twenty helper columns re-deriving the
same cleaning — formula sprawl where data preparation should
be. The stage-six answer you already own:
the recipe. Move
import-and-clean into Power Query (which handles millions of rows
without breaking sweat), land a tidy Table, and let formulas do
only the thinking layer. Precise ranges (Orders[Amount], not
A:A) finish the job — the
shared-MATCH pattern
alone can cut a heavy sheet’s lookup cost by whatever multiple of
columns it was repeating.
Cause four: the archaeology layer
Slow files are usually also old files: twelve “backup” sheets
(each a full copy),
conditional-formatting rules bred by years of pasting,
names pointing at deleted ranges,
invisible shapes from a decade of pastes-from-web. Run the digs:
Name Manager (delete the #REF!s), Manage Rules (prune), and
.xlsx archaeology’s bluntest instrument — copy the live
sheets to a fresh workbook and compare file sizes. A 40MB file
that becomes 4MB just told you where the weight was.
The order of operations
When a real slow file lands on you: Ctrl+End first (thirty
seconds, biggest single win), then hunt volatiles, then the
whole-column references, then the archaeology. While operating,
Formulas → Calculation Options → Manual with F9-to-recalculate
makes the patient workable on the table — just put it back to
Automatic after; a manual-calc file in innocent hands shows
stale numbers with total confidence, which is a
worse disease than slowness.
The quiet moral: performance isn’t a specialist topic bolted on the end — it’s the same architecture this syllabus has preached throughout (Tables, names, recipes, one home per fact), measured in seconds instead of clarity. Workbooks built the boring way are fast the way tidy kitchens are clean: not through heroics, but because nothing’s in the wrong place. Cup of tea optional again.