Expert Excel · Interactive lesson 10 of 12
Performance & scalability
A11 caught volatile functions and whole-column references. At real scale, the risks get bigger: a data model with too many rows, a query that re-reads a huge file every refresh, and sometimes — a workbook trying to be a database that outgrew Excel entirely.
Screens shown: Excel for Microsoft 365 for Windows. Practice works in any browser.
Written tutorialExcel Performance at ScaleSkill check
Try these first:
- Trace which formulas sit early versus late in the calculation chain.
- Explain what makes an array formula expensive at scale, specifically.
- Diagnose a slow Power Query refresh and a bloated Data Model.
- Recognize when a "workbook" has actually outgrown Excel.
All familiar? Skip ahead to E11 — Security & Deployment.
Concept teach
What you're learning
- Calculation chain awareness
- Excel recalculates in dependency order — a slow formula early in that chain delays everything downstream of it, even cells that look unrelated.
- Volatile functions
- Still the fastest way to make an entire workbook recalculate on every keystroke, no matter how small the actual change.
- Array formula cost
- A dynamic array spilled across a huge range recalculates the whole array on any dependency change — powerful, but not free at scale.
- Power Query refresh time
- A query that re-reads a multi-gigabyte source file over a network share on every refresh is slow by design, not bad luck — cache, filter earlier, or stage locally.
- Data Model size
- Millions of rows across many related tables with dozens of measures adds real memory pressure — trim unused columns before importing, not after.
- Formatting bloat
- Styling applied to entire rows or columns "to be safe" bloats file size and slows every recalculation, whether or not those cells hold data.
- Manual vs. automatic calculation
- Switch to Manual (F9 to trigger) while mid-edit on a large model, and remember to switch back before handing it off.
- When Excel is no longer the right tool
- Dozens of linked files simulating a relational database, nightly manual reconciliation across them, or a "workbook" that's really trying to be a data warehouse — that's a sign to move to an actual database, not a bigger spreadsheet.
Where to find it in Excel
Data›Queries & Connections›Status bar
| A | B | C | D | E | F | ||
|---|---|---|---|---|---|---|---|
| 1 | Query | Last refresh | Duration | ||||
| 2 | Sales | 10:02 | 48 s | ||||
| 3 | stg_Sales | 10:01 | 35 s | ||||
| 4 | Targets | 10:01 | 2 s | ||||
| 5 | |||||||
| 6 | |||||||
| 7 | |||||||
| 8 |
Swipe sideways to see the whole window.
- 1Refresh All; time each refresh and log it.
- 2Watch row counts: past about a million rows, load to the Data Model or move the job out of the grid.
- 3The slowest steps are the ones to fix first.
- 4Formulas › Calculation Options › Manual while you restructure a slow model.
Rendered view of Excel for Microsoft 365 (Windows). Ribbon layout can differ slightly on Mac, Excel for the web or a narrow window.
Guided build
Seven real risk signs from a slow enterprise workbook. Flag every one, then check your answers.
Independent extension
Diagnose a slow workbook
Diagnose your slowest workbook or model and list every risk by impact and effort to fix — volatile functions, array formula cost, refresh time, formatting bloat, model size. Fix the two highest-impact, lowest-effort ones first, and note whether anything on the list is actually a sign Excel isn't the right tool anymore.
Spot a mistake or have an idea for this lesson? Send feedback