Why Excel Is Slow With Formulas Over Many Rows Explained

A workbook that was fast at ten thousand rows takes a minute to respond at two hundred thousand. Every edit makes the status bar show "Calculating". People blame Excel, the laptop, the antivirus. The cause is usually in the formulas: how many there are, what each one does, and which ones refuse to be skipped. This post explains how Excel decides what to recalculate, the patterns that make it do too much, how to find the expensive cells and what to change. It is a spoke of Excel files that are too big or too slow.

How Excel decides what to calculate

Excel builds a dependency tree. Each formula knows which cells it reads (its precedents) and which cells read it (its dependents). When you change a cell, Excel marks the cell and its dependents as dirty, and recalculates the dirty ones in dependency order. Cells that do not depend on the change are skipped. Smart recalculation of this kind is why a big workbook can still respond quickly to a small edit.

Excel also uses multiple processor threads for independent branches of the chain, so a workbook of many independent formulas calculates faster on a machine with more cores. A long chain where each cell depends on the previous one (a running balance down a column) limits the parallelism.

Slowness comes from two sources: too many formulas marked dirty at once, and formulas that are expensive each time they run. Both are visible in the workbook once you know what to look for.

Volatile functions

Some functions are volatile: Excel recalculates them on every calculation, regardless of what changed, and so it recalculates everything that depends on them. The main ones are:

A single TODAY() in a cell is cheap on its own. The cost is the chain: if 100,000 cells depend on the cell holding TODAY(), they all recalculate at every change. OFFSET and INDIRECT are the usual offenders because they get used in thousands of rows to build moving ranges or to point at sheets by name. Replacements:

• **OFFSET for a moving window:** use INDEX, which is not volatile. SUM(A2:INDEX(A:A, n)) does what SUM(OFFSET(A2,0,0,n,1)) does without the volatility.

• **INDIRECT for a sheet name:** restructure so that the references are direct, or use one table with a column for the sheet name.

• **TODAY in many formulas:** put it in one cell, and refer to that cell. It still recalculates, but one formula instead of ten thousand does the volatile work, and the dependents are fewer if you can limit them.

Conditional formatting and data validation formulas using volatile functions recalculate with the display, and make scrolling slow.

Lookups that scan

VLOOKUP(key, table, col, FALSE) with an exact match searches the first column of the table from the top until it finds the key. On average it examines half the rows. A column of 200,000 lookups into a table of 200,000 rows performs around twenty billion comparisons. Each lookup is quick, but the number multiplies.

What helps:

• Use a faster match. XLOOKUP and INDEX/MATCH do not scan faster by themselves for exact match on unsorted data, though recent versions of Excel build an index cache for repeated lookups against the same range, which makes many lookups much cheaper. Do not rely on this: the behavior depends on the version.

• Sort and use binary search. For an approximate match on sorted data, VLOOKUP(..., TRUE) or XLOOKUP with a binary search mode (2 or -2) runs in logarithmic time. Only correct if the data is sorted and an approximate match is what you mean.

• Look up once, not many times. If five columns each do a VLOOKUP on the same key, use one MATCH in a helper column to find the row, then INDEX five times.

• Do the join elsewhere. A join in a database or Power Query is built for this. Loading both tables into the Data Model and relating them avoids per-cell lookups entirely (VLOOKUP and XLOOKUP versus SQL JOIN).

Whole column references and ranges

A formula such as =SUMIFS(C:C, A:A, "x") refers to a million-row column. Excel limits most functions to the used range, so modern versions treat it as cheap. The cost appears in some contexts: array formulas, SUMPRODUCT and functions that cannot trim the range will evaluate all million rows. A SUMPRODUCT over three whole columns is a million multiplications per call, and a column of those is a disaster.

Safer habits: use Tables and structured references (Sales[Amount]), which cover exactly the data, or bounded ranges. Avoid SUMPRODUCT over whole columns; use SUMIFS, which is optimized. Count the conditional formats too: a rule applied to A:Z is evaluated for every visible cell, and copying cells repeatedly can leave thousands of fragmented rules. Manage them under Conditional Formatting, Manage Rules, set to This Worksheet.

Array formulas and dynamic arrays

Legacy array formulas ({=SUM(IF(...))}) compute an array for each cell where entered, and a formula array-entered down 100,000 rows repeats the whole work 100,000 times. Dynamic array functions (FILTER, UNIQUE, SORT) spill once and are efficient when used once, but calling one per row has the same problem. Compute a result once, in one cell, and reference it. If you need grouped results, a PivotTable or Power Query does it in a single pass.

Chains and dependencies that spread

A change to one cell can dirty a very large subtree. An input at the top of a model that feeds every row of every sheet makes every edit recalculate everything. This is by design, but when it is accidental it is expensive. Look at who depends on what: Formulas, Trace Dependents shows arrows; in a large model, a dependency map from a tool helps. Isolate inputs, so a change to an assumption recalculates only what needs it, and cut cross-sheet links that exist only for convenience.

Formulas that reference other workbooks are slower and fragile. Excel must open or read the linked file. Bring the data in with a query and refresh it deliberately.

Find the expensive parts

Do not guess. Excel offers several ways to measure.

1. Calculation options. Set calculation to Manual (Formulas, Calculation Options) and press F9 to see how long a full calculation takes, shown in the status bar. Ctrl+Alt+F9 forces a full recalculation including all formulas.

2. Divide. Copy the workbook, delete half of the sheets or convert sections to values, and see how much faster it gets. The section whose removal helps most holds the cost.

3. Count formulas. Home, Find & Select, Go To Special, Formulas shows how many there are on a sheet. Millions of formulas are millions of things to track.

4. Look for volatile functions. Search the workbook (Find, within Formulas) for OFFSET, INDIRECT, TODAY, NOW, RAND.

5. Use a profiling add-in or macro. VBA can time the calculation of each sheet or range with Range.Calculate. Microsoft's performance documentation shows a pattern for it.

Keep the measurements in a note: before, after each change, on the same machine.

What to change

In rough order of payoff:

1. Calculate manually while editing. Switch to Manual, edit, then F9. This is a workaround, not a cure, and it is free.

2. Remove volatility. Replace OFFSET and INDIRECT; reduce TODAY to one cell.

3. Replace repeated lookups with one match column or a join.

4. Convert finished results to values. A column of formulas whose inputs will never change is dead weight. Paste values and keep the logic documented elsewhere.

5. Bound ranges and use Tables.

6. Replace per-row array work with a pivot, a query or a single spilled formula.

7. Move the heavy work out. A database or a script does set-based work without a per-cell dependency chain (translating Excel formulas into SQL and why SQL beats formulas once the data gets large).

8. Use 64-bit Excel for large workbooks, and enough memory.

Item 4 deserves a caution: pasting values removes the audit trail. Keep a copy with formulas, or write down the rule.

Other causes of a slow workbook

Formulas are the usual suspect and not the only one. A file with hundreds of thousands of fragmented conditional formats, many embedded objects, a huge used range, hundreds of defined names, an add-in that hooks every calculation, or a pivot cache for each of many pivots can be slow with few formulas. Antivirus scanning and file location (a network drive or a synced folder) slow saving and opening. Check these when the calculation time is small and the workbook is still sluggish (reducing workbook size by removing unused styles).

When formulas are not the cause of the slowness

The data is too big for a sheet. Hundreds of thousands of rows with formulas on each is the wrong tool; use a database or the Data Model.

The slowness is on open or save. That points to file size, links or add-ins, not calculation.

Scrolling is slow. Conditional formatting, many shapes or a graphics driver problem.

It is one machine. If a colleague's computer is fast, check memory, bitness and add-ins on yours.

Microsoft documents the engine in Excel performance: improving calculation performance. For larger data, see using the Excel Data Model for larger datasets. The Excel team also describes how multithreaded calculation works. To convert a small extract to JSON, use the CSV to JSON converter.