FormulaCamp
Completed
16. Professional Excel Engineering · Performance and Integration · 20 min

5. Optimizing Recalculation

Lesson 5 of 7
Task 1 of 4

  1. In D2:D9, replace TODAY() with a reference to the report date in H1.

Lesson theory

A big workbook is slow not because of how much data it holds but because of unnecessary calculations. The main causes and cures:

  • Volatile functions (TODAY, NOW, RAND, OFFSET, INDIRECT) recalculate on every edit anywhere in the workbook, along with everything that depends on them. TODAY() in a thousand rows means a thousand recalculations on every entry. It's better to have one "Report date" cell that all rows refer to. It also makes the report reproducible: open it a month from now and you'll see the same numbers;
  • repeated calculations: if every row recomputes SUM($B$2:$B$9), move the total into one cell;
  • references to entire columns (A:A) in older functions and array formulas make Excel scan a million rows, so limit the range or use tables;
  • for heavy models, switch to Formulas › Calculation Options › Manual and recalculate with F9.

On this sheet, column D computes days overdue with TODAY(). Switch it to the report date in H1.

Test yourself

1. Why does TODAY() in every row slow a workbook down?
2. Which key recalculates the workbook in manual calculation mode?
3. Which is better for speed in a share formula: =B2/SUM($B$2:$B$9) or =B2/$H$2, where H2 holds the total?

Practice is part of Pro

The live spreadsheet, task checking and .xlsx files in all 170 lessons. Your progress is saved in this browser; sync across devices is coming soon.

Get ProFree lessons