When the Workbook Gets Slow: Finding What Excel Is Actually Recalculating
A workbook that took a second now takes forty. Nothing was added that felt large. The cause is nearly always that Excel is recalculating far more than it needs to, on every single edit, and the fix is to find which of eight things is doing it.
What you do: set calculation to Manual so you can work, measure one recalculation, then work down the list below in order of how much each usually costs.
The short version. Excel recalculates a dependency chain. Anything that makes the chain touch a million rows makes the chain expensive.
First, make the file usable again
- Formulas, Calculation Options, Manual. Now editing does not trigger a full recalculation.
- Press F9 once and count the seconds. That is your baseline, and you need it to know whether a change helped.
- Shift+F9 recalculates only the active sheet, which narrows down where the cost is.
- Turn Manual off when you finish. A shared workbook left on Manual is a source of wrong numbers that look right.
The eight causes, most expensive first
1. Volatile functions
NOW, TODAY, RAND, RANDBETWEEN, OFFSET, INDIRECT, CELL, INFO. These recalculate whenever anything in the workbook changes, and so does everything that depends on them. Find them with Ctrl+F, Look in Formulas, searching for OFFSET( and INDIRECT(.
Replace OFFSET with INDEX, which is not volatile. Replace INDIRECT with a real reference or a table name. Replace TODAY() in ten thousand rows with one cell holding TODAY() that the rows point at.
2. Whole column references inside heavy formulas
=SUMIFS(B:B, A:A, $D2) copied down 50,000 rows asks Excel about a million rows, 50,000 times. Convert the source to a table and use structured references, which cover only the used rows and expand automatically:
=SUMIFS(Sales[Amount], Sales[Region], $D2)3. Lookups that should be a pivot or a query
200,000 lookups exist to produce a summary of forty rows. A PivotTable does the same aggregation in one pass. Better still, do it in Power Query so it happens once per refresh instead of on every calculation.
4. Conditional formatting on entire columns
This is the quiet one. Applying a rule to A:A instead of A2:A5000, then copying and pasting cells around, splits it into hundreds of overlapping rules. Home, Conditional Formatting, Manage Rules, then This Worksheet. If the list scrolls, that is your problem. Delete them all and reapply two or three to real ranges.
5. An inflated used range
Ctrl+End should land on your last cell of data. If it lands on XFD1048576, the file is carrying a million empty formatted rows. Select every row below the data, right click, Delete, then save, close and reopen. The reopen is required: the used range is recalculated when the file is loaded.
6. Links to closed workbooks
Data, Edit Links. Each external link makes Excel go looking, and a link to a file on a slow network share can add many seconds. Break the ones you do not need, and paste values where the source is finished.
7. Array formulas over more than they need
A SUMPRODUCT or a dynamic array over a million-row range does a million-row calculation. Bound the range to the data, and prefer the modern dynamic array functions, which are usually faster than the legacy Ctrl+Shift+Enter forms doing the same job.
8. Shapes, images and hidden objects
Home, Find and Select, Selection Pane will list them. A workbook that has had charts pasted into it repeatedly can accumulate hundreds of invisible objects, each of which is redrawn.
The structural fixes
| Instead of | Do | Why it is faster |
|---|---|---|
| Formulas over raw rows | Aggregate in Power Query | Runs once per refresh |
| Lookups into a second table | A relationship in the Data Model | Compressed and indexed |
| A sheet per month | One long table with a month column | One calculation chain, not twelve |
| Formulas kept for finished periods | Paste values on closed months | Nothing left to recalculate |
| Everything in one workbook | Data file and report file | The heavy part is refreshed on demand |
How to apply this to your own work
- Time one F9 on your slowest file and write the number down before touching anything.
- Search for
OFFSET(andINDIRECT(and count the hits. This is usually where the seconds are. - Open Manage Rules on each sheet and see how many conditional formats exist. The number is often in the hundreds.
- Press Ctrl+End on every sheet. Fix any that overshoot, then save and reopen.
- Move one repeated calculation into Power Query and time the difference. That comparison is what convinces a team to change how the file is built.
The one habit to keep
When a workbook slows down, ask what changed rather than what to delete. Files rarely get slow gradually; they get slow the week somebody added a column of lookups or applied a format to an entire sheet.
How long does one recalculation of your main workbook take right now?
Excel for Analysts is 378 pages that read every formula and dialog one at a time, so the workbook stops being a place where numbers appear and becomes one you can check.
Excel for Analysts, $19 →Power Query or Power Pivot explains where the work should have gone, and the row limit covers what to do when the file is simply too big for the grid.
Read Power Query vs Power Pivot →