Why Your Sheet is Stuck on “Calculating… (XX%)”

When Google Sheets hangs on “Calculating… (XX%)”, the calculation engine on Google’s cloud servers has hit a dependency bottleneck or calculation loop. The percentage counter reflects the completion rate of the formula dependency tree. It stalls when volatile functions (like INDIRECT, OFFSET, or NOW), open-ended array ranges (like A:A), or circular references force millions of recursive recalculations. To break the hang, change recalculation settings to on-change only, convert open-ended range references to explicit cell boundaries, or temporarily turn off iterative calculations.

Fast-Fix: The 45-Second Solution

When your spreadsheet freezes mid-calculation, fix it by setting Recalculation to “On change” and turning off “Iterative calculation” under File > Settings > Calculation, this immediately surfaces circular dependency errors (#REF!) instead of hanging. Then, convert open-ended array ranges like A:A to bounded references (A2:A1000) and pause heavy IMPORTRANGE or GOOGLEFINANCE functions by prefixing them with an apostrophe ('=) while editing.

Quick Risk Snapshot

• Severity: High (Locks spreadsheet editing and grinds output updates to a halt)
• Safe to Edit/Depend On?: No (Calculated results are incomplete, and displayed numbers may be stale)
• Primary Cause: Open-ended range references (A:A) nested inside array, lookup, or aggregation formulas
• Secondary Cause: Excessive volatile functions (OFFSET, INDIRECT, TODAY, NOW) triggering cascading recalculations
• Rare Cause: Circular dependencies combined with active iterative calculation settings

Low Risk vs. High Risk Diagnostic Paths

Determining whether the calculation stall is localized to a single sheet tab or spans your entire data architecture dictates how you troubleshoot:

• Localized Formula Overload (Low Risk): The progress bar hangs when editing a specific tab containing heavy array formulas (FILTER, QUERY, SORT, UNIQUE). The fix involves restricting range boundaries or converting static calculated output into plain values directly on that tab.
• Multi-Workbook Dependency Mesh (High Risk): The percentage bar freezes because multiple spreadsheets are chained together via IMPORTRANGE, or because custom Google Apps Script triggers are modifying cells during calculation passes. Resolving this requires auditing external data pipelines and breaking circular inter-sheet references.

How Google Sheets Calculation Processing Works

Google Sheets evaluates formulas using a cloud-side execution engine that constructs a directed acyclic graph (DAG), commonly called the dependency tree.

Think of this engine like a automated factory assembly line. When you edit cell A1, the engine scans the entire workbook to identify every cell that relies on A1. If B1 equals A1 + 1, B1 goes onto the conveyor belt to be recalculated. If C1 relies on B1, C1 enters the queue next.

The percentage indicator (“Calculating… 45%”) tracks how much of this assembly line queue has been processed. The system stalls under two main mechanical conditions:

1. Conveyor Belt Jam (Data Bloat): When a formula references A:A across a sheet with 50,000 rows, the engine must evaluate 50,000 individual slots even if only 10 rows contain data. If 20 formulas reference A:A, the queue instantly inflates to 1,000,000 evaluation tasks.
2. Infinite Assembly Loop (Volatile or Circular Logic): Volatile functions like NOW() or OFFSET() invalidate the entire dependency tree on every single keystroke. If a volatile function feeds into an iterative calculation loop, the engine recalculates the same cells repeatedly, pinning the progress bar at a fixed percentage.

Probability Breakdown: Root Causes of Calculation Hangs

Root Cause	Likelihood	Key Identifier
Open-Ended Range References (A:A)	40%	Array formulas (SUMIFS, FILTER, VLOOKUP) referencing full columns across large tabs.
Volatile Function Chains (OFFSET, INDIRECT)	25%	Recalculation triggers on every keystroke, forcing widespread dependency updates.
Chained IMPORTRANGE / External Data Fetches	20%	The progress bar freezes while waiting for external server responses or API rate limits.
Iterative Calculation / Circular Loops	10%	File settings allow circular references, forcing up to 100 calculation passes per edit.
Heavy Conditional Formatting Rules	5%	Hundreds of custom rules evaluating full-column ranges simultaneously during cell edits.

Technical Troubleshooting: Step-by-Step Diagnostic Pathways

Path 1: Bounding Open-Ended Column References

Full-column references force the calculation engine to scan millions of empty cells.

1. Press Ctrl + F (or Cmd + F) and click the three dots to open Find and replace.
2. Search for :A, :B, :C within formulas to locate open-ended ranges like A2:A or A:A.
3. Restrict open-ended ranges to explicit limits. For example, change =SUMIFS(C:C, A:A, "Yes") to =SUMIFS(C2:C5000, A2:A5000, "Yes").
4. If your dataset expands dynamically, use Google Sheets Tables or explicit named ranges instead of full-column references.

Path 2: Auditing Volatile Functions

Volatile functions recalculate every time any cell in the workbook changes, regardless of whether their inputs changed.

• Replace OFFSET with INDEX:OFFSET is volatile; INDEX is not.
  • Broken (Volatile): =OFFSET(A1, 1, 0, 10, 1)
  • Fixed (Non-Volatile): =INDEX(A2:A11)
• Replace INDIRECT with Direct References or SWITCH: INDIRECT forces the engine to re-parse text strings into cell addresses on every pass. Replace dynamic tab lookups with conditional logic where possible.
• For a detailed guide on locating these performance bottlenecks, see How to Identify “Volatile Functions” Slowing Sheets.

Path 3: Resolving Sorting and Filtering Overhead

Combining SORT, UNIQUE, and FILTER inside a single dynamic array formula creates massive processing overhead when applied to thousands of rows.

• Break complex nested array statements into dedicated helper columns.
• If sorting dynamic data is killing execution speed, see Why SORT and UNIQUE are Killing Spreadsheet Speed.
• For formulas using heavy logical filtering, review Why OFFSET and FILTER Cause Performance Lag.

Path 4: Disabling Iterative Calculation Loops

If two cells depend on each other, Google Sheets will hang if iterative calculation is enabled.

1. Navigate to File > Settings > Calculation.
2. Uncheck Iterative calculation.
3. Click Save settings.
4. If your formulas contain a circular dependency, Sheets will immediately flag the cell with #REF! error text reading “Circular dependency detected”.
5. To troubleshoot and fix circular references, see “Circular Dependency Detected” (Iterative Calc).

What Increases the Risk

• High Row Counts with Empty Space: Keeping 50,000 blank rows at the bottom of a sheet while using open-ended range references (A:A).
• Real-Time Recalculation Timers: Setting recalculation to “On change and every minute” in sheets loaded with TODAY() or NOW() functions.
• Stacked Conditional Formatting: Applying 50+ custom conditional formatting formulas across full columns. For diagnostic steps, see Troubleshooting Lag (100+ Conditional Formatting Rules).
• Multi-Tab IMPORTRANGE Loops: Sheet A pulling from Sheet B, which pulls from Sheet C, which references back to Sheet A. If external import calls hang, see Resolving IMPORTRANGE “Loading…” Hang.

Consequence Timeline: Operational Friction Impact

• Immediate (0–5 Minutes): Progress bar freezes at a specific percentage. Cell inputs lag, and edits display delayed values.
• 30 Minutes: Browser RAM usage spikes. Chrome tabs may freeze, throw lag warnings, or crash.
• 24 Hours: Downstream integrations (Looker Studio, Google Apps Script, connected BigQuery tables) fail to update or pull incomplete datasets due to formula execution timeouts.

What This Is Confused With (Signal Breakdown)

Error Signal	Cause / Behavior	Key Difference from “Calculating…”
“Calculating… (XX%)”	Cloud calculation engine is processing a heavy or looping dependency tree.	Formula syntax is valid, but the calculation queue is overloaded or looping.
“Loading…” in IMPORTRANGE	Waiting for network response or authentication from an external Google Sheet.	Data fetch is pending; the local formula engine has not started calculating. See Resolving IMPORTRANGE “Loading…” Hang.
“Calculation Limit” Error	Hard safety boundary triggered when formula count or complexity exceeds system caps.	The engine halts calculation entirely and displays a permanent error cell. See “Calculation Limit” (50k+ Formulas).
“Exceeded maximum execution time”	A custom Google Apps Script exceeded the 6-minute runtime cap.	Script execution failure, not a native formula calculation queue stall. See “Exceeded maximum execution time” (Calculations).

What To Do Right Now

1. Pause Auto-Calculation: Open File > Settings > Calculation and set recalculation to On change. Turn off Iterative calculation.
2. Convert Static Ranges to Values: Select historical data ranges containing calculated formulas, copy them (Ctrl + C), and press Ctrl + Shift + V (Paste Values Only). This eliminates formula overhead on static historical records. See How to Use “Paste Values Only” for Speed.
3. Delete Unused Rows and Columns: Scroll to the bottom of your dataset, highlight all empty rows beneath your data, right-click, and select Delete rows.
4. Isolate Heavy Tabs: Duplicate the workbook, delete tabs one by one, and observe when the “Calculating…” bar disappears to locate the broken tab.

Hard-Stop Triggers

• Continuous Browser Crashes: If opening the sheet immediately causes Chrome to throw a “Memory Limit Exceeded” crash page, stop trying to edit formulas in the browser. See Troubleshooting “Memory Limit Exceeded” (Chrome Tabs).
• Total Cell Count Nearing 10 Million: If your workbook approaches Google Sheets’ hard ceiling of 10 million cells, native formula calculations will stall permanently. You must archive historic data or migrate to BigQuery/SQL.

What an Admin or Specialist Will Check

When auditing a sheet stuck in a calculation loop, a spreadsheet specialist will:

1. Audit Range Boundaries: Search all sheets for full-column references (A:A, 1:1) in array and lookup formulas.
2. Review Volatile Function Tree: Map all occurrences of INDIRECT, OFFSET, NOW, and TODAY to verify whether they feed into massive summary tables.
3. Inspect Conditional Formatting Rules: Open the conditional formatting sidebar across all tabs to remove duplicate, overlapping, or full-column rules.
4. Check Apps Script Triggers: Inspect the Apps Script project editor (Extensions > Apps Script) for onEdit or onChange functions executing unintended sheet writes during formula updates.

Typical Effort Range

• Settings Adjustment & Disabling Iterative Calculation: 1 to 2 minutes.
• Bounding Open Ranges & Deleting Empty Rows: 5 to 15 minutes.
• Refactoring Volatile Functions and Array Logic: 30 to 60 minutes.

Related System Escalators

• To audit volatile functions across your sheet, see How to Identify “Volatile Functions” Slowing Sheets.
• To fix performance drops caused by complex sorting logic, see Why SORT and UNIQUE are Killing Spreadsheet Speed.
• To resolve circular reference errors, see “Circular Dependency Detected” (Iterative Calc).
• To fix lag caused by excessive conditional formatting, see Troubleshooting Lag (100+ Conditional Formatting Rules).
• To handle browser memory exhaustion from calculation overhead, see Troubleshooting “Memory Limit Exceeded” (Chrome Tabs).

Workspace Assessment

A spreadsheet stuck on “Calculating… (XX%)” is experiencing a dependency queue jam on Google’s cloud servers. It is almost always caused by open-ended column references (A:A) or volatile functions (OFFSET, INDIRECT) forcing millions of unnecessary calculation steps. By restricting formula ranges to explicit cell boundaries, turning off iterative calculation, and converting static historical formulas to plain values, you can clear the dependency queue and restore immediate calculation speed.