How to audit slow Excel calculation
Slow calculation has a short list of structural causes: volatile functions, whole-column references, a very large number of formulas, and conditional formatting that was applied as thousands of separate rules. Time the workbook once, then use the formula and formatting checks to see which cause you actually have. Rewriting fast formulas first will not make a volatile sheet feel better.
Separate “slow to open” from “slow to calculate”
A file that takes a minute to open and then edits normally is usually large: pictures, caches, or a huge sheet. A file that opens and then shows “Calculating” in the status bar, or pauses on every edit, is a calculation problem. The size guide covers the first. This guide covers the second. They overlap when a huge number of formulas is both a size and a speed issue. Note which symptom you have before you change the workbook. Manual calculation mode hides the symptom and does not remove it. Use it while you edit, and turn it off when you want the truth.
Time one change
On a copy, switch to automatic calculation and change one input that should recalculate the model. If you cannot tell whether it finished, Excel’s calculation is the problem you think it is. You do not need a benchmark suite. You need a before and after on the same machine. Write down a rough count of seconds. After each repair below, do the same edit again. Keep the repair that moves the clock. Revert the ones that do not.
Volatile functions
NOW, TODAY, RAND, RANDBETWEEN, OFFSET, INDIRECT, INFO, and CELL recalculate more broadly than ordinary formulas. The volatile checker counts them and gives sample cells. A header with one TODAY is harmless. A sheet of OFFSET formulas that build a rolling window is not. Replace OFFSET with INDEX. Replace INDIRECT sheet names with a real reference or with a small set of direct formulas. Replace RAND in a report with a value if the sample does not need to change. Leave a volatile function in place when the model truly depends on it, and expect the clock to show that cost.
Whole columns and too many formulas
SUMIFS, VLOOKUP, and XLOOKUP aimed at A:A scan far more cells than the table uses. Point them at the real table or at a structured table reference that grows correctly. A hundred thousand ordinary formulas can be slow even when none are volatile. The checker reports that count. Where a column is a copy of one logic, a PivotTable, a single dynamic array, or Power Query may replace thousands of cell formulas. That is a design change. Do it when the count is the finding, not as a fashion.
When you want the numbers from your own workbook, check formatting bloat on this device.
Conditional formatting as a calculation tax
Formula-based conditional formats recalculate too. A column with one rule is cheap. A column with a rule per cell is a second workbook of formulas you cannot see in the formula bar. The formatting checker counts rules and fragmented ranges. Open Manage Rules, delete the fragments, and create one rule for the range. Data bars and icon sets are included. If the count is small, formatting is not your speed problem. Go back to volatile functions and whole columns.
Calculation settings that confuse the test
Multi-threaded calculation should usually stay on. Iterative calculation makes circular models slow or unstable. If iteration is on and you did not mean to allow circular references, turn it off and fix the circular chain instead of raising the iteration count. Add-ins and workbooks in the same Excel session calculate too. Close other files when you time yours. External links that refresh on open add waiting that feels like calculation and is actually a network call. The link finder shows those targets.
When the audit is done
You should be able to say which finding moved the clock: volatile calls, whole-column references, formula volume, or formatting rules. If none of them moved it, the delay is outside the grid: a pivot refresh, an add-in, a query, or a computer that is out of memory because the file is enormous. That last case belongs to the size guide. A fast file can still contain #REF! and hidden sheets. Speed is one pillar of the Audit score, not the whole report.
What to change first when several findings fire
If the volatile count is high and the formatting count is high, change one family and time again. A good order is whole-column references first, because the edit is local and easy to reverse: replace A:A with the real table. Volatile OFFSET and INDIRECT blocks come next, because each one can wake up a large region. Formatting consolidation comes after that if the clock still moves when you edit a cell that should not touch those rules. A huge formula count is last, because replacing a column of formulas with one array or a pivot changes the workbook’s design and is harder to undo. Write the seconds next to the change. You are building a small lab note for this file, not a universal ranking of Excel functions. The next slow workbook may put formatting first. The method is what transfers. The order is a suggestion for the afternoon you have one file open. If a change makes the clock worse, put it back. Speed work is full of clever rewrites that add INDIRECT “just to make the sheet name dynamic” and quietly create the next audit. Prefer a direct reference you can see over a flexible reference you will measure again in a month.
Notes worth keeping
Write four numbers on a scratch sheet or in the email to yourself: volatile calls, whole-column references, formula count, and conditional-formatting rules, plus the rough seconds for one edit. After the repair, write them again. That pair of notes is the audit. It is more useful than a screenshot of the status bar, because the next person can see which number moved. If you inherit a slow file and the notes are missing, you are allowed to create them before you change formulas. Speed work without a before-and-after becomes a matter of taste, and taste is how OFFSET gets added back “because it is flexible.”
Related reading
The closest repair notes are A list of volatile functions and Fix stored formula errors .
Walk the same checks in order with the Workbook audit checklist.
The terms behind this guide are Conditional formatting rule and Volatile function .
The paired check for that note is the Volatile formula checker for Excel.
Questions people ask before trusting the result
Does manual calculation fix a slow file?
It hides the wait until you press Calculate. Use it as a working mode. Do not send a workbook that only feels fast because calculation is manual, unless the recipients know to calculate.
Are XLOOKUP and VLOOKUP volatile?
No. They are slow when the range is a whole column or when you have a great many of them. INDEX is the usual replacement for OFFSET, not for every lookup.
Will fewer formats change results?
They change colors and icons. They should not change values. Volatile replacements such as removing RAND will change values. Read the cell before you replace it.
Can I trust a single timing?
Treat it as a direction. The same edit, on the same copy, before and after one change, is enough to see a large difference. Tiny differences are noise.
Why is .xlsb sometimes faster?
It opens faster because the parts are binary. Calculation of the same formulas is a separate question. A volatile .xlsb still calculates those functions.