Concept
1. Volatile functions
A volatile function recalculates every time anything changes in the workbook — and so does every formula that depends on it.
| Volatile | Non-volatile replacement |
|---|---|
OFFSET (dynamic ranges) |
Tables, INDEX(A:A,1):INDEX(A:A,n), spills with #, TAKE/DROP |
INDIRECT (text references) |
INDEX/CHOOSE/XLOOKUP on real ranges |
TODAY(), NOW() |
one cell inp_Today (or Power Query's refresh date) referenced everywhere |
RAND, RANDARRAY, RANDBETWEEN |
paste values after generating |
CELL, INFO |
avoid in dashboards |
One TODAY() is fine; 5,000 formulas that each call TODAY() or OFFSET are not.
2. Other speed killers
- Full-column references in array formulas (
A:Ainside SUMPRODUCT/FILTER) — use Table columns. - Repeated calculations — the same SUMIFS written in 50 places; calculate once in CALC and reference it (LET for repeated pieces).
- Lookups on unsorted huge ranges many times — use XLOOKUP once into a helper column, or a Data Model relationship.
- Thousands of conditional formatting rules fragmented by copy-paste (Module 3 Lesson 3).
- Many linked pictures and complex shapes.
3. Calculation mode
Formulas → Calculation Options:
- Automatic — normal.
- Automatic except for data tables — if you use What-If data tables.
- Manual — while building heavy models; press F9 (calculate all) or Shift + F9 (active sheet). Always switch back to Automatic before saving a dashboard, or viewers see stale numbers — the mode is saved with the file.
Microsoft 365: Review → Check Performance finds and removes formatting on empty cells that bloats the file.
4. File size trim
| Action | Where |
|---|---|
| Reset the used range: delete empty rows/columns beyond data (check with Ctrl + End), then save | each sheet |
| Pivot cache: untick Save source data with file (refresh on open instead) — or keep data in the Data Model | PivotTable Options → Data |
| Load big tables to the Data Model / connection only instead of sheets | Power Query → Load To |
| Compress pictures (Picture Format → Compress Pictures, 150 ppi, delete cropped areas) | images |
| Delete unused names, styles and hidden old sheets | Name Manager; Cell Styles |
| Save as .xlsb (binary) — often 30–50% smaller and faster to open; keeps macros | File → Save As |
Measure before and after: file size, and recalculation time with a stopwatch (or Application.CalculateFull timing in VBA).
5. A quick performance pass
- Search formulas for OFFSET, INDIRECT, TODAY, NOW (Ctrl + F, Look in: Formulas).
- Replace with non-volatile versions.
- Ctrl + End on every sheet; trim the used range.
- Clean conditional formatting rules.
- Pivot caches and Power Query loads as above.
- Set calculation to Automatic, save, compare size.
Common mistakes
OFFSET-based dynamic named ranges everywhere. Leaving the file in Manual calculation. 50 MB files because 50,000 raw rows are stored twice (sheet + pivot cache).