How to Force Google Sheets to Automatically Recalculate Formulas Every Minute

Unlike a static desktop Excel file, Google Sheets excels at pulling live data from the internet. Functions like GOOGLEFINANCE for stock prices, IMPORTXML for web scraping, and NOW() for the current time are incredibly powerful for building live dashboards.

However, many users run into a frustrating limitation: Google Sheets caches data aggressively to save server bandwidth. If you use a random number generator (RAND) or a live time function (NOW()), you might notice the values only update when you manually edit a cell or refresh the entire webpage. If you have the spreadsheet displayed on a TV monitor as a passive dashboard, the data will quickly become stale.

To fix this, you must dive into the spreadsheet settings and force Google Sheets to continuously recalculate these volatile formulas in the background.

How to Change the Calculation Settings

Google Sheets includes a hidden setting specifically designed to bypass the default caching behaviour and force regular updates.

1. Open your Google Sheets document.
2. In the top menu bar, click on File.
3. Select Settings from the dropdown menu (it is usually near the very bottom).
4. A “Settings for this spreadsheet” pop-up window will appear. Click on the Calculation tab at the top.
5. Look for the dropdown menu labelled Recalculation. By default, this is set to “On change”.
6. Click the dropdown and select On change and every minute. (You can also select “On change and every hour” if your data does not need to be highly real-time).
7. Click Save settings at the bottom of the window.

What This Setting Actually Does

It is important to understand how Google Sheets applies this rule to avoid overloading your spreadsheet.

• Volatile Functions: This setting specifically targets volatile functions like NOW(), TODAY(), RAND(), and RANDBETWEEN(). These formulas will now automatically execute every 60 seconds, updating the numbers on your screen without any user interaction.
• External Data Imports: Functions like IMPORTDATA, IMPORTHTML, and IMPORTXML have their own internal refresh limits (usually every 1-2 hours) dictated by Google’s servers. Changing the recalculation setting to “every minute” will not force these external scrapers to fetch data faster. Google actively prevents you from using Sheets to DDoS third-party websites.
• Google Finance: The GOOGLEFINANCE function is a hybrid. It refreshes automatically, but financial data can be delayed by up to 20 minutes depending on the exchange. The one-minute recalculation setting ensures your sheet updates as soon as Google’s internal cache receives the new price, making it essential for stock tracking dashboards.