Why Your Workbook Takes 45 Seconds to Recalculate: Volatile Functions, Whole-Column Formulas and the 44 Million Cells Nobody Asked For

The file is called Orders_Master_v7.xlsx. It has 8,400 rows and six columns — 50,400 cells of data, which is nothing. There are no macros in it, no Power Pivot data model, no million-row query. It is a list of orders and a block of summary formulas.

Type "North" into a cell and press Enter, and the status bar reads Calculating (8 processors): 12% for the next forty-five seconds.

Everybody has a theory. The file is corrupted. The network drive is slow. Excel is bloated. It needs more RAM. None of those are true, and you can prove it in ten minutes, because slow workbooks are almost never mysterious — they are arithmetic. Fourteen summary formulas in this file are written against whole columns, which means each one reads 3,145,728 cells to answer a question about 8,400 rows. Two hundred and twenty more are built on OFFSET, which is volatile, which means every one of them recalculates whenever anything anywhere in the workbook changes.

Multiply it out: 44,040,192 cell reads on every keystroke, against the 352,800 that actually hold anything.

This article is about finding that arithmetic in your own file and taking it out. The same workbook, after four changes and no loss of a single number, recalculated in 0.4 seconds.

What you need. SUMIFS, COUNTIFS, INDEX, MATCH and VLOOKUP work in every version. Tables and structured references need Excel 2007 or later. LET, FILTER, UNIQUE and XLOOKUP need Microsoft 365 or Excel 2021. The calculation shortcuts and the Manage Rules dialog have been there since long before any of that.

1) Measure It First — Guessing Costs You the Afternoon

🎯 Scenario: Everyone agrees the file is slow. Nobody can tell you how slow, or which part of it is slow, and the last person who tried to fix it deleted a sheet and broke the summary. You need a number before you change anything.

Twelve rows of the tracker, laid out exactly as the real 8,400 are. Before touching a formula, take two measurements:

A full recalculation. Press Ctrl+Alt+F9. That forces every formula in every open workbook to recalculate whether or not Excel thinks it needs to — the worst case, and the only honest one. Time it with a stopwatch. Do it three times and keep the middle number.

A single edit. Type a value into any empty cell and press Enter. Time that too. This is the number people actually live with, because it is what happens every time they touch the file.

In the tracker: 44.6 seconds for a full recalculation, 8.9 seconds for one edit.

The gap between those two numbers is itself a diagnosis. If a single edit costs a large fraction of a full recalculation, something is forcing Excel to recalculate far more than the cells you touched — that is volatility, and it is section 3. If a single edit is fast but a full recalculation is glacial, you have a lot of expensive formulas but a healthy dependency tree — that is sections 6 and 7.

Know your calculation keys. F9 recalculates dirty cells in all open workbooks. Shift+F9 does the active sheet only. Ctrl+Alt+F9 forces every formula everywhere. Ctrl+Shift+Alt+F9 rebuilds the dependency tree first and then forces everything — the sledgehammer, and the one to use when you suspect Excel's own bookkeeping has gone stale.

2) What Excel Actually Recalculates

Excel does not recalculate the workbook when you press Enter. It recalculates what changed, and what depends on what changed.

It maintains a dependency tree: which cells feed which formulas. Edit F2, and Excel marks F2 dirty, then marks everything that reads F2 dirty, then everything that reads those, and so on down the chain. Then it calculates the dirty cells in dependency order.

On a healthy file this is astonishingly efficient. A 200,000-row model can respond instantly to an edit, because the edit touched nine formulas and Excel recalculated nine formulas.

Two things wreck it:

1. Volatile functions declare themselves dirty on every calculation, no matter what changed. Everything downstream of them is dirty too. The dependency tree stops being a filter.
2. Formulas that read more cells than they need. The tree is still doing its job; the individual formulas are just enormous.

Almost every slow workbook is one of those two, and the file in this article is both. Sections 3 to 5 are the first problem. Sections 6 to 9 are the second.

3) The Nine Volatile Functions, and the Contagion Rule

A volatile function recalculates on every recalculation cycle, whether or not any of its inputs changed. There are nine of them in ordinary use:

The first five are honest: they are volatile because their answers genuinely change on their own. The last four are volatile because Excel cannot see what they will read until it runs them, so it has to assume the worst.

Volatility is contagious, and that is the part that hurts. Any formula that refers to a volatile cell is treated as volatile. Any formula that refers to that one is volatile too. It spreads all the way down the chain.

So a single TODAY() in Settings!B2, with 400 formulas reading it, does not cost you one recalculation. It costs you 401 — on every edit, forever.

=IF(A2<TODAY()-30,"Overdue","Current")

Copied down 8,400 rows, that is 8,400 volatile formulas. Written once in Settings!$B$2 and referenced:

=IF(A2<$B$2-30,"Overdue","Current")

...it is one volatile cell and 8,400 ordinary ones. Same answer. And if the ageing only has to be right as of the moment the file was opened — which is what everyone actually means — you can go further and type the date as a hard value with Ctrl+;, which puts today's date in as a static number with no formula behind it at all.

How to find them. Ctrl+F, search Formulas, look for OFFSET(, INDIRECT(, TODAY(, NOW(, RAND, INFO( and CELL( one at a time, and use Find All — the dialog gives you a count and a clickable list of every hit. That count is your volatility budget.

4) OFFSET Is a Volatile INDEX

OFFSET is the single most common cause of a slow workbook that nobody suspects, because it looks harmless and it does something useful.

=SUM(OFFSET($F$1,MATCH($H$2,$B:$B,0)-1,0,12,1))

The 220 formulas in the tracker built on this pattern are the reason a single edit costs 8.9 seconds. INDEX does the same job and is not volatile:

=SUM(INDEX($F:$F,MATCH($H$2,$B:$B,0)):INDEX($F:$F,MATCH($H$2,$B:$B,0)+11))

That looks worse and runs enormously better. The key fact is that INDEX can return a reference, not just a value, so it can sit on either side of a range colon. Excel resolves it as part of building the dependency tree instead of at calculation time, which is exactly why it is not volatile.

The other classic OFFSET habit is the dynamic named range:

Sales_Data  =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),6)

That grows with the data, which is why people write it, and it is volatile, which is why the file is slow. Two replacements, both non-volatile:

Sales_Data  =Sheet1!$A$2:INDEX(Sheet1!$F:$F,COUNTA(Sheet1!$A:$A))

Or — better, and the answer for almost every real workbook — select the data and press Ctrl+T to make it a Table. Table1[Revenue] grows and shrinks with the rows on its own, reads as English in every formula that uses it, and costs nothing.

5) INDIRECT Breaks More Than Speed

INDIRECT builds a reference out of text:

=SUM(INDIRECT($A$2&"!F2:F8401"))

It is volatile for the same reason OFFSET is, and it has two extra problems that make it worse than a performance issue.

It is invisible to the dependency tree. Excel cannot know that this formula reads Mar!F2:F8401 until it evaluates the text, so the cells it reads are not registered as its precedents. Trace Precedents shows nothing. And because Excel cannot order the calculation properly, it recalculates the formula every time to be safe.

It does not survive editing. Rename the sheet Mar to March and =Mar!F2 follows the rename automatically, while =INDIRECT("Mar!F2") becomes #REF! immediately. Insert a row and a normal reference shifts; the text inside INDIRECT does not.

Replace it with whichever of these fits:

• A Table with structured references when the target is one growing range.
• CHOOSE when you are picking between a known handful of ranges: =SUM(CHOOSE($A$2,Jan!F:F,Feb!F:F,Mar!F:F)). Not volatile.
• A stacked range plus SUMIFS when you are switching sheets by name — put the sheet name in a column and filter on it instead.
• Power Query when the real requirement is "combine these tabs", which it usually is.

The one place INDIRECT genuinely earns its place is a formula that must survive row deletion — =SUM(INDIRECT("A1:A10")) keeps meaning A1:A10 no matter what gets deleted. That is a real and narrow use. Building a dashboard out of it is not.

6) Whole-Column References and the 1,048,576-Row Tax

Here is the formula that costs the most in the tracker, written the way most people write it:

=SUMPRODUCT((B:B="North")*(F:F))

A modern worksheet has 1,048,576 rows. B:B is 1,048,576 cells and F:F is another 1,048,576, and SUMPRODUCT genuinely builds arrays that big and multiplies them element by element. Three whole columns in one formula — the pattern in the real file uses a date column too — is 3,145,728 cells read to answer a question about 8,400 rows.

Fourteen of those formulas in the summary block:

Excel does apply some used-range trimming — SUMIFS and COUNTIFS are notably better at ignoring the empty tail than SUMPRODUCT is — but you cannot rely on it, and section 13 shows how the used range gets corrupted anyway. Bound the ranges yourself:

=SUMIFS(F2:F8401,B2:B8401,"North")

Or turn the data into a Table and stop thinking about it:

=SUMIFS(Orders[Revenue],Orders[Region],"North")

On the twelve rows above:

=SUMIFS(F2:F13,B2:B13,"North")

...returns 20,778.50, and so does the whole-column SUMPRODUCT, and so does the Table version. Three formulas, one answer, wildly different costs.

The exception worth knowing. VLOOKUP and MATCH against a whole column are much less damaging than an aggregate against one, because they stop at the first hit rather than scanning everything. Still bound them — but if you are triaging, fix the aggregates first.

7) SUMPRODUCT Against SUMIFS

🎯 Scenario: The summary block has fourteen cells in it, all written with SUMPRODUCT, all correct, and it is the slowest part of the file. You want to know whether rewriting them is worth an hour.

These two return the same number on the sample data:

=SUMPRODUCT((B2:B13="North")*F2:F13)
=SUMIFS(F2:F13,B2:B13,"North")

Both give 20,778.50. What differs is how they get there.

SUMIFS, COUNTIFS and AVERAGEIFS run on a dedicated, optimised code path in Excel — one that understands ranges, uses the used-range boundary, and multithreads across cores. SUMPRODUCT is a general-purpose array engine: it materialises (B2:B13="North") as an array of TRUE/FALSE, coerces it to 1/0 by multiplication, multiplies it against the revenue array, and sums the result. On twelve rows the difference is unmeasurable. Over whole columns, across fourteen formulas, it is most of your forty-five seconds.

Rewrite the ones that are just filtered sums and counts:

=COUNTIFS(B2:B13,"North")
=SUMIFS(F2:F13,B2:B13,"North",D2:D13,"Desk Riser")

Keep SUMPRODUCT for what only it can do — weighting one column by another, and criteria that are expressions rather than ranges:

=SUMPRODUCT(E2:E13,F2:F13)/SUM(E2:E13)
=SUMPRODUCT((YEAR(A2:A13)=2026)*(E2:E13>100)*F2:F13)

That is a fair trade. SUMPRODUCT is a fine function being asked to do a job that a faster function does exactly as well.

8) The Same Lookup, Written Five Times

🎯 Scenario: One formula in the commission column takes visibly longer than the rest of the sheet put together, and it is 300 characters of nested IF that nobody wants to touch.

=IF(ISNA(VLOOKUP(C2,Reps!A:D,4,FALSE)),"Unassigned",
   IF(VLOOKUP(C2,Reps!A:D,4,FALSE)>0.1,F2*VLOOKUP(C2,Reps!A:D,4,FALSE),
   F2*VLOOKUP(C2,Reps!A:D,4,FALSE)*0.9))

That is four identical lookups over a whole column, in one cell, copied down 8,400 rows: 33,600 scans where 8,400 would do. Excel does not cache the repeat — it evaluates each one.

LET computes it once and names the result:

=LET(
  rate, IFERROR(VLOOKUP(C2,Reps!$A$2:$D$60,4,FALSE),""),
  IF(rate="","Unassigned",
  IF(rate>0.1, F2*rate, F2*rate*0.9))
)

One lookup, bounded range, same answer, and it is now a formula a human can read.

Without LET — Excel 2019 and earlier — the answer is a helper column. Put the lookup in H2, refer to H2 from the commission formula, and let the sheet get one column wider. Helper columns have a reputation as a beginner's habit; on performance they are the opposite. One lookup per row, calculated once, visible when it goes wrong, and trivially auditable. A megaformula that recomputes the same thing four times is not more elegant, it is four times slower and nobody can debug it.

9) Sorted Data and the 14-Comparison Lookup

An exact-match lookup is linear. =VLOOKUP(x, tbl, 2, FALSE) and =MATCH(x, rng, 0) walk the range from the top until they hit a match, so on an 8,400-row reference table a miss costs 8,400 comparisons and an average hit costs 4,200.

An approximate-match lookup on sorted data is binary. It halves the range at each step: 8,400 rows resolve in about 14 comparisons. That is not a percentage improvement, it is a change of category — and on 8,400 lookups against an 8,400-row table it is the difference between a coffee break and no perceptible delay.

The catch is that VLOOKUP(...,TRUE) returns the nearest smaller value when there is no exact match, which is silently wrong for an ID lookup. The two-step pattern fixes that:

=IF(VLOOKUP(C2,Reps!$A$2:$D$8401,1,TRUE)=C2,
    VLOOKUP(C2,Reps!$A$2:$D$8401,4,TRUE),
    "Not found")

The first lookup asks "what is the closest key at or below mine?" and compares it to the key you wanted. If they match, the row is real and the second lookup fetches the value. If not, nothing matched. Two binary searches — 28 comparisons — instead of one linear scan of 8,400.

XLOOKUP exposes the same machinery directly:

=XLOOKUP(C2,Reps!$A$2:$A$8401,Reps!$D$2:$D$8401,"Not found",0,2)

search_mode 2 means binary search, ascending. It keeps exact-match semantics and gets binary-search speed.

The condition is absolute. Binary search on unsorted data does not error — it returns a wrong answer, quietly, on some rows and not others. Sort the reference table by its key, and if the table is rebuilt by an import, sort it as part of the import rather than trusting that it arrives sorted.

10) The 1,700 Conditional Formatting Rules

Open Home → Conditional Formatting → Manage Rules → This Worksheet on a workbook that has been in use for a few years. The number in that list is often the surprise of the day.

Conditional formatting rules are formulas, they are evaluated constantly, and they multiply behind your back. Copy and paste a block of ten rows inside a formatted range and Excel frequently cannot keep the rule as one rule any more — it fragments it into several, each covering a slice of the range. Do that a hundred times over two years and a workbook that has three rules in its design has 1,700 in its file.

The damage compounds when the rules are written against whole columns (Applies to: $A:$F) or use volatile functions (=$A1<TODAY()), because now every scroll and every edit re-evaluates a formula across a million rows.

The fix is unglamorous and fast:

1. Manage Rules → This Worksheet, and read the Applies to column. Fragments of the same rule stack up as near-identical entries.
2. Delete the fragments, keep one rule, and set Applies to to the used range: =$A$2:$F$8401.
3. Replace TODAY() inside a rule with a reference to one cell that holds it.
4. Where a rule exists only to colour a value band, check whether a number format or a Table style does the same job for free.

Data validation deserves the same audit while you are there: a dropdown whose source is =INDIRECT($B$2&"_List") is volatile, and one per row means one volatile evaluation per row.

11) Do It Once Instead of 8,400 Times

The tracker has a column that flags large North orders, written the obvious way and filled down:

=IF(AND(B2="North",E2>100),F2,"")

8,400 formulas to produce a list of maybe 300 rows. One dynamic array formula replaces the lot:

=FILTER(A2:F8401,(B2:B8401="North")*(E2:E8401>100),"None")

One formula, one calculation, one result that resizes itself. Same for the list of regions someone maintained by hand and someone else maintained with 8,400 COUNTIF formulas:

=SORT(UNIQUE(B2:B8401))

The principle behind both: the number of formulas matters as much as the cost of each one. Excel carries per-formula overhead — dependency bookkeeping, dirty-flag handling — and 8,400 cheap formulas can easily cost more than one expensive one.

12) Manual Calculation Is Triage, Not a Fix

Formulas → Calculation Options → Manual stops Excel recalculating on every edit. Press F9 when you want the numbers.

It makes an unusable file usable for an afternoon, and it is genuinely the right tool while you are doing bulk data entry into a heavy model. It is not a fix, for two reasons.

It ships stale numbers. A workbook in manual mode shows whatever was calculated last. Save it, mail it, and the recipient opens a file whose totals do not match its data. There is no warning beyond a small "Calculate" in the status bar that nobody reads.

The setting travels, and it is sticky. Calculation mode is stored in the workbook, and the mode of the first workbook opened in an Excel session applies to every workbook opened afterwards in that session. One file left on Manual by a colleague in 2019 can quietly put your own files on Manual, which is a class of bug that will consume a whole day if you have not met it before.

If you use it, use it deliberately: switch to Manual, do the work, press Ctrl+Alt+F9, switch back to Automatic, then save.

13) The Used Range That Runs to Row 1,048,576

Press Ctrl+End. On a healthy workbook it lands on the last cell of your data — F8401. If it lands on XFD1048576, or somewhere thousands of rows below anything you can see, Excel believes the used range is the whole sheet.

That belief is expensive. It inflates the file, it defeats the used-range trimming that makes SUMIFS tolerable on whole columns, and it makes scrollbars useless. It is caused by formatting applied to entire columns, by data that was deleted with the Delete key instead of by deleting the rows, and by imports that left a trail of empty-but-formatted cells.

The repair, in order:

1. Click the row header immediately below your last row of data.
2. Ctrl+Shift+↓ to select every row to the bottom of the sheet.
3. Right-click → Delete — the row-deletion command, not the Delete key, which only clears contents.
4. Do the same for columns to the right of your data: Ctrl+Shift+→, right-click, Delete.
5. Save, close and reopen. The used range is only recalculated on save, so nothing appears to change until you do this.

On the tracker: 41 MB → 2.3 MB, and Ctrl+End back to F8401.

While you are there, check Data → Queries & Connections and Data → Edit Links for connections nobody needs any more. A formula referring to a closed workbook makes Excel read that file to resolve it; a handful of those across a slow network share will explain a delay all by themselves.

14) Finding Which Formulas Actually Cost the Money

Everything so far assumes you know where the time goes. When you do not, bisect for it — no add-in required.

1. Copy the workbook. Everything below is destructive; never do it in the live file.
2. Select the whole summary block and Copy → Paste Special → Values. Re-time with Ctrl+Alt+F9.
3. If the time collapsed, the summary block is your problem and you can bisect inside it — convert half to values, re-time, halve again. Six rounds narrows 64 formulas down to one.
4. If it did not collapse, do the same sheet by sheet. Delete a sheet, re-time, undo.

Ten minutes of this beats any amount of theorising, and it regularly finds something nobody predicted: one array formula on a forgotten sheet, one lookup into a closed workbook on the network, one conditional formatting rule covering $A:$XFD.

Then re-measure the way you started, with the stopwatch and Ctrl+Alt+F9, and write both numbers down. In the tracker the four changes that mattered were: SUMPRODUCT over whole columns became bounded SUMIFS (44.6s → 6.1s), 220 OFFSET formulas became INDEX (6.1s → 1.9s), the used range was repaired (1.9s → 0.9s), and 1,700 conditional formatting rules became four (0.9s → 0.4s).

15) Common Mistakes

• Optimising before measuring. Rewriting the formulas you happen to dislike, then finding the file is still slow because the cost was in conditional formatting all along.
• Leaving TODAY() or NOW() in a filled-down column. One cell holds it; 8,400 rows read that cell.
• Assuming OFFSET is the only way to make a range grow. A Table does it, without volatility, and reads better.
• Reaching for INDIRECT to switch between sheets. CHOOSE covers a fixed list; stacking the data and filtering covers the rest.
• Whole-column references in aggregates. SUM, SUMPRODUCT, SUMIFS and COUNTIFS over A:A in a file with 8,400 rows of data.
• Repeating the same VLOOKUP inside one formula. Four copies cost four lookups; LET or a helper column costs one.
• Binary-search lookups on unsorted data. Fast, and wrong, with no error to warn you.
• Leaving the file on Manual calculation. The numbers on screen stop being the numbers the data implies, and the setting follows the file to whoever opens it next.
• Deleting cell contents instead of deleting rows. The used range keeps the rows, and so does the file size.
• Converting formulas to values in the live workbook to make it fast. That is not optimisation, it is data loss with a stopwatch attached.

Conclusion

A slow workbook is a workbook doing more work than the question requires, and the amount is usually measurable in a couple of minutes. Press Ctrl+Alt+F9 and time it. Count the volatile functions with Find All. Look at what the aggregates read against what actually holds data. Press Ctrl+End and see where it lands.

In this file the answer was four changes, none of them clever, and none of which altered a single number: bound the ranges, replace OFFSET with INDEX, repair the used range, consolidate the formatting rules. Forty-five seconds became four tenths of one.

The general rule underneath all of it: make Excel read the cells that hold your data, once, and no others. Almost every performance fix in Excel is a special case of that sentence.