Google Sheets Formulas Not Updating Automatically? Here Are All the Fixes

Google Sheets formulas stop updating automatically for five distinct reasons, and each one has a different fix. The most common culprit is calculation settings being set to "manual" mode rather than "on change"-but IMPORTRANGE caching, circular references, and sheet size throttling are equally frustrating and less documented. This guide covers every cause with a specific solution for each.

Why This Is Such a Common Problem

The Stack Overflow question "Google Sheets formula not recalculating" has accumulated over 255,000 views, making it one of the most-searched spreadsheet issues on the internet. That number reflects how often this problem catches people off guard: Google Sheets should update automatically, so when it does not, users assume something is broken with their formula rather than a configuration or architectural issue.

Most of the time, the formula is fine. The problem is one of five underlying causes described below.

Cause 1: Calculation Settings on Manual

What Happens

Google Sheets has a Calculation setting that controls when formulas recalculate. The options are:

• On change (default): formulas recalculate whenever a cell they depend on changes.
• On change and every minute: recalculates on change and forces a recalculation every 60 seconds.
• On change and every hour: recalculates on change and forces a recalculation every hour.

If this setting was accidentally changed to a mode that does not trigger on change, or if you are working in a Sheet that was imported or shared with a different configuration, formulas will appear to be stuck.

How to Fix It

1. Go to File > Settings (or File > Spreadsheet settings in some regional versions).
2. Click the Calculation tab.
3. Set Recalculation to On change.
4. Click Save settings.

After saving, any cell that previously showed a stale value should update immediately when a dependency changes.

When to Use "Every Minute" or "Every Hour"

These options are useful for formulas that depend on time-sensitive data, for example, a formula using =NOW() or =TODAY() that you want to update automatically without anyone editing the sheet. For most business reports, On change is correct, and the time-based options simply add recalculation load without benefit.

Cause 2: Volatile vs. Non-Volatile Function Behavior

What "Volatile" Means

In spreadsheet engines, a volatile function recalculates every time any cell in the sheet changes, regardless of whether its inputs changed. A non-volatile function only recalculates when its direct inputs change.

Google Sheets volatile functions include:

• =NOW()
• =TODAY()
• =RAND()
• =RANDBETWEEN()
• =INDIRECT()

Non-volatile functions (like =SUM(), =VLOOKUP(), =SUMIF()) only recalculate when their specific input range changes.

The Problem

If your formula uses =TODAY() as part of a date calculation (e.g., =TODAY()-A2 to calculate days elapsed), it will update whenever the sheet recalculates, which is normal. But if you have a formula that wraps a non-volatile function around data that only changes via an external import, it may appear not to update because the non-volatile recalculation engine waits for an input cell to signal a change, and an external data refresh may not always trigger that signal.

How to Fix It

• Wrap a time-based volatile call into your formula to force it to be treated as volatile:

  =IF(NOW()>0, VLOOKUP(A2, data_range, 2, FALSE), "")

This makes VLOOKUP recalculate whenever NOW() triggers (i.e., on every sheet recalculation event), at the cost of slightly more computation.

• **Use =INDIRECT() to dynamically reference ranges** that should update when the sheet refreshes. INDIRECT is volatile and will force recalculation, but be cautious, it also disables dependency tracking, which can hide circular reference errors.

• For most business formulas, the better solution is to structure your data so that changing source data genuinely triggers a dependency update (i.e., your formula directly references the cell being changed by your data source).

Cause 3: IMPORTRANGE and IMPORTDATA Cache

What the Cache Does

=IMPORTRANGE() and =IMPORTDATA() do not update on every sheet recalculation. Google caches their results and refreshes them on a fixed internal schedule, roughly every 30 minutes to several hours, depending on server load and usage patterns. There is no documented guarantee about how frequently this cache refreshes.

This means: if data in the source sheet changes, your =IMPORTRANGE() formula will not reflect that change immediately, and may not reflect it for hours.

How to Force an IMPORTRANGE Refresh

There is no official "force refresh" button for IMPORTRANGE. The options are:

1. Delete the IMPORTRANGE cell and re-enter the formula. This forces a fresh cache pull. It is disruptive if the formula feeds downstream calculations.

1. Add a volatile wrapper to trigger revalidation:

   =IF(NOW()>0, IMPORTRANGE("spreadsheet_url","Sheet1!A1:Z100"), "")

This does not bypass the cache entirely, but it prompts Google Sheets to check whether the cache is still valid more frequently.

1. Use a Google Apps Script to programmatically clear and reset the formula on a schedule:

   function refreshImportRange() {
     var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
     var cell = sheet.getRange("A1");
     var formula = cell.getFormula();
     cell.clearContent();
     SpreadsheetApp.flush();
     cell.setFormula(formula);
   }

Trigger this script with a time-based trigger in Apps Script (e.g., every 30 minutes).

1. Replace IMPORTRANGE with a data connector. If you need data to be reliably current, IMPORTRANGE's caching behavior is an architectural limitation, not a bug. A connector like brooked.io that writes data directly into cells bypasses this limitation entirely, the data is simply there, with no cache layer.

IMPORTDATA Behaves the Same Way

=IMPORTDATA() fetches from a URL and also caches its result. The same workarounds apply. If the URL serves data that changes frequently, IMPORTDATA is an unreliable foundation for a business dashboard.

Cause 4: Circular Reference Detected

What a Circular Reference Is

A circular reference occurs when a formula refers, directly or through a chain of other formulas, to its own cell. For example:

• Cell A1 contains =A1+1 (directly circular)
• Cell A1 contains =B1*2, and Cell B1 contains =A1+5 (indirectly circular)

When Google Sheets detects a circular reference, it stops calculating the cell and returns either a 0, an empty value, or a #REF! error, and sometimes stops calculating other formulas in the same sheet as a side effect.

How to Find Circular References

Google Sheets shows a circular reference warning banner at the top of the sheet when one is detected. However:

• The banner does not always specify which cell is the source.
• In complex sheets with many formula dependencies, the circular cell can be difficult to locate.

To find it:

1. Go to Tools > Show formula auditing.
2. Look for cells highlighted in the dependency trace that point back to themselves.

Alternatively, open the Name Box (the cell reference field at the top left) and type through your suspected formula cells one by one, watching for a #REF! indicator.

How to Fix It

Circular references are almost always a logical error in formula construction. The fix is to restructure the formula to eliminate the self-reference. Common patterns:

• Move intermediate calculations to a separate helper column rather than embedding them in the same cell.
• If you genuinely need iterative calculations (e.g., compound interest calculations), Google Sheets supports this with File > Settings > Calculation > Iterative calculation-but use this only when you understand the iteration limit you are setting.

Cause 5: Sheet Too Large (Performance Throttling)

What Happens

Google Sheets has a soft limit of 10 million cells per spreadsheet. As a sheet approaches this limit, or has many thousands of rows with complex array formulas, Google Sheets begins to throttle recalculation to maintain performance. Formulas may appear to update only partially, or changes may take 30–60 seconds to propagate.

This manifests as formulas that "seem fine but sometimes do not update," which is one of the most confusing symptoms to diagnose.

Signs You Are Hitting This Limit

• The Sheets loading spinner shows for longer than 5 seconds on open.
• Formulas take noticeably longer to update after you edit a cell.
• Google Sheets shows a "This spreadsheet is running slowly" notification.
• ARRAYFORMULA or QUERY results appear after a multi-second delay.

How to Fix It

1. Delete empty rows. A sheet with 50,000 rows of data but formatted down to row 1,000,000 counts all those empty cells against the 10M limit. Select all empty rows below your data and delete them (not just clear them, delete the rows entirely).

1. Split large sheets. Move older historical data to an archive spreadsheet and use IMPORTRANGE only for the most recent 12 months in your active sheet.

1. Replace volatile ARRAYFORMULA expansions with static values. If you have an ARRAYFORMULA that expands down 10,000 rows but only 200 rows have data, the formula still evaluates all 10,000 cells. Rewrite it with a bounded range reference.

1. Use QUERY instead of nested FILTER+SORT combinations. QUERY is more efficient in large sheets because it operates as a single formula evaluation rather than chained operations.

1. Offload heavy data transformation to a data connector. If your Sheet is large because you are doing complex pre-processing of raw API data inside the Sheet, consider moving that work to a tool like brooked.io, which aggregates and transforms before writing to the Sheet.

How to Force Recalculate Everything Right Now

If you are not sure which cause applies and you just need the Sheet to update immediately, use the keyboard shortcut:

Windows/Chrome OS: Ctrl + Shift + F9 Mac: Cmd + Shift + F9 (or Fn + Cmd + Shift + F9 on some keyboards)

This forces a full recalculation of every formula in the spreadsheet, regardless of the Calculation setting. It does not bypass IMPORTRANGE caching, but it clears all other calculation states.

You can also force a recalculation by going to Tools > Force recalculate now (this option may appear as Tools > Refresh in some Sheets versions).

When to Replace Formulas with a Data Connector

Formulas are the right tool when:

• Your data is already in Sheets and you are transforming or summarizing it.
• Your data sources are other Google Sheets (IMPORTRANGE is acceptable for low-update-frequency data).
• You are doing one-time analysis, not ongoing reporting.

Formulas become the wrong tool when:

• You need data from external APIs (HubSpot, GA4, QuickBooks) to be current without manual exports.
• Your Sheet is large enough that performance throttling is causing formula delays.
• IMPORTRANGE caching is causing data freshness issues that affect real decisions.

In those cases, a data connector that writes directly into cells, rather than a formula that fetches on demand, is both more reliable and more maintainable. brooked.io takes this approach: it writes pre-fetched, pre-aggregated data into your Sheet on a schedule, eliminating formula-layer fetching entirely.

Comparison Table

Troubleshooting

I changed the Calculation setting but formulas still do not update Check for a circular reference, it can override the calculation setting and prevent updates. Also verify the specific cell is not intentionally static (check if it contains a hard-coded value rather than a formula, press F2 to enter edit mode and see).

My formula was working yesterday, now it is not This typically indicates a circular reference introduced by a recent edit to another cell in the sheet. Check the formula audit view or look for any cells recently edited that might now reference the non-updating cell.

IMPORTRANGE always shows yesterday's data The IMPORTRANGE cache is updating roughly daily in this case. Use the Apps Script method described in Cause 3 to force more frequent refreshes, or switch to a connector that pushes data into the cell directly.

Ctrl+Shift+F9 did not fix it If force recalculate does not help, the issue is almost certainly IMPORTRANGE caching or a circular reference, those two cases are not resolved by the keyboard shortcut.

The Sheet works fine when I open it but breaks after a few minutes This pattern often indicates a volatile function (like NOW() or RAND()) is triggering excessive recalculation that eventually causes Sheets to throttle. Audit your formulas for unnecessary volatility.

Bottom Line

Google Sheets formulas not updating is a solvable problem in every case, but the solution is different depending on the root cause. Check your Calculation settings first (it is the easiest fix), then look for circular references, then consider whether IMPORTRANGE caching is the architectural limitation you are actually hitting. For business-critical reporting that needs reliable, timely data, replacing formula-based data fetching with a direct connector is the most durable long-term fix.

Fix Your Data Pipeline with brooked.io

If IMPORTRANGE caching or manual data exports are why your formulas keep going stale, brooked.io solves the problem at the source. It writes current data from your business tools directly into Google Sheets on a schedule, no IMPORTRANGE, no stale cache, no manual refreshes required.

Internal link suggestions:

• "Looker Studio Taking Forever to Load? The Real Cause (and 3 Ways to Fix It)" → /looker-studio-slow-loading-fix
• "How to Build a Real-Time Business Dashboard Without a Data Team" → /real-time-dashboard-without-data-team
• "GA4 (other) Bucket Taking Over Your Google Sheets Report? Here's the Fix" → /ga4-other-bucket-fix

Troubleshooting quick reference

Frequently asked questions