How to speed up a slow Excel workbook (without changing a single result)
Three formula patterns behind most slow workbooks, SUMPRODUCT over whole columns, volatile OFFSET and repeated VLOOKUPs, and what to replace each with. A worked example that reads 500 times fewer cells.
A workbook that takes seconds to respond after every edit usually isn't too big. It's doing far more work than it needs to, because of a few formula patterns that read millions of cells or recalculate when nothing they depend on has changed. This post walks through one slow workbook, finds the three patterns, and replaces them with formulas that give exactly the same values.
The workbook
An order report: 2,000 orders on an Orders sheet, a 16-row Products table, and a Summary of revenue by region (4 rows) and month (9 columns). It has 10,036 formulas, and it's slow.
Step 1: Find out what's slow
Before changing anything, count what each kind of formula reads. In GPTExcel, attach the workbook and ask "This workbook is really slow. What's making it slow?" It scans every formula and finds three problems:
| Problem | Where | Why it's slow |
|---|---|---|
| SUMPRODUCT over a million rows | Summary, 36 cells | Each one multiplies four arrays of 1,048,575 rows: about 151 million cells per recalculation, for 2,000 orders |
| OFFSET running total | Orders column J, 2,000 cells | OFFSET is volatile, so it recalculates after every edit anywhere, and row 2,001 re-adds all 2,000 rows above it |
| Four VLOOKUPs per row | Orders columns F to I, 8,000 lookups | The same product is searched for four times in each row |
In all, a full recalculation reads about 153 million cells.
Step 2: Replace each pattern
SUMPRODUCT over whole columns → SUMIFS
The Summary cells looked like this, with every range running to the last row of the sheet:
=SUMPRODUCT((Orders!$D$2:$D$1048576=$A2)*(Orders!$A$2:$A$1048576>=B$1)*(Orders!$A$2:$A$1048576<EDATE(B$1,1))*Orders!$H$2:$H$1048576)SUMIFS does the same conditional total, and Excel only reads the rows that hold data, even when you give it
whole columns:
=SUMIFS(Orders!$H:$H, Orders!$D:$D, $A2, Orders!$A:$A, ">="&B$1, Orders!$A:$A, "<"&EDATE(B$1,1))A volatile OFFSET running total → add the row above
=SUM(OFFSET($H$2,0,0,ROW()-1,1))Each row sums every row above it again, and OFFSET makes Excel redo all of it after any edit. A running total
only needs the previous total plus this row:
J2: =H2
J3: =J2+H3 (filled down)Four VLOOKUPs per row → one MATCH, then INDEX
=VLOOKUP(C2,Products!A:E,2,FALSE)
=VLOOKUP(C2,Products!A:E,4,FALSE)
=E2*VLOOKUP(C2,Products!A:E,4,FALSE)
=E2*VLOOKUP(C2,Products!A:E,5,FALSE)Find the product's row once, in a helper column, and reuse it:
K2: =MATCH(C2,Products!$A$2:$A$17,0)
F2: =INDEX(Products!$B$2:$B$17,K2)
G2: =INDEX(Products!$D$2:$D$17,K2)
H2: =E2*G2
I2: =E2*INDEX(Products!$E$2:$E$17,K2)Searching is the expensive part of a lookup; INDEX with a known row number is almost free.
Step 3: Prove nothing changed
A faster workbook that gives different numbers is worse than a slow one, so check two things before you use it:
- It recalculates with no errors. GPTExcel's Formula validator recalculated the new workbook: 12,036
formulas (the 2,000 extra are the helper
MATCHes), no errors. - Every value matches the original. Comparing all 20,060 calculated cells on Orders and Summary, the two workbooks are identical.
| Before | After |
|---|---|
| 36 SUMPRODUCTs over 1,048,575 rows | SUMIFS, which only reads the rows you use |
2,000 volatile OFFSETs | =J2+H3: each row adds one number to the row above |
| 8,000 VLOOKUPs | 2,000 MATCHes in a helper column, then INDEX |
| About 153 million cells read | About 292 thousand cells read |
That's around 500 times fewer cells per full recalculation, and nothing recalculates on every edit any more.
Other patterns worth checking
- Other volatile functions:
INDIRECT,TODAY,NOW,RANDandOFFSETall recalculate after every edit. OneTODAY()in a cell that everything else refers to is fine; thousands of them aren't. - Long nested IFs aren't slow, but they're hard to maintain.
=IF(D2="North",5%,IF(D2="South",6%,...))is clearer asSWITCH, or better, as anXLOOKUPinto a small rates table you can edit without touching the formula. - Whole-column array formulas in
FILTER,SUMPRODUCTor array-entered formulas. Point them at a table or a range that ends where the data does.
Try it on your workbook
Attach the slow file to GPTExcel and ask what's making it slow, then "Fix them without changing any results". It hands back a new copy, so your original stays as it was. See the Formulas guide for more, or XLOOKUP vs VLOOKUP vs INDEX/MATCH for choosing a lookup.