Excel performance tuning for 100k-row data: three fixes, measured

Finance and operations colleagues keep asking why a spreadsheet "freezes the moment you touch it". We instrumented the classic scenarios on a mid-range office laptop (i5, 16 GB) with a 100k-row × 60-column dataset. The spread between processing methods exceeded fifty-fold. Here is the symptom → cause → fix breakdown, timings included.

Measure first: where exactly does it stall?

Open-stall, filter-stall, and recalc-stall have different causes:

• 90-second file open: volatile functions (NOW(), OFFSET(), INDIRECT()) forcing full recalculation on load;
• Scrolling jank while filtering: heavy conditional formatting plus whole-column references;
• Spinners on every edit: tens of thousands of cross-sheet VLOOKUPs forming a dependency web.

Fix one: purge volatile functions and whole-column references

Replace INDIRECT-built references with direct or structured table references, and narrow A:A to the actual range. Those two changes alone took open time from 90s to 21s. Principle: references as small as possible, volatile functions as rare as possible.

Fix two: replace VLOOKUP armies with merge queries

Thousands of cross-sheet lookups became a single Power Query merge materialized as values:

• 20,000 VLOOKUP formulas: ~40s per recalculation;
• Power Query merge loaded as a table: 6s per refresh, static thereafter.

For lookups that rarely change, materialization always beats live computation. Keep interactivity in Power Query refreshes, not in every cell.

Fix three: at larger scales, use the data model

For million-row aggregation, stop computing in the grid. Load data into Power Pivot and summarize with PivotTables. The columnar compression is dramatic: a 300 MB CSV became a 40 MB workbook, and pivots render instantly.

Four everyday disciplines

1. Layer raw data and reports: raw sheets store values only;
2. Prefer lightweight conditional formats (data bars, color scales) over per-cell formula rules;
3. Periodically re-save to purge ghost rows and styles — selecting below the last row, deleting, and saving often shrinks files surprisingly;
4. Beyond ~200k rows of ongoing analysis, move to a database or Power BI. Don't fight it.

Nine out of ten Excel performance problems come from giving database work to a spreadsheet. Separate "store, compute, view" roles and assign each its tool — the freezes disappear.