Amey-Thakur Spreadsheet performance
A slow workbook is usually slow for a few identifiable reasons rather than because of size alone. Fixing them is mechanical once you know where to look.
Method
- Avoid whole-column references. Referencing an entire column forces evaluation across a million rows, and bounded ranges or tables fix it immediately.
- Reduce volatile functions. Functions recalculating on every change cascade through dependents and are the most common cause of sluggishness.
- Replace repeated lookups with helper columns. Computing once and referencing beats recomputing the same lookup in twenty formulas.
- Remove formatting applied to entire sheets. Conditional formatting over whole columns is a frequent and invisible cost (see conditional-formatting).
- Convert stable formulas to values. Historical periods that will never change do not need recalculating.
- Move heavy transformation to Power Query. It processes once on refresh rather than continuously (see power-query).
- Split or migrate when it stops helping. A workbook that remains slow after these fixes has outgrown the tool.
Boundaries
Optimisation extends a spreadsheet's viable range and does not remove its limits. Some slowness comes from the file being on a network share rather than from the content. Very large datasets belong in a database (see databases).