Skip to content
Tutorials Center
My learning

Topics

Lesson 90 of 93 ยท Practice and Review

Performance and Spreadsheet Best Practices

Make Excel workbooks fast and trustworthy: volatile functions, recalculation cost, lookup speed, input-calculation-output structure, checks and the FAST standard.

A spreadsheet that works with 50 rows can crawl with 50,000, and a spreadsheet that gives the right answer today can give a wrong one next year when someone edits it. Both problems have the same cure: build workbooks on purpose. This lesson covers two things. First, performance: what makes Excel recalculate slowly and how to avoid it. Second, structure: how to lay out a model so that other people (and you in six months) can trust and change it.

Excel cannot run on this site, so Excel features are described in text and plain blocks. Three small JavaScript TryIts are stand-ins that make the ideas visible: one counts recalculations in a dependency graph, one is a formula "linter" that flags risky patterns, and one counts the comparisons that different lookup strategies need. They model the ideas; they are not Excel's actual engine, so they show relative cost, not real timings.

Fast and trustworthy

Performance Recalculate only what changed.
Structure Inputs, calculations, outputs.
Safety Checks and no hardcoding.
Documentation Say what it does.
1 / 2

How Excel recalculates

Every formula depends on other cells. Excel remembers these links as a dependency chain. When you change a cell, Excel marks the formulas that depend on it as "dirty" and recalculates only those, in a sensible order. This is called smart recalculation, and it is why editing one number in a big workbook is usually instant.

Two things break that efficiency:

  • Volatile functions are recalculated on every recalculation, no matter what changed, and so is everything that depends on them. NOW, TODAY, RAND, RANDBETWEEN, RANDARRAY, OFFSET and INDIRECT are volatile (some uses of CELL and INFO are too).
  • Long chains. If a thousand formulas depend on one cell, an edit to that cell recalculates all thousand.

You can control when recalculation happens: Formulas, Calculation Options, Automatic, Automatic except for data tables, or Manual. In manual mode F9 recalculates everything that changed, and Shift+F9 only the active sheet. Manual mode is a valid tool for large models, but then the status bar must say "Calculate" when numbers may be stale, and it is easy to forget. See auditing formulas for the calculation modes.

Run it: counting recalculations

The stand-in below builds a graph of 1,000 inputs, each with one calculated cell, plus a grand total. It then "edits" one input and counts how many formulas are recalculated. In the second model, one cell holds the volatile NOW, and 500 cells depend on it (think of an "age in days" column). Every edit recalculates the volatile cell and its dependents too.

JS Smart recalculation and volatile functions

Press Run (or Ctrl + Enter) to see the console output here.

No output.

In model A, an edit costs 2 recalculations out of 1,001 formulas. In model B the same edit costs 503, because the volatile cell and everything below it are always dirty. With a few hundred cells that is invisible; with hundreds of thousands, or when the volatile cell sits at the root of a big model, it is the reason a workbook feels slow after every keystroke.

Practical speed advice

  • Avoid volatile functions in large models. OFFSET and INDIRECT are the main offenders because they are easy to replace: use INDEX for a moving endpoint (A1:INDEX(A:A,n)), tables for ranges that grow, and a normal reference instead of INDIRECT where possible (OFFSET and INDIRECT). If you need today's date, put =TODAY() in one labeled cell and refer to it, instead of calling it in thousands of cells.
  • Keep ranges as large as the data, not larger. Excel limits many functions to the used part of a column, so SUM(A:A) is often cheaper than it looks. But whole-column references still hide intent, can be slow inside array formulas, SUMPRODUCT and conditional formatting rules, and break when something unrelated is typed lower in the column. An Excel table gives you a range that fits exactly and grows by itself.
  • Do lookups once. If three columns need the same row of a lookup table, find the row number once with MATCH in a helper column and use INDEX three times. Or return all the columns at once with XLOOKUP (XLOOKUP).
  • Use cheaper search methods for big sorted data. An exact-match lookup scans row by row. An approximate match, or XLOOKUP with binary search mode, halves the search area at each step but needs sorted data.
  • Reduce the footprint. Delete unused formatted rows and columns (Ctrl+End shows where Excel thinks your data ends), avoid thousands of conditional formatting rules, and convert finished calculations to values when they will never change. The binary .xlsb format can open and save large files faster (saving and sharing).
  • Measure, do not guess. Switch to manual calculation, press F9 and watch how long it takes after each suspicious change.

Run it: how lookups scale

This stand-in counts the comparisons needed to do 1,000 lookups in a sorted list of 10,000 keys by scanning (like an exact-match lookup) and by binary search (like an approximate match or a binary-mode XLOOKUP). Counting comparisons says nothing about milliseconds, but it shows why the second approach scales.

JS Linear scan versus binary search

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Structure: inputs, calculations, outputs

Fast is useless if wrong. The most reliable habit is to separate a workbook into three zones:

Zone Contents Rules
Inputs Assumptions and raw data typed by people One place, labeled, with units; validated (data validation)
Calculations Formulas that turn inputs into results No typed numbers; consistent formulas across a row or column
Outputs Summary tables, charts, dashboards Only formulas that read from the calculations

Zones can be sheets (Inputs, Calc, Report) or clearly marked areas on a single sheet. A common convention is to color input cells (for example light yellow or blue text) so that nobody types into a formula cell by mistake. Protect the calculation and output zones and unlock only the inputs (collaboration and protection).

Avoid hardcoding

A hardcoded number is a constant buried in a formula: =B2*1.2 for a tax rate, =C5/12 for months. Nobody can see where the 1.2 came from, and when the rate changes you must find every formula that uses it. Put the number in a labeled input cell, give it a name (TaxFactor) and write =B2*TaxFactor. The same goes for text criteria such as "East": refer to a cell.

Not every number is "magic". 0, 1, 2 and structural constants such as 12 months per year are usually fine. The question to ask is: could this value change, and would someone need to know about it?

Run it: a tiny formula linter

This stand-in scans formula text for patterns that make workbooks fragile: volatile functions, whole-column references, hardcoded numbers and deep IF nesting. Real tools do this with more care (Excel itself has "error checking" rules), but the idea is the same.

JS A formula linter

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Notice that =D2/12 passes because 12 months per year is a structural constant, and =B2*1.2 does not. A linter can only guess; you decide what is a real assumption.

Checks and error-prone patterns

Models fail quietly. Build in checks that shout:

  • Reconciliation cells. The sum of the parts must equal the whole: =IF(ROUND(SUM(B2:B10)-B11,2)=0,"OK","CHECK"). Put all checks in one visible place and surface a single "all checks pass" flag on the output page.
  • Consistent formulas. The formula should be the same all the way down a column (in R1C1 notation it would be literally identical). One different cell in the middle is a typical hidden bug; Excel's error checking flags "inconsistent formula".
  • Careful with these patterns:
    • overwritten formulas (a typed number in the middle of a calculated column);
    • hidden rows, columns or sheets that still feed totals;
    • mixed units (thousands in one column, units in another);
    • merged cells and blank rows inside data, which break sorting, filtering and tables;
    • circular references that were "fixed" by turning on iterative calculation;
    • links to other workbooks that move or disappear;
    • copy-pasted results that no longer update.
  • Do not hide real bugs. Wrapping everything in IFERROR(...,0) makes errors disappear instead of fixing them (handling errors in formulas).
  • Test with extreme values. Zero, negative numbers, a blank, an enormous number. If the sheet misbehaves on them, someone will find out the hard way.

Document the workbook

Add a first sheet called README or About with: the purpose, the author and date, the sources of the data, the list of assumptions, how to update it (for example "paste new data into Raw, then press Refresh"), and a short change log. Use cell comments or notes for the one or two formulas that need an explanation. A model that explains itself survives its author.

The FAST standard

FAST is a published set of spreadsheet modeling rules, widely used in financial modeling. Its name lists its goals: Flexible, Appropriate, Structured, Transparent. In plain words: a model should be easy to change, only as complex as needed, built with a consistent layout, and understandable by someone who did not build it. Its detailed rules (consistent column structure, one formula per row, separation of inputs and calculations, no hardcoded numbers in formulas, and so on) overlap strongly with the advice in this lesson. Read the original standard if you build models professionally; we only summarize its spirit here.

A pre-release checklist

  1. Inputs, calculations and outputs are separated and labeled.
  2. No hardcoded numbers or text criteria inside formulas.
  3. Formulas are consistent down every column; no overwritten cells.
  4. No unnecessary volatile functions; ranges fit the data (or use tables).
  5. Check cells exist and all pass.
  6. Inputs are validated; calculation cells are protected.
  7. A README explains purpose, sources and how to update.
  8. The file opens and recalculates in a reasonable time, and a colleague has tried to break it.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why is a volatile function like OFFSET a performance problem in a large model?

    Volatile functions are always marked dirty, so their dependents are recalculated every time, even if none of their inputs changed.
  2. 2. Where should a tax rate used in many formulas be stored?

    One labeled input cell can be seen, changed and checked. Hardcoding the number hides it and makes updates error-prone.
  3. 3. What is the main reason to separate inputs, calculations and outputs?

    Separation makes the model understandable and safer to edit; it is about trust and maintenance more than speed.
  4. 4. What does wrapping every formula in IFERROR(...,0) usually do?

    An error is information. Replacing it with 0 hides bugs. Handle specific, expected errors and let unexpected ones show.
  5. 5. What do the letters FAST stand for?

    FAST is the Flexible, Appropriate, Structured, Transparent standard for spreadsheet modeling.

Summary

  • Excel recalculates only dirty formulas, but volatile functions (NOW, TODAY, RAND, OFFSET, INDIRECT) are always dirty, along with their dependents.
  • Keep ranges as large as the data (tables help), do lookups once, and use binary search only on sorted data.
  • Separate inputs, calculations and outputs; protect what people should not edit.
  • Replace hardcoded numbers with labeled input cells; add checks that shout; keep formulas consistent.
  • Document the model on a README sheet; the FAST standard summarizes these goals.
  • The JavaScript TryIts are stand-ins that show relative costs and patterns, not Excel's actual engine.

Next: Keyboard Shortcuts and Productivity Tricks.