Why Your Excel File Is So Slow, and How to Fix It

By the XLsheetAI Team · Updated August 15, 2026 · 12 min read

The one-sentence answer

In most slow workbooks the culprit is a volatile function — NOW, TODAY, RAND, OFFSET, INDIRECT or CELL — sitting somewhere upstream, because a volatile function recalculates every time anything in the workbook changes and forces every formula that depends on it to recalculate too, so a single timestamp cell can turn one keystroke into a full-workbook recalculation.

The rest of this article walks the causes in the order they actually bite, from the ones that explain most slow files to the ones you only reach for after the obvious checks come back clean. Work down the list rather than trying everything at once, because the first two or three fixes usually settle it.

1. Volatile functions recalculating everything

Excel is normally smart about recalculation. It builds a dependency tree, marks only the cells affected by your edit as dirty, and recomputes those. Change a price in one cell and only the formulas that read that cell — and the formulas that read those — do any work.

Volatile functions opt out of that optimisation. They declare themselves dirty on every single recalculation, whether or not their inputs changed. Everything downstream of them is therefore dirty too. The dependency tree stops being a filter and becomes a fan-out.

The volatile functions worth knowing:

• NOW() and TODAY() — the classic accident. Someone puts "Last updated" in a header cell, and now the workbook recalculates on every edit forever.
• RAND(), RANDBETWEEN() and RANDARRAY() — volatile by definition, since a new number each time is the point.
• OFFSET() and INDIRECT() — the expensive ones, because they are usually written once per row in a large table.
• CELL() called without a reference argument, and INFO().

The fix. Search for them: Ctrl+F, tick Formulas under Look in, and search for NOW(, OFFSET(, INDIRECT( in turn. Then replace what you can.

• A "last updated" stamp does not need to be live. Type the date, or set it with Ctrl+; which inserts today's date as a static value.
• OFFSET used to build a dynamic range can nearly always become an Excel Table (Ctrl+T), whose ranges expand on their own without any formula at all.
• INDIRECT used to reference a sheet name chosen by a dropdown is harder to remove, but it is often replaceable with CHOOSE over a fixed list of ranges, which is not volatile.
• OFFSET used for a "last N rows" window is usually replaceable with INDEX, which is not volatile.

2. Whole-column references inside formulas

Writing =SUMIF(A:A, "North", B:B) is convenient, and on a small sheet it costs nothing you would notice. Copy it down a few thousand rows, or combine it with several other whole-column formulas, and each one is asking Excel to consider over a million rows of mostly empty cells.

Excel does optimise this — it will not literally scan a million blank cells if it knows the used range ends at row 8,000 — but the optimisation is imperfect, it interacts badly with an inflated used range (see cause 4), and it stops helping entirely once the reference is nested inside array logic or a lookup chain.

The fix. Reference the rows that exist. Best of all, convert the data to an Excel Table with Ctrl+T and use structured references such as =SUMIF(Sales[Region], "North", Sales[Amount]). The Table grows and shrinks with the data, so the reference stays exact without maintenance, and you never have to remember to extend a range after pasting new rows.

Where a Table is not practical, a generously sized but bounded range — A2:A20000 for a sheet that will never pass 20,000 rows — is still far better than A:A.

3. Conditional formatting applied to entire columns

This one is under-diagnosed, and it is the cause most likely to make a file feel slow while scrolling rather than while calculating, because formatting rules are re-evaluated as cells are drawn.

Two things go wrong together. First, people apply rules to whole columns — select column D, add a colour scale — so the rule's applies-to range is $D:$D, more than a million cells for one rule. Second, and worse, conditional formatting rules multiply silently. Copying and pasting formatted rows, inserting rows in the middle of a formatted block, or deleting rows can split one rule into several, each covering a fragment of the original range. Do that repeatedly over months of maintenance and a workbook that logically has four rules can carry hundreds of near-identical ones, each with its own range to evaluate.

The fix. Go to Home → Conditional Formatting → Manage Rules, and in the Show formatting rules for dropdown choose This Worksheet rather than the default current selection. That is the only view that reveals the full list. If you see dozens of duplicated rules with fragmented ranges like $D$2:$D$14, $D$15:$D$15, $D$16:$D$400, delete them all and reapply a small number of clean rules over exact ranges.

Two habits prevent it coming back: apply rules to bounded ranges rather than whole columns, and use Paste Special → Values when pasting rows into a formatted block so you copy the numbers without dragging formatting rules along.

4. An inflated used range

Press Ctrl+End. Excel jumps to the bottom-right corner of what it believes is the used range. If your data ends at row 3,000 and Ctrl+End lands on row 61,482 — or column XFD — the workbook is carrying tens of millions of cells that Excel allocates, scrolls, saves and considers in every whole-column reference.

It happens through ordinary use: formatting applied to entire rows or columns, data pasted in and later deleted, a stray space typed far down the sheet and cleared. Deleting the contents does not shrink the used range, because Excel tracks the largest extent the sheet has ever had, not what is in it now.

The fix, per worksheet:

1. Click the row header of the first genuinely empty row below your data.
2. Press Ctrl+Shift+Down Arrow to select every row from there to the bottom of the sheet.
3. Right-click a selected row header and choose Delete — delete the rows, do not just press the Delete key, which only clears contents.
4. Repeat to the right: click the column header just past your last column, press Ctrl+Shift+Right Arrow, right-click and Delete.
5. Save, close and reopen the file. This step is not optional. The used range is only rewritten when the workbook is saved, so Ctrl+End will keep reporting the old corner until you have done a full save-and-reopen cycle.

Check Ctrl+End again afterwards. It should now land on the true last cell of your data.

5. Array formulas and long lookup chains

Two related patterns show up in workbooks that are slow specifically after an edit, with a visible Calculating: (n%) in the status bar.

The first is a large block of array-style formulas — SUMPRODUCT over big ranges, legacy Ctrl+Shift+Enter formulas, or dynamic array functions such as FILTER and SORT whose results feed other formulas. Each one evaluates across many cells, and a column of them multiplies that work by the number of rows.

The second is a dependency chain: a lookup on sheet A feeds a lookup on sheet B, which feeds a calculation on sheet C, which a pivot source reads. Excel cannot parallelise a chain — each link waits for the one before it — so a long chain recalculates slowly even when no individual formula is expensive.

The fix. Use Formulas → Formula Auditing → Trace Dependents on a suspect cell to see how far the ripple travels; the arrows make a long chain obvious immediately. Then shorten it. Replace repeated lookups of the same key with a single helper column that resolves the key once and is then referenced by everything else. Sort the lookup source and use binary search modes where the data allows it. And where a chain of intermediate calculations exists only to reshape data, do the reshaping in Power Query (Data → Get & Transform) instead, so it runs on refresh rather than on every recalculation.

If a workbook has grown this way over years, it is also worth reading our piece on costly spreadsheet errors — the same complexity that makes a file slow is what makes it wrong in ways nobody catches.

6. Unused defined names and links to closed workbooks

Open Formulas → Name Manager (Ctrl+F3). In a workbook that has been copied between files over the years, you will often find hundreds of defined names you never created, many showing #REF! as their value, and many pointing at workbooks on someone's old network drive.

Names that resolve to errors or to external files are checked when the workbook opens and when it recalculates. Links to closed workbooks are worse: Excel may try to reach the source file, and if that path is unavailable — a VPN that is down, a colleague's laptop — the delay is a network timeout, which is why some files hang for ten seconds on open and then behave normally.

The fix. In Name Manager, sort by Value, select every name showing #REF! and delete them in bulk. Then check external references: on the Data tab, open Edit Links (in current Microsoft 365 builds this sits in the Queries & Connections group and may be labelled Workbook Links). If the button is greyed out, the workbook has no external links, which is one suspect eliminated. If links are listed and you no longer need live data from them, use Break Link to convert them to static values.

7. Images and shapes accumulating invisibly

Objects are hard to spot because they can be tiny, transparent, stacked on top of each other, or sitting off to the side of the visible area. They arrive by accident: a paste from a web page brings hidden shapes, a copied row duplicates the object anchored to it, a Camera-tool snapshot gets copied a hundred times.

The symptom is a large file that is slow to open and slow to scroll, with no formula complexity to explain it.

The fix. Press Ctrl+G (or Home → Find & Select → Go To Special), choose Objects, and click OK. Excel selects every object on the sheet and the status bar reports how many. If you expected two logos and it selects 1,400, press Delete — but check first that you are not deleting form controls or charts you need. Home → Find & Select → Selection Pane lists objects individually if you need to inspect them before deleting.

While you are there: photographs pasted at full camera resolution are a separate size problem. Select a picture and use Picture Format → Compress Pictures, unticking Apply only to this picture to compress every image at once.

8. File format: .xlsb vs .xlsx

This is last for a reason. Changing format does not make a badly built workbook calculate faster — it changes how Excel reads and writes the file, not how it computes.

An .xlsx file is a zip archive of XML documents. Excel must unzip and parse that XML on open, and generate and rezip it on save. An .xlsb file stores the same workbook in a binary layout, so there is far less parsing work at both ends, and the file on disk is usually noticeably smaller for the same content.

The fix, and its cost. Use File → Save As and pick Excel Binary Workbook (*.xlsb). Opening and saving get quicker on large files; recalculation is unchanged. The trade-off is interoperability: plenty of external tools, data pipelines, browser previews and non-Excel spreadsheet applications read .xlsx but choke on .xlsb. Use it for the big internal model you work in daily, and keep .xlsx for anything you send outward or feed to another system.

The working fix while you edit: Manual calculation

Everything above is a repair. This is the anaesthetic that makes the repair bearable, and it is also the right setting for any heavy data-entry session.

Go to Formulas → Calculation Options → Manual. The same setting lives at File → Options → Formulas → Workbook Calculation if you prefer. Excel now stops recalculating after every entry, and typing becomes responsive again even in a workbook you have not fixed yet.

Refresh on demand with these keys:

Two warnings. First, whatever is on screen in Manual mode may be stale — press F9 before you read, screenshot or send any total. Excel shows Calculate in the status bar when results are pending, and it is easy to miss. Second, calculation mode is saved with the workbook and applies to whichever file Excel opens first in a session, so a file you left in Manual will land on a colleague's machine in Manual. Switch back to Automatic before sharing.

The diagnostic table

Not sure which formula is the expensive one? Paste it into XLsheetAI and get a plain-English explanation of what it actually scans — or describe what you need and get a version built on Tables and INDEX instead of OFFSET and whole-column references.

When the file is the wrong tool

Some workbooks are not slow because of a mistake. They are slow because they are being asked to be something else.

The signals are consistent. Rows keep arriving and never get archived. Several people need to edit at the same time and end up passing copies around with dates in the filenames. History matters, so nothing is ever deleted. The same numbers get re-aggregated into a dozen summary sheets that all have to stay in sync. At that point every fix in this article buys you a few months, and then you are back where you started with a bigger file.

Two exits. Move the storage into a database — Access, SQLite, Postgres, or whatever your organisation already runs — and keep Excel as the front end that queries it, so the sheet holds a result set rather than the entire history. Or move the reporting into Power BI, which loads data into a compressed columnar model built for exactly this volume and keeps the presentation layer separate from the calculation layer.

Neither is a small decision, and Excel remains the better choice for anything exploratory or one-off. Our comparison of Excel vs Power BI covers where the line sits in practice and what building a model actually costs you.

Bottom line

Work the list in order. Search for volatile functions, replace whole-column references with Tables, audit conditional formatting rules from the This Worksheet view, then press Ctrl+End and reset the used range if it lands somewhere it should not. Those four checks account for most slow workbooks. Switch to Manual calculation while you do the work, and remember to switch back before anyone else opens the file.