8 Ways to Speed Up a Slow Excel Workbook
A workbook that takes thirty seconds to open, freezes when you type, and shows “Calculating: 4 Processors 12%” in the status bar is not a hardware problem most of the time — it is a design problem. The good news is that the same handful of culprits show up in almost every slow file I have ever been asked to fix. This guide walks you through diagnosing the bottleneck first, then applying eight fixes roughly in order of how much speed they typically buy you.
Diagnose Before You Fix
Don’t start deleting things at random. Five minutes of detective work tells you which of the eight fixes below will actually matter for your file.
Check the file size first. A workbook with 20,000 rows of data and a few formulas should be a few megabytes at most. If you are staring at a 60 MB file, something is bloated — usually formatting, unused cells, or embedded objects — and fixes 3, 6, and 7 are your targets. If the file is small but calculation is slow, the problem is formulas: start with fixes 1, 2, and 5.
Find the true used range. On each sheet, press Ctrl+End. If your data ends at row 5,000 but Excel jumps to row 1,048,576, it is tracking — and saving — a million empty rows of formatting. Delete the excess rows and columns entirely (select the full rows, right-click, Delete, then save), or follow our guide to delete unused columns to reduce file size.
Time a recalculation. Press F9 and count the seconds. Then make one trivial edit and watch whether the whole book recalculates again. If a single keystroke triggers a multi-second calculation, you almost certainly have volatile functions or massive reference ranges.
Check for external links. Hidden links to other workbooks force Excel to resolve paths on open and on recalc. Use Data > Edit Links, or see how to find external links in Excel and break the ones you no longer need.
1. Hunt Down Volatile Functions
A volatile function recalculates every single time Excel recalculates anything, anywhere in the workbook — even if nothing it depends on changed. The main offenders:
| Function | Volatile? | Common faster alternative |
|---|---|---|
NOW(), TODAY() | Yes | A typed-in static date, refreshed manually |
OFFSET() | Yes | INDEX() (non-volatile) |
INDIRECT() | Yes | Direct references or INDEX() |
RAND(), RANDBETWEEN() | Yes | Paste random values as static once generated |
CELL(), INFO() (most arguments) | Yes | Usually removable |
One TODAY() cell is harmless. The problem is when it sits inside a formula that has been filled down 50,000 rows, or when a dynamic range built with the OFFSET function feeds twenty other formulas. Every keystroke anywhere in the file now triggers a cascade.
The two highest-value swaps:
- OFFSET → INDEX.
OFFSET($A$1,0,0,COUNTA($A:$A),1)becomes$A$1:INDEX($A:$A,COUNTA($A:$A)). Same dynamic range, zero volatility. - INDIRECT → structured references. If you are using INDIRECT to build sheet names from text, consider consolidating the data onto one sheet instead. INDIRECT is also why a workbook can stay slow even in manual calculation mode — Excel cannot trace its dependencies, so it recalculates defensively.
To find them, use Find & Replace (Ctrl+F), set “Look in” to Formulas, and search for OFFSET(, INDIRECT(, NOW(, and so on, one at a time.
2. Replace Full-Column References
=SUMIF(A:A,"West",B:B) looks tidy, but especially inside array-style formulas like SUMPRODUCT, Excel may evaluate the entire column — over a million cells — per formula. Multiply that by a few thousand copied-down formulas and you have your freeze.
The clean fix is converting your data to an Excel Table (Ctrl+T). Formulas then reference Table1[Amount] instead of B:B, and the reference grows and shrinks with the data automatically — here is how to create a table in Excel.
Two related formula upgrades while you are in there: replace SUMPRODUCT((A:A="West")*(B:B)) patterns with SUMIFS over a Table column — SUMIFS is heavily optimized while SUMPRODUCT evaluates everything you feed it. And if ten columns each run a lookup against the same key, do the match once with MATCH in a helper column and point ten cheap INDEX formulas at it. Modern XLOOKUP with binary search mode (search_mode of 2 on sorted data) is dramatically faster still on large tables.
3. Cut Back Conditional Formatting
Conditional formatting rules are evaluated constantly — on every recalculation and every screen repaint, including scrolling. Worse, copying and pasting silently fragments rules: a single rule applied to A1:A1000 can splinter into hundreds of duplicates each covering one cell. I have opened workbooks with over 10,000 rules that the owner thought had three.
Open Home > Conditional Formatting > Manage Rules, set the dropdown to “This Worksheet,” and look. If you see dozens of near-identical rules with fragmented ranges, delete them all and rebuild one rule per behavior over the full intended range — our guides on how to use conditional formatting and how to remove conditional formatting cover the mechanics. Rules built on volatile functions, like highlighting dates relative to TODAY(), are double trouble; keep those ranges tight.
4. Tame the Pivot Cache
Every pivot table carries its own copy of the source data — the pivot cache — stored inside the file. Five pivots built separately from the same 100,000-row source means six copies of that data in your workbook. Three things to do:
- Share one cache. When you need another pivot from the same source, copy an existing pivot table and re-arrange its fields instead of inserting a new one; the copy shares the original’s cache. (Our walkthrough on how to create a pivot table covers setup basics.)
- Turn off “Save source data with file” if the source lives in the same workbook: PivotTable Options > Data tab, uncheck it, and check “Refresh data when opening the file.” The cache is rebuilt on open instead of stored, often cutting file size dramatically.
- Clear old items. In the same Data tab, set “Number of items to retain per field” to None, then refresh. Deleted source rows otherwise haunt the cache and your filter dropdowns forever.
5. Switch to Manual Calculation Mode — Strategically
Formulas > Calculation Options > Manual stops Excel from recalculating after every edit. It is not a cure — the calculation is just as slow when it runs — but it puts you in control of when, which transforms the editing experience in a heavy model. The shortcuts:
| Shortcut | What it recalculates |
|---|---|
F9 | All open workbooks (changed cells only) |
Shift+F9 | Active sheet only |
Ctrl+Alt+F9 | Everything, forced, all open workbooks |
Ctrl+Alt+Shift+F9 | Rebuilds the dependency tree, then full recalc |
Two warnings. First, calculation mode is an application-level setting set by the first workbook opened in the session — open a manual-mode file first and every other file you open follows it, which is how people end up emailing reports full of stale numbers. Second, always run a full F9 before saving or sharing.
6. Save as .xlsb (Binary Format)
The standard .xlsx format is zipped XML — text that Excel must parse on open and serialize on save. The binary .xlsb format stores the same workbook (formulas, formatting, pivots, and VBA included) in an encoding that opens and saves noticeably faster and typically lands far smaller on disk. For a formula-heavy 40 MB file, this one change can take save times from twenty seconds to five.
File > Save As > “Excel Binary Workbook (*.xlsb)”. Trade-offs: the contents are not readable by generic XML tools, and an .xlsb can contain macros without the filename warning you the way .xlsm does. For more size tactics, see how to reduce the size of an Excel file.
7. Remove Unused Cell Styles
Workbooks that have absorbed years of copy-paste from other files accumulate thousands of orphaned cell styles — open Home > Cell Styles and if the gallery scrolls forever with names like “Normal 2 2 3,” you have this problem. Excess styles bloat the file, slow opening, and at the format limit (around 64,000 unique cell formats) trigger the dreaded “Too many different cell formats” error.
You cannot delete them efficiently by hand. Options:
- Run Microsoft’s Inquire add-in (File > Options > Add-ins > COM Add-ins > Inquire, available in some Office editions), which includes a Clean Excess Cell Formatting command.
- Copy just the data sheets into a brand-new blank workbook using Paste Special > Values, then re-apply formatting fresh. Drastic, but for a badly corrupted style table it is often the fastest route.
While you are cleaning, delete blank rows inside data ranges too — they break Tables and force the full-column workarounds covered in fix 2.
8. Upgrade Hardware and Use 64-bit Excel
Everything above fixes the workbook. If a genuinely large model is still slow afterward, look at the environment:
- 64-bit Excel. 32-bit Excel caps usable memory at roughly 2 GB regardless of installed RAM. 64-bit Excel (the default in current Microsoft 365 installs — check File > Account > About Excel) uses all available RAM, which matters for big pivot caches, Power Query, and the Data Model.
- RAM and cores. Excel’s multithreaded calculation engine scales across cores; 16 GB of RAM is a sensible floor for heavy work. An SSD speeds up open/save, not calculation.
- Add-ins. COM add-ins hook events and can slow every edit. Disable them one at a time (File > Options > Add-ins) to test.
Hardware is last deliberately: a workbook with fragmented conditional formatting and 50,000 OFFSET calls will be slow on any machine money can buy.
Quick Reference: Which Fix for Which Symptom
| Symptom | Start with |
|---|---|
| Slow to open and save, huge file size | Fixes 6, 7, used-range cleanup |
| Freezes while typing or scrolling | Fixes 1, 3, 5 |
| ”Calculating…” for many seconds after edits | Fixes 1, 2, 5 |
| Slow only after refreshing data | Fix 4, external links check |
How do I find which sheet is making my workbook slow?
Use Shift+F9 (calculate active sheet only) on each sheet in turn and time it. The sheet that takes seconds while the others are instant is your culprit — inspect it for volatile functions, full-column formulas, and the rule count in Manage Rules.
Does deleting rows actually shrink the used range?
Deleting entire rows or columns marks the range for cleanup, but Excel only resets the used range when you save the file (in older versions, save and reopen). If Ctrl+End still jumps too far after that, check for stray formatting or comments in the far cells.
Is .xlsb safe to use for shared files?
Yes — anyone with desktop Excel 2007 or later can open it, and formulas, pivots, and macros all survive. The caveats: Excel for the web has historically had limited .xlsb support, some third-party tools that read .xlsx cannot parse it, and macros inside an .xlsb are not flagged by the extension the way .xlsm makes them visible.
Why is my workbook slow even in manual calculation mode?
Three usual reasons: volatile functions combined with INDIRECT (which defeats Excel’s dependency tracking), heavy conditional formatting (which evaluates on screen redraws, not just on calculation), and event-driven VBA or add-ins running code on every selection change. Manual mode only controls formula recalculation — it does nothing for the other two.
