How often does Google Sheets auto update formulas?

Formula recalculation in Google Sheets is instantaneous on every cell edit by default, controlled by the "Automatic" setting under Settings → Calculation. For volatile functions - NOW, TODAY, RAND, INDIRECT, OFFSET - this means recalculation fires on every keystroke anywhere in the workbook. For non-volatile formulas, recalculation triggers only when the formula's direct inputs change. You can switch to manual recalculation mode (Shift+F9 to force a recalc) if volatile function lag is making a large model unusable during editing sessions.

Why is my Google Sheets model slow to recalculate?

Almost always one of 3 things: (1) volatile functions scattered across tabs - one NOW() in an Assumptions tab feeding 15 downstream tabs means every keystroke triggers recalculation of that entire tree; (2) whole-column references like `SUMIFS(C:C,...)` instead of bounded ranges like `SUMIFS($C$2:$C$2001,...)`, which forces Sheets to evaluate all 10 million cells in the column on every recalc pass; or (3) a non-linear dependency chain where downstream tabs reach back to non-adjacent upstream tabs, creating a graph Sheets has to resolve in multiple passes. Audit volatile functions first - they're the most common culprit and the easiest to fix.

Can I replace INDIRECT in a scenario toggle without rebuilding the model?

Yes. The standard replacement is CHOOSE with an integer scenario index (1=Base, 2=Upside, 3=Downside) or INDEX/MATCH with text labels if you want human-readable selectors. Both are non-volatile: they recalculate only when the scenario selection or the underlying scenario values change, not on every edit. The migration is mechanical - find each INDIRECT formula, determine what it resolves to under each scenario, and rewrite it as a CHOOSE or INDEX/MATCH with explicit cell references. Models with 200+ INDIRECT-based references typically drop from 12-15 seconds recalculation time to under 2 seconds after the migration.

What's the difference between onEdit and time-based triggers in Google Sheets?

An `onEdit` trigger fires synchronously when a user edits a cell. A time-based trigger runs on a schedule (minimum interval: 1 minute), independent of user activity. The critical operational difference: simple `onEdit` triggers can't make external network calls - UrlFetchApp requires an installable trigger. Time-based triggers can call external APIs but run in the background with no UI feedback and fail silently unless you build explicit error logging. As of July 2026, consumer accounts get 90 minutes of total daily trigger runtime; Workspace accounts get 6 hours. Each individual run caps at 6 minutes.

How do I stop Google Sheets from recalculating when I'm in the middle of editing?

Switch calculation mode to manual: Settings → Calculation → Manual. In manual mode, formulas only recalculate when you press Shift+F9 (current sheet) or Ctrl+Shift+F9 (all sheets). This is particularly useful when editing a large model where volatile functions are causing 3-8 second lag on every keystroke. The risk: it's easy to forget to recalculate before sharing the file, so add a visible "MANUAL CALC MODE - press Ctrl+Shift+F9 before sharing" note in your Assumptions tab header if you work this way regularly.

Auto Update Google Sheets: 3 Mechanisms Explained (2026)

Most of the lag analysts blame on Sheets is actually a formula architecture problem. Fix that first.

How Auto Update Works for Google Sheets Formulas

Every formula belongs to one of two categories: dependent (recalculates only when its direct inputs change) or volatile (recalculates on every single edit, anywhere in the workbook).

Dependent formulas are fast. =SUMIFS('P&L'!$C$2:$C$2001,'P&L'!$B$2:$B$2001,">="&Assumptions!$B$3) recalculates only when those specific cells change. The rest of the time, it costs nothing.

Volatile formulas are a tax on every keystroke. The main offenders:

NOW() and TODAY()
RAND() and RANDBETWEEN()
INDIRECT()
OFFSET()
CELL() and INFO()

One NOW() in your Assumptions tab - say, a timestamp showing when the model was last refreshed - means every edit anywhere triggers a full recalculation of that formula's entire dependency tree. If that cell feeds 15 downstream tabs, those 15 tabs recalculate on every keystroke. A 2,000-row model with 40 volatile functions can take 3-8 seconds to respond to a single cell edit.

The fix is a manually-updated helper cell:

Cell B2 (Assumptions tab): [type the date manually, e.g., 2026-07-07]
Cell B3: =TEXT(B2,"MMM DD, YYYY")   ← non-volatile, displays "Jul 07, 2026"

The timestamp updates when you type it. Not on every edit.

Auditing your volatile function count: There's no built-in counter in Sheets. The fastest approach is Ctrl+F on each tab searching for NOW, TODAY, RAND, INDIRECT, OFFSET. Note every tab they appear on, then trace which downstream tabs reference those cells. That's your recalculation surface area.

Formula Architecture: Keeping Auto Update Fast Across a Multi-Tab Google Sheets Model

Most recalculation lag in complex models isn't from the formula engine. It's from a poorly structured dependency graph. When Sheets figures out what to recalculate after an edit, it traces the chain. A linear chain is fast. A web is slow.

The 5-tab LBO dependency chain

A standard LBO model runs: Assumptions → Income Statement → Balance Sheet → Cash Flow → Returns Analysis. The most common mistake is letting tabs reach back over non-adjacent tabs. The Returns tab references both the Income Statement and the Assumptions tab directly for the same growth rate the Income Statement already computed. Cash Flow pulls from both Balance Sheet and Assumptions for a number Balance Sheet already derived.

Each shortcut adds a branch to Sheets' recalculation graph. More branches mean more nodes to evaluate, and the overhead compounds across tabs.

Clean structure: each tab pulls from exactly one upstream source for any value that was already computed upstream. The dependency graph should look like a river, not a web.

Concrete example. If your Returns tab needs Year 5 revenue, don't re-derive it from Assumptions:

← bad: re-deriving revenue that Income Statement already calculated
=Assumptions!$B$5 * (1+Assumptions!$B$6)^5

Pull it from where it was already computed:

← good: reference the result, not the inputs
='Income Statement'!$C$22

Obvious in theory, easy to break in practice when you're adding a new return metric at 11pm before a board meeting. Audit your Returns tab periodically: any formula that skips a tab in the chain is a cleanup candidate.

Cross-tab SUMIFS: whole-column references are the silent slowdown

The pattern that trips people up is conditional aggregations using entire-column references:

=SUMIFS('P&L'!C:C,'P&L'!B:B,">="&Assumptions!$B$3,'P&L'!D:D,Assumptions!$B$8)

This isn't volatile - it only recalculates when the referenced cells change. But C:C tells Sheets to evaluate all 10 million possible cells in that column on every recalculation pass. On a 2,000-row P&L this is pure overhead. Lock references to your actual data range:

=SUMIFS('P&L'!$C$2:$C$2001,'P&L'!$B$2:$B$2001,">="&Assumptions!$B$3,
         'P&L'!$D$2:$D$2001,Assumptions!$B$8)

On a $34.2M ARR model with 38.5% gross margin tracked across 8 cost categories, trimming whole-column references to bounded ranges cut Cash Flow tab recalculation time by roughly 60%. The formula logic is identical. The performance is not.

Replacing INDIRECT in scenario toggles

INDIRECT is the most common source of avoidable volatility in FP&A models. It's popular in scenario toggles because it feels elegant:

=INDIRECT(Assumptions!$B$1&"!Revenue")   ← B1 holds "Base", "Upside", or "Downside"

The problem: INDIRECT is volatile. Every edit anywhere in the workbook recalculates every INDIRECT formula, whether or not the scenario selection changed. In a model with 200 scenario references, this alone can add 10-15 seconds to every edit cycle.

Two clean replacements:

Option 1 - CHOOSE with an integer index

Store 1, 2, or 3 in B1 instead of a text label:

=CHOOSE(Assumptions!$B$1,
  Scenarios!$B$5,   ← Base
  Scenarios!$C$5,   ← Upside
  Scenarios!$D$5)   ← Downside

Non-volatile. Recalculates only when B1 or the scenario cells change. Works for models with 3-4 scenarios.

Option 2 - INDEX/MATCH with text labels

If you want to keep visible labels ("Base", "Upside", "Downside") in the selector cell:

=INDEX(Scenarios!$B$5:$D$5,
  MATCH(Assumptions!$B$1,Scenarios!$B$1:$D$1,0))

B1 holds the scenario name, B1:D1 holds the headers, B5:D5 holds the values. Same performance as CHOOSE, slightly more flexible when scenarios are added later.

Both eliminate the volatile function call while preserving identical scenario-switching behavior. Models with 200+ INDIRECT-based scenario references typically recalculate in under 2 seconds after the migration, down from 12-15.

Auto Update Mechanism Comparison for Google Sheets

Method	Trigger	Latency	Failure Mode	Best For
Formula recalculation	Cell edit	Instant (or slow if volatile)	Silent lag from volatile functions	In-model calculations
`onEdit` Apps Script trigger	Cell change	Seconds	Can't make external API calls	Simple in-sheet write-backs
Time-based trigger	Scheduled (min: 1 min)	Depends on script	Silent failure, no notification	Scheduled internal tasks
`IMPORTRANGE` / `IMPORTDATA`	~60 min auto, or manual	1-60 min	Stale silently on source changes	Cross-file references
External data add-on	On demand	Seconds	Auth expiry	Live data from HubSpot, Stripe, etc.

Setting Up Auto Update Triggers in Google Sheets (and What They Won't Save You From)

Apps Script triggers handle the cases formula recalculation can't: scheduled data pulls, writing to external APIs, logging changes to an audit tab. Setup is Extensions → Apps Script → Triggers → Add trigger → pick event type and function. As of July 2026, consumer accounts have a 90-minute daily script runtime quota; Workspace accounts get 6 hours. Each individual trigger run caps at 6 minutes.

The real problem with triggers isn't the setup. It's that they fail silently. A time-based trigger pulling exchange rates at 6am that hits a quota limit or a stale auth token doesn't tell anyone. Your model shows yesterday's rates. You find out when someone in the board meeting asks why the USD/EUR assumption looks off.

If you're using triggers for anything investor-facing, write the failure visibly:

function refreshRates() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet();
  try {
    // your fetch logic here
    sheet.getRange('Assumptions!B12').setValue(new Date()); // last-updated timestamp
  } catch(e) {
    sheet.getRange('Assumptions!B13').setValue('REFRESH ERROR: ' + e.message); // visible failure
  }
}

That's the extent of Apps Script most FP&A workflows actually need. Anything pulling from HubSpot, Stripe, or a data warehouse on a repeatable schedule is better handled by a tool that owns the auth and retry logic. Tools like ModelMonkey connect Sheets to those sources directly, manage the connection credentials, and let you refresh on demand with an audit trail of when each pull ran - which is the "silent failure" problem solved without a try-catch block.

Where Auto Update in Google Sheets Gets Complicated

The formula engine handles in-model calculations. Triggers handle simple scheduled tasks. Neither handles the genuinely hard case: external data that needs to stay current in an investor-facing model.

IMPORTDATA and IMPORTRANGE are Google's native answer. According to the Google Sheets function reference for IMPORTRANGE, the function refreshes when the source spreadsheet changes or when the importing sheet is opened - not on a reliable schedule. If your ARR tab pulls Stripe data through a connected sheet via IMPORTRANGE, and that connected sheet hasn't been opened in 3 days, your ARR figure is 3 days stale. Google doesn't surface this staleness anywhere on the tab.

Google Sheets also caps at 10 million cells per spreadsheet. That doesn't bite a standard 3-statement model, but it's relevant if you're storing historical snapshots alongside live-pull data in the same file.

The practical ceiling for auto-updating external data in Sheets - without custom infrastructure - is roughly: refresh on open, or refresh every hour, with no reliability guarantee on either. For a quarterly board pack, that's fine. For a bank syndicate DCF where the comp sheet needs live market data, it's not.