How to find what is slowing an Excel workbook
A step-by-step run through the add-in, from install to a faster workbook. Checking the sheet you are on, every explanation, every fix preview and Undo are free and unlimited. Checking every sheet at once, applying the fixes and the exportable report are paid, and each is marked Pro where it appears.
What the problem is
A slow workbook is almost never slow for one reason you can see. It is a handful of volatile formulas, a used range that runs to the bottom of the sheet, a lookup over whole columns, thousands of conditional-format fragments. Excel has no screen that says which of them is in your file, or how much each costs.
This add-in reads the formulas, the used ranges and the settings, gives each cause a weight, and lists them heaviest first. The weights are estimates from a cost model, not timings, and every number in the pane says so. It edits the workbook only when you press a fix button, and never connects to anything.
Before you start
- Save a copy first if the workbook matters. Every fix saves what it changes inside the workbook so Undo can put it back, but a copy costs nothing.
- The scan reads formulas only. It does not read your values.
1. Install
Get it from Microsoft Marketplace, or from Home › Add-ins › Get Add-ins in Excel and search for Workbook Speed Checker.
2. Open the pane
Open a workbook, then Home › Workbook speed › Check speed.
3. Check a sheet
Press Check this sheet. It reads the sheet you are on, plus the workbook-wide settings: calculation mode, styles and names. Check every sheet Pro reads them all in one go, in bands, so a large workbook does not overload Excel.
If Excel would not answer for something (a sheet that is too scattered to read, say), the pane says so under the grade. A source it could not read is never reported as clean.
4. Read the grade, the bars and a finding
- The grade runs A to E. It is never better than C while any finding is a likely main cause.
- Where the time goes shows each sheet's share of the total estimated weight.
- A finding has a rule code, where it is (press Go to to jump there), what is wrong, why it matters, a few examples and its estimated weight. Findings are ordered heaviest first.
5. Fix it Pro
Where a safe fix exists, press Preview fix to see the exact change. Nothing is written until you press the button under the preview. Each fix first checks the workbook has not changed since the scan, takes a restore point, and refuses if it cannot.
- Clear the excess formatting (WS-01)
- Freeze NOW, TODAY and RAND cells to values (WS-03)
- Set calculation to Automatic (WS-04)
- Merge identical conditional-format rules (WS-10)
6. Undo, or keep the changes
Undo appears after a fix and puts back exactly what it changed. It is free in every tier and always will be. The restore point is stored in the workbook, so it survives closing the pane and travels with the file. One fix is kept at a time.
7. The exported report Pro
Export the findings as CSV or Markdown. Any field that begins with =, +, - or @ is prefixed so a spreadsheet cannot run it as a formula.
8. Entering a licence key
Expand Sign in: enter a licence key at the bottom of the pane, paste the key and press Activate. A key covers three installs.
What each check means
Every finding links here by its rule code.
WS-01 — Used-range bloat
Excel counts every cell that has formatting as part of the used range, even if it holds nothing. Format a whole column, or paste from a wide source, and the used range can run to row 1,048,576. Excel then saves, loads and recalculates all of it, and any whole-column reference walks all of it.
What to do. Press Preview fix, then apply it (Pro). The fix clears the formatting past the data, keeping a buffer of 10% or 100 rows, and never touches a value or a formula. It refuses if a conditional-format rule reaches into the area, or if there is too much to hold for Undo; the message then points you to Excel's own Review › Check Performance.
WS-02 — Workbook size
Open, save and recalculation time grow with the cells inside the used ranges. Past five million cells it is worth a look; past twenty-five million it is probably the main cause.
What to do. Trim bloat first (WS-01). Then archive finished sheets to their own file. No automatic fix: moving data is not something to do for you.
WS-03 — Volatile functions
NOW, TODAY, RAND, RANDBETWEEN, RANDARRAY, OFFSET, INDIRECT, CELL and INFO recalculate on every change anywhere in the workbook, and so does every formula that depends on them.
What to do. NOW, TODAY and the RAND family can be frozen to their current values (Pro, after a preview). Only do it where the value is settled: a frozen cell stops updating. OFFSET and INDIRECT cells are never frozen, because they sit inside formulas that must keep following their data. Replace them with INDEX or a direct reference, which are not volatile.
WS-04 — Calculation mode
Manual calculation leaves stale results until someone presses F9, and nothing tells the next person who opens the file. Automatic calculation over thousands of volatile cells recalculates all of them on every edit.
What to do. For Manual, the fix sets Automatic (Pro). Excel then recalculates once, which on a heavy workbook can take a while. For the second case there is no fix: switch to Manual while you edit, or remove the volatiles (WS-03). Setting the mode from an add-in needs a recent Excel; older ones tell you to use Formulas › Calculation Options.
WS-05 — Iterative calculation
With iteration on, each recalculation makes several passes over circular references. That is right for a model that needs it and pure overhead if it was left on by accident.
What to do. Switch it off in File › Options › Formulas if you do not use circular references.
WS-08 — Whole-column lookups
A lookup over A:A walks as far down as the sheet's used range goes, so bloat (WS-01) multiplies the cost. It is flagged when the sheet being searched has a used range of 10,000 rows or more.
What to do. Bound the range to the data (A2:A5000), or use a Table column, which grows and shrinks with the data.
WS-09 — Repeated lookups
The same lookup copied down hundreds of rows scans its target range once per row.
What to do. One XLOOKUP spilled over the column, or a helper column that finds the position once with MATCH and then uses INDEX, scans the range once.
WS-10 — Conditional formatting
Copy and paste splits one conditional-format rule into hundreds, and Excel evaluates every fragment on every change. Whole-column custom-formula rules are checked for every row.
What to do. Rules of the same kind that differ only in range can be merged (Pro, after a preview). It refuses when a different rule sits between two identical ones, because merging would change which rule wins; when the formula has relative references, because they mean something different from each range's first cell; and for colour scales, data bars and icon sets, which this add-in cannot read in full and so does not count as fragments.
WS-11 — Custom functions
Functions written in VBA or supplied by another add-in run outside Excel's fast calculation path, one call at a time. Names you defined yourself with LAMBDA are not counted.
What to do. No automatic fix. Replace the calls with built-in functions where you can, or call the function once and reference the result.
WS-12 — Calculation chains
A running total where every cell waits for the one above (5,000 rows or more) cannot be calculated in parallel, and one edit ripples down the whole column.
What to do. A SUM over a bounded range, or a single dynamic-array formula, breaks the chain.
WS-13 — Data validation
Validation is checked on every edit inside its range, empty cells included, and it widens the used range.
What to do. Bound it to the data. No automatic fix.
WS-14 — Heavy objects
Charts and pivot tables redraw and refresh with the data they sit on, and every sheet's used range and formats load with the file. Rarely the main cause.
What to do. Context only. Remove what nobody looks at.
WS-15 — Formula density
How many cells calculate and how few patterns they share. A dense sheet is one where every other finding costs more.
What to do. Context only.
WS-16 — Style bloat
Copying sheets in from other files brings their cell styles with them, by the hundred. Each is stored and merged on every open and save, and Excel refuses new formats at about 64,000.
What to do. Home › Cell Styles › Merge, or a style-cleaning tool, removes the unused ones. No automatic fix: deleting a style changes every cell that uses it.
WS-17 — Defined names
Names are resolved on load and whenever anything that uses them recalculates. Broken ones, pointing at #REF!, are clutter left behind by deleted sheets.
What to do. Open the Name Manager, filter for Names with Errors and delete them. No automatic fix.