Why Your Excel Model Is Slow (and How to Fix It)

A model that used to be instant now takes four seconds to recalculate every time you type. Nothing obvious changed. The file is 40MB and you have no idea why.

Slow workbooks almost always come down to a short list of causes. Here they are in rough order of how often they turn out to be the problem, with how to find each one.

1. Volatile functions

A volatile function recalculates every time anything in the workbook changes, not just when its own inputs change. A handful is fine. A few thousand, spread through a schedule, and every keystroke triggers a full pass.

The main offenders are OFFSET, INDIRECT, NOW, TODAY, RAND, RANDBETWEEN, and CELL and INFO in most forms. OFFSET and INDIRECT are the ones that show up in models, usually in a dynamic range or a sheet-name lookup that seemed clever at the time.

How to find them: Ctrl + F, search for OFFSET( and INDIRECT(, set Look in to Formulas, and Find All. What to do instead: INDEX is not volatile and covers most of what OFFSET is used for. Structured tables and named ranges cover most of what INDIRECT is used for.

2. Whole-column references

=SUM(A:A) looks tidy. It also asks Excel to consider 1,048,576 rows. Modern Excel optimises some of this, but nested inside a SUMIF or a lookup, repeated down a schedule, it stops being free very quickly.

How to find them: search formulas for :A), :B) and similar, or look for SUMIF( and VLOOKUP( with unbounded ranges. What to do instead: bound the range to the data, or convert the range to a proper Excel Table so it grows on its own.

3. Conditional formatting that multiplied

This one is invisible and it is often the real answer. Every time someone copies a formatted row, Excel can split the existing rule into new ones. A model that started with six conditional formatting rules can end up with several thousand nearly identical ones, each evaluated on every recalculation.

How to find it: Home > Conditional Formatting > Manage Rules, then set the scope to This Worksheet. If you scroll and keep scrolling, that is your problem. What to do: delete the lot and reapply a small number of rules across the whole range at once.

4. A used range that runs to the bottom of the sheet

Press Ctrl + End. If it lands thousands of rows below your actual data, Excel is carrying that whole area as the used range, which inflates file size and slows scrolling, selection, and calculation.

This usually comes from formatting applied to entire columns, or from data that was deleted without the rows being removed. What to do: select every row below your data to the bottom of the sheet, delete the rows themselves (not just the contents), then save and reopen. Ctrl + End should now land near your data.

5. External links to files nobody can reach

Links to a workbook on someone's old laptop make Excel pause on open and on refresh while it tries to reach a path that no longer exists. We wrote up how to find external links in an Excel workbook, including the places they hide that Edit Links does not show you.

6. Manual calculation, which is a symptom

If you find the workbook set to Manual under Formulas > Calculation Options, someone did that because it was slow. It is worth fixing the cause rather than leaving it, because manual calculation is how a model ends up being reviewed with stale numbers on screen.

Finding all of it at once

Each of the checks above is a search or a menu you can do by hand, and on one sheet that is fine. Across a 40-tab model it is an afternoon.

ExcelSurge's workbook audit runs these as part of a Performance score: whole-column references, high-impact volatile functions, conditional-formatting bloat and used-range sprawl, each reported with the cells involved and a one-click fix where a fix is safe to automate. It runs locally, so the model does not leave your machine.

If you are checking a model before it goes to someone else, the ten-minute model check covers what else is worth looking at while you are in there.