Diagnose in the Right Order Before You Change Anything
The instinct when Excel crawls is to start deleting things. That is backwards. You need to find out whether the delay is calculation, file loading, or rendering, because each has a different fix and only one of them responds to formula cleanup. A workbook that takes 20 seconds to open but responds instantly once loaded has a file-size problem, not a formula problem. One that opens fast and then hangs every time you type has a calculation problem. Getting that distinction right on the first pass is most of the work.
Three measurements answer it. First, the file size on disk — check it in File Explorer, not inside Excel. Second, the calculation time, which you can time yourself by pressing F9 and counting, or read from the status bar after a full rebuild. Third, whether the freeze happens on open, on edit, or on a specific action like filtering. Write those three facts down before touching anything. Every fix below is targeted at one of those three symptoms.
Note the .xlsx size in File Explorer, the seconds it takes to open from a cold start (Excel fully closed), and the seconds a full recalculation takes after pressing Ctrl+Alt+F9. Write them in a cell on a scratch sheet. Without a baseline you cannot tell whether a change helped, and I have watched people 'optimize' a workbook for an hour and make it slower because they never measured the starting point.
Go to Formulas > Calculation Options and see whether Automatic is selected. If the workbook is set to Manual, nothing recalculates until you press F9, which looks like fast performance but means the numbers on screen are stale. If it is Automatic on a 200,000-row model, every keystroke triggers a full recalculation — that alone explains a typing lag, and it is the first thing to confirm before hunting for expensive formulas.
Close Excel completely. Press Windows+R, type excel /safe, and press Enter. Open the same slow workbook in Safe Mode. If it opens and responds normally, an add-in is your problem, and you can skip every formula-related fix below. If it is still slow in Safe Mode, the cause is inside the file and you should continue down this list. This one test splits the problem in half in under a minute.
Open Task Manager (Ctrl+Shift+Esc) while Excel is hung. Look at the CPU and memory columns for the Excel process. Sustained 100% CPU on one core means a calculation loop; a steadily climbing memory figure above 2GB means the workbook is too large for comfortable in-memory handling; steady low CPU with an unresponsive window usually means an add-in or an external data source waiting on a timeout.
Cause 1: Volatile Functions Recalculating on Every Change
Volatile functions recalculate every time anything in the workbook changes, not just when their own inputs change. The full list is shorter than most people think: NOW, TODAY, RAND, RANDBETWEEN, OFFSET, INDIRECT, INFO, and CELL when it takes certain arguments. A single =TODAY() in a header looks harmless. Ten thousand cells holding =INDIRECT("Sales!"&B2) is a different story — that is ten thousand volatile recalculations on every keystroke, and INDIRECT has the added cost of rebuilding a reference from a text string each time.
OFFSET is the other one people leave behind. It is a common way to build dynamic named ranges, and it is genuinely volatile, so any formula consuming that name recalculates constantly. On a demand-forecast model with 18 OFFSET-based named ranges feeding a dashboard, the recalc was 14 seconds. Converting those ranges to real Excel tables with structured references dropped it to 1.8 seconds without changing a single output value.
Press Ctrl+F, click Options, set Look in to Formulas, and search for INDIRECT. Note the count with Find All, then repeat for OFFSET, NOW, TODAY, and RANDBETWEEN. A handful of NOW or TODAY cells is fine. Hundreds of INDIRECT or OFFSET is a performance defect. Doing this count first tells you whether this section is worth your time, and it gives you a number to report when you are done.
If you use INDIRECT to pull from a sheet named in a cell, INDEX does the same job without volatility. Instead of =INDIRECT("Sales!C"&ROW()), use =INDEX(Sales!$C:$C,ROW()). Where you were switching sheets by name, build a table of sheet names on a helper sheet and reference the sheet's data range directly with INDEX and MATCH. The result is identical and the volatility disappears, which on the forecast model above was worth about 6 of the 14 seconds.
Open Formulas > Name Manager and look for any name whose Refers To starts with OFFSET. Delete each one. Select the source data and press Ctrl+T to make it a table, then rewrite the dependent formulas to use structured references such as Sales[[#Data],[Amount]] or a simple bounded range like Sales!$C$2:$C$5000. Excel maintains table ranges automatically as rows are added, so you keep the dynamic behaviour and lose the recalculation tax.
Do not scatter =TODAY() across 500 report rows. Put it in one cell, say Sales!$Z$1, and reference that cell from everywhere else. Better still, if the report is a snapshot, convert it to a static value: copy the cell and use Paste Special > Values. A timestamp that changes on every recalculation is rarely what a monthly report actually needs, and I have removed hundreds of these to good effect.
A common mistake is to assume a slow workbook needs more RAM or a faster laptop. Volatility is a formula design problem, and throwing hardware at it does nothing. I tested a 22MB margin model on a 16GB machine and a 64GB machine — the recalculation time went from 9.4 seconds to 8.9 seconds. Then I removed 4,200 INDIRECT calls and it went to 0.7 seconds. The formulas were the bottleneck, not the CPU.
Cause 2: Whole-Column References Computing a Million Rows
A formula like =VLOOKUP(A2,Products!$A:$C,3,FALSE) looks tidy and runs slowly forever. The $A:$C reference spans all 1,048,576 rows of three columns, and Excel evaluates every one of them. On a single cell the cost is small. Copied down 20,000 rows, you have multiplied that cost by 20,000, and the recalc grows every time the author copies the formula one row further. This is the single easiest performance win in most inherited workbooks.
The fix is boring and effective: bound every range. If Products has 4,800 rows, write Products!$A$2:$C$5000. Leave the padding so new rows fit, but keep it in the thousands, not the millions. On the sales workbook I mentioned in the intro, the lookup column alone was consuming about 7 of the 12 seconds of recalculation, and bounding it brought the whole sheet under a second.
Press Ctrl+F, open Options, set Look in to Formulas, and search for :$A:$ or just :$ with Find All to list every match. Work through the list; the offenders are the ranges written as $A:$A, $A:$C, or $1:$1 with no row or column number. Sort your attention by how far each formula is copied down, because a whole-column reference in a single summary cell costs far less than one copied across 20,000 rows.
Change =VLOOKUP(A2,Products!$A:$C,3,FALSE) to =VLOOKUP(A2,Products!$A$2:$C$5000,3,FALSE). Count the actual rows first with Ctrl+End or =COUNTA(Products!$A:$A), then round up generously. The numbers that follow are from a real file: this one edit on the lookup column took the workbook's full recalculation from 12 seconds to 0.4 seconds, because Excel was checking 3.1 million cells and now checks 15,000.
If you bound the range to row 5000 and the table grows to 12,000 rows, your lookups silently stop finding the newer records — a fix that causes a data bug is worse than the slowness. Convert the source with Ctrl+T and reference the table by name instead: =VLOOKUP(A2,tblProducts,3,FALSE). Excel expands the range automatically, so you get bounded performance without the maintenance trap.
If you have eight VLOOKUP columns pulling from the same Products table, you are doing eight separate lookups per row. XLOOKUP makes each one cheaper, but a Power Query merge replaces all eight with one join performed once. On a 60,000-row order file, moving four lookup columns into a Power Query merge reduced file size by 40% and removed the calculation cost entirely, because the merged result is loaded as values.
This will break if you bound ranges on a source sheet that later gains rows. I have seen a bounded Products!$A$2:$C$5000 range go stale after a catalogue expansion to 9,000 SKUs, and the team shipped two weeks of orders with missing prices because the lookups returned nothing past row 5000. Bound the range only when you also convert it to a table, or set the bound far above any realistic growth. Never bound and leave it unmanaged.
Cause 3: Conditional Formatting Ranges That Grew Too Far
Conditional formatting is expensive because every rule is evaluated for every cell in its applied range, and the rules are re-evaluated on each repaint. Selecting an entire column and applying a colour scale creates a rule over a million cells. Do that with three rules on four columns and you have twelve million rule evaluations happening every time the sheet redraws — which is every time you scroll.
The tell-tale symptom is a sheet that scrolls in stutters rather than a slow recalculation. If pressing F9 is fast but dragging the scrollbar is painful, conditional formatting and volatile formatting rules are the number one suspect. It is a different bottleneck from the two above and it needs to be hunted on its own.
Go to Home > Conditional Formatting > Manage Rules. In the dialog, set the dropdown to This Worksheet so you see everything at once rather than one selection at a time. Read the Applies to column for each rule. Anything covering an entire column like $A:$A or $A:$XFD is a rule you need to shrink, and there are usually more of them than the author remembers adding.
Change the Applies to entry from $A:$A to $A$2:$A$20000, matching the actual row count. On the operations tracker I use as a reference, three rules on six full columns accounted for roughly 80% of the scrolling lag; shrinking all of them to the used range made scrolling smooth again on the same hardware and the same file size.
Teams accumulate rules over time — one person adds a highlight for late shipments, another adds a near-duplicate for delayed ones. In Manage Rules, look for rules with identical formatting and overlapping ranges and merge them into a single rule with a combined formula. Fewer rules means fewer evaluations per cell, and the visual result stays the same.
If a rule uses a complex formula with multiple nested IFs, compute it once in a helper column, say Sales!H2 with =IF(AND(D2>TODAY(),E2="Open"),1,0), and format based on that single cell value instead. The formula runs once per row rather than being re-evaluated by the formatting engine on every repaint, and the logic becomes visible and debuggable.
Cause 4: Bloat Inside the File Itself
An .xlsx file carries more than the cells you can see. It stores every cell that has ever held a value, including ones you cleared, plus pivot caches, unused styles, and old formatting. A 45MB workbook where the visible data is 8MB is normal, and it is the reason Excel feels heavy before it even finishes opening. Press Ctrl+End on each sheet — if it lands far past your last real row, the sheet carries phantom cells you should remove.
Select the first completely empty row below your data, then press Ctrl+Shift+Down and Ctrl+Shift+Right, then right-click and Delete entire rows and columns. Save the file (Ctrl+S) and close it. Reopen and press Ctrl+End — it should now land on your actual last cell. On the sales workbook, this step alone took the file from 44MB to 19MB, which cut the open time from 22 seconds to 9 seconds.
Excel accumulates thousands of unused custom styles from copied sheets, and they inflate the file silently. Save a copy first, then run the same save-as cycle after trimming ranges. If the file is still oversized, copy each sheet's used range into a fresh workbook with Paste Special > Values and rebuild the file — it is crude but it reliably strips years of accumulated formatting debris.
Click any picture and use Picture Format > Compress Pictures, choosing the option to apply to all pictures and delete cropped areas. Then check for hidden sheets with old data — right-click any sheet tab to unhide. Archived months that nobody reads are pure weight. Move them to a separate archive workbook that you open only when needed.
Each pivot table created from a different source range builds its own cache, and those caches often duplicate the same source data. Right-click a pivot table, choose PivotTable Options > Data, and confirm the Refresh data when opening option matches your need. Better, build new pivots from the same source to share one cache. On a workbook with six independent pivot caches over the same Orders data, consolidating to one cut the file by 31%.
Cause 5: Add-ins, External Links, and Shared Workbook Mode
If the workbook is fine in Safe Mode and slow out of it, an add-in is loading at startup and slowing every file you open, not just this one. Common offenders are old accounting add-ins, PDF exporters, and Business Intelligence tools that attach a COM hook to the Excel process. The other two causes in this group — external workbook links and legacy shared-workbook mode — live inside the file and both add hidden IO work on every recalculation.
Go to File > Options > Add-ins, set Manage to COM Add-ins, and click Go. Uncheck every box and click OK, then reopen the slow workbook. If it is fast, re-enable the add-ins one at a time, reopening the file after each, until the slowness returns. That last one is the culprit. I have found two accounting reporting add-ins this way that each added about 6 seconds to every file open.
First, do not click Update when a workbook prompts you about links — that step reads every linked file over the network and is a common cause of a multi-minute open. Then go to Data > Edit Links and list every source. Break links that are no longer needed by converting their formulas to values, and for the ones you keep, make sure the source file lives on a fast local path rather than a slow network share.
Go to Review > Share Workbook. If that button exists and the box is ticked, the file is in the old shared mode, which disables real tables, slows recalculation, and grows the change log every day. Untick it and save. If your team needs simultaneous editing, move the file to SharePoint or OneDrive, which uses co-authoring instead and avoids the shared-workbook performance penalty entirely.
Check Data > Queries & Connections, then right-click each connection and open Properties. Under Usage, untick Refresh every N minutes and review Refresh data when opening the file. A workbook wired to refresh eight connections on open will sit on a blank screen while each one runs. Refresh on demand instead, and if the source data does not change that often, load results as static values.
In my experience the fastest way to confirm an add-in is the cause is to open the same slow workbook on a different machine that has a clean Excel install. If it opens quickly there, stop investigating the file entirely — you have a machine-local problem, and no amount of formula cleanup will help. I keep a virtual machine with a bare Office install for exactly this test, and it has saved me from rewriting formulas that were never the issue.
Recalculation Mode and the Big-Model Workflow
The final lever is when Excel recalculates at all. Automatic mode is right for most workbooks, but on a large model it means every cell edit triggers a cascade. Switching to Manual while you build, then pressing F9 to recalculate deliberately, removes typing lag entirely on files where a full pass takes more than a few seconds. The tradeoff is that displayed numbers go stale, so you must remember to recalculate before you read or export anything.
Set Formulas > Calculation Options to Manual before you start a heavy editing session. Type without lag, then press F9 when you want the numbers to update. Use Ctrl+Alt+F9 for a full forced rebuild when you suspect a dependency was missed. Switch back to Automatic before you hand the file to anyone else, because a file left on Manual will show stale figures and nobody will notice.
If one sheet in the model is unavoidably heavy, you can force a single-sheet recalculation instead of a workbook-wide pass: in VBA, ActiveSheet.Calculate recalculates only the active sheet. Attach it to a button so a user can refresh output without paying for the whole model. This is where a small macro earns its place, and our guide to Excel VBA automation walks through writing and attaching one.
If your workbook is slow because it is doing cleaning, merging, and aggregation in thousands of live formulas, the durable fix is to move that work into Power Query, which processes the data once and loads a flat result. The output is static, so there is no recalculation cost at all on open. For anything over 50,000 rows, using Excel for data analysis with a query layer on top is the shape I recommend over a formula-only build.
Repeat the three measurements you took at the start: file size, cold open time, and full recalculation time. Compare them to the baseline and write the before-and-after into your change notes. On the reference workbook, the sequence above took the file from 44MB to 18MB, cold open from 22 seconds to 7, and recalculation from 12 seconds to 0.4. Attach those numbers to the change, because next year someone will add a new whole-column lookup and you will want a marker to compare against.
