Excel running slow: how to find the cause and fix it

1. Narrow it down: one file, or all of Excel?

Open a new blank workbook and type into it. If that's fast, the problem is inside the slow file, and the sections below will find it. If even a blank workbook lags, the cause is Excel itself, usually an add-in, and section 7 covers it.

Then watch the status bar while you edit the slow file. If it shows Calculating with a percentage after each change, the formulas are the cost. If editing is fine but scrolling stutters, look at formatting, conditional formatting and objects. If only opening and saving are slow, look at file size, links to other workbooks and pictures.

2. Volatile functions recalculate everything, every time

Most formulas recalculate only when a cell they depend on changes. Volatile functions recalculate after every edit anywhere in the workbook: NOW, TODAY, RAND, RANDBETWEEN, OFFSET, INDIRECT, and CELL and INFO in most uses. Every formula that depends on them recalculates too, so one OFFSET at the top of a chain can drag thousands of cells along with it.

The usual offender is a dynamic range like OFFSET(D2,0,0,COUNTA(D:D)-1) feeding SUMs, charts and drop-downs. Replace it with an Excel table (Ctrl+T): references like Orders[Amount] grow with the data and aren't volatile. INDIRECT used to point at a sheet named in a cell is harder to replace, but if it's in a few thousand formulas, consider whether a single lookup table with a Sheet column would do the job.

TODAY in one cell referenced by the others means one volatile function instead of thousands, which is a real saving, but the cells that use it still recalculate on every edit. If an ageing report only needs today's date once a day, type the date with Ctrl+; instead.

3. Use SUMIFS, not SUMPRODUCT, over long ranges

SUMPRODUCT and array formulas calculate every cell you give them. Point one at whole columns, like SUMPRODUCT((C:C="North")*D:D), and Excel works through 1,048,576 rows for each formula, empty or not. A few hundred of those make every edit crawl.

SUMIFS, COUNTIFS and AVERAGEIFS give the same totals for simple conditions and are built to skip the empty part of a column, so they're far cheaper. Keep SUMPRODUCT for jobs SUMIFS can't do, and give it exact ranges like D2:D9, or a table column.

4. Do a lookup once, then reuse the position

A report that pulls ten columns for each order with ten separate XLOOKUPs or VLOOKUPs searches the order list ten times per row. Find the row once with MATCH in a helper column, then use INDEX for each column you need: INDEX(C2:C9,G2), INDEX(D2:D9,G2) and so on, where G2 holds the MATCH. Each INDEX is nearly free.

Running counts like COUNTIF(A$2:A2,A2) filled down a 50,000-row list are another quiet cost: each row checks every row above it, so the work grows with the square of the list. A pivot table or a single COUNTIFS on the whole range is usually what you actually wanted.

5. Clean up conditional formatting, shapes and the used range

Conditional formatting rules are re-checked as you scroll and edit. Copying and inserting rows splits rules into fragments, and a sheet that has been used for a year can have hundreds of near-identical rules. Go to Home > Conditional Formatting > Manage Rules, set Show formatting rules for to This Worksheet, delete the duplicates and keep one rule per job over the whole range.

Data pasted from web pages often brings invisible shapes and pictures with it, sometimes thousands. Home > Find & Select > Go To Special > Objects selects them all; press Delete if none are needed. Then press Ctrl+End: if it lands far below your data, delete the empty rows and columns past the data and save, which resets the used range.

In Excel for Microsoft 365 and Excel for the web, Review > Check Performance (if your version shows it) finds formatting on empty cells and clears it for you.

6. Switch to Manual calculation while you build

When a big model is slow by nature, stop it recalculating after every keystroke while you edit, then calculate when you're ready. Manual mode applies to every open workbook, and a workbook saved in Manual can switch Excel to Manual when it's the first file you open, so switch back when you're done.

1. Go to Formulas > Calculation Options and choose Manual.
2. Edit as usual. When results are out of date, the status bar shows Calculate.
3. Press F9 to calculate all open workbooks, or Shift+F9 for the active sheet only.
4. If results still look wrong after F9, press Ctrl+Alt+F9 to force a full recalculation of every formula.
5. Set Formulas > Calculation Options back to Automatic before you share the file, so nobody reads stale numbers.

7. If all of Excel is slow: add-ins and settings

Hold Ctrl while you start Excel and answer Yes to open it in safe mode, which loads no add-ins. If Excel is fast there, an add-in is the cause. Go to File > Options > Add-ins, choose COM Add-ins in the Manage box, click Go, untick them all, then turn them back on one at a time until the slowdown returns.

Also check File > Options > Advanced, under Formulas, that Enable multi-threaded calculation is ticked so Excel uses all the processor cores. And close other heavy workbooks: everything open shares the same calculation queue.

8. Fixes that rarely help

Splitting one big workbook into several linked ones usually makes things slower, because links have to be refreshed and the files open more slowly. Rewriting SUMIFS with whole-column references into exact ranges seldom matters, since SUMIFS stops at the used range anyway; the whole-column cost is in SUMPRODUCT and array formulas. And reinstalling Office won't fix a workbook that's slow because of its formulas. Measure first: if Calculating shows after edits, fix formulas; if it doesn't, fix formatting, objects and add-ins.