Why your MIS workbook is slow: volatile formulas and what to use instead

If your monthly MIS pack takes seconds to respond every time you type a number, the usual culprit is not the data volume. It is volatile functions. A volatile function recalculates whenever anything in the workbook changes, and so does every formula that depends on it. One OFFSET at the top of a chain can force thousands of cells to recalculate on every keystroke.

1. The volatile functions

OFFSET and INDIRECT: the most common in MIS packs, used for dynamic ranges and sheet-name switching.
TODAY and NOW.
RAND, RANDBETWEEN and RANDARRAY.
CELL and INFO (most uses).

Conditional formatting rules and data validation lists that use these functions are volatile too, and they are easy to miss.

2. Find them

Press Ctrl + F, choose Options, set Look in to Formulas, Within to Workbook, and search for OFFSET(, then INDIRECT(, and so on. Find All lists every cell.
Check Home → Conditional Formatting → Manage Rules (choose "This Worksheet" for each sheet) and Formulas → Name Manager for named ranges built with OFFSET.

3. Replace them

Instead of	Use	Example
`OFFSET` for a growing range	An Excel Table (Ctrl + T) and structured references	`=SUM(Sales[Amount])`
`OFFSET` for "last n months"	`INDEX` ranges, which are not volatile	`=SUM(INDEX(B2:M2,n0):INDEX(B2:M2,n1))`
`INDIRECT` to pick a sheet	One long data table with a Month or Entity column, plus `SUMIFS` / `XLOOKUP`	`=SUMIFS(Data[Amount],Data[Entity],$B$1,Data[GL],$A5)`
`VLOOKUP` with `MATCH` on whole columns	`XLOOKUP` on Table columns	`=XLOOKUP(A5,GL[Code],GL[Name],"Not mapped")`
`TODAY()` in hundreds of cells	One input cell with the reporting date, referenced everywhere	`=EOMONTH(Settings!$C$3,0)`
The same sub-calculation repeated	`LET` to calculate it once	`=LET(v,SUMIFS(...),IF(v=0,"",v/Budget))`

A reporting-date input cell is better practice anyway: the pack for September stays the September pack when you open it in November.

4. Other speed fixes

Avoid whole-column references such as A:A inside SUMPRODUCT and array formulas.
Move heavy data shaping to Power Query and load only the clean table into the model. See structuring SAP data dumps with Power Query.
Switch to Formulas → Calculation Options → Automatic except for data tables if the pack uses What-If data tables.
Delete unused ranges below the data (Ctrl + End shows how far Excel thinks the sheet goes).

5. Make it reviewable

Fast is good; checkable is better. Keep inputs, calculations and outputs on separate sheets, colour input cells consistently, add a checks sheet that reconciles the pack to the trial balance, and avoid hard-coded numbers inside formulas. The free templates follow this layout.