On some occasions, you’ll find it helpful to auto-refresh your calculations or formulas in Google Sheets. This is to ensure that your spreadsheet always contains up-to-date data.
Often, cells containing formulas or functions will only update their values whenever you make changes on the spreadsheet. This is the default calculation setting of Google Sheets, and you can just go on with this setting most of the time.
In some cases, though, there’s a need to include formulas that will fetch frequently-changing values. And while the default calculation setting of Google Sheets is active, you need to manually refresh your spreadsheet for it to show the most recent values. Fortunately, there’s a way to override this default setting.
Thanks to a setting called Recalculation, you can now auto-refresh your spreadsheet every minute or every hour. So, if you need to import highly volatile data such as real-time financial information to your spreadsheet, consider toggling this setting.
Now, let’s learn how to use the Recalculation setting to auto-refresh your formulas in Google Sheets.
Let’s get started!
A Real Example of Auto-Refreshing Formulas in Google Sheets
Let’s assume that you have a spreadsheet that uses the RANDBETWEEN function to generate a set of random numbers. When you refresh or make changes to your spreadsheet, the cells will automatically update their values.
Whatever the reason, sometimes you may want to override the default behavior of Google Sheets such that you want it to update the values instantly every minute. You can enforce this behavior by simply changing the Recalculation setting. In doing so, you no longer have to manually refresh your browser or change anything from your spreadsheet just to display another set of numbers.
So, how can you enable the Recalculation setting to auto-refresh your formulas? Let’s find out by doing a simple activity in the next section.
How to Auto-Refresh Formulas in Google Sheets
- Start by making a copy of our example spreadsheet. Go ahead and click the link below.
- With the spreadsheet already open, you’ll notice that the current sheet contains many random numbers.
- The numbers in the spreadsheet are generated by the
RANDBETWEENfunction, so, if you try to refresh your browser, or type anywhere on the sheet, the values will change. Our goal for this activity is to override this default behavior. Besides allowing Google Sheets to wait for a manual trigger before updating the numbers, we’ll also set it to change the numbers after one minute automatically.
- To do this, click the File menu and then choose Settings. This will display your spreadsheet settings window.
- From the spreadsheet settings window, navigate to the Calculation section.
- At this point, click the Recalculation drop-down button. You should see the following options:
- On change
- On change and every minute
- On change and every hour
- Since we want our current spreadsheet to update every minute, we should choose the option that says ‘On change and every minute’. Click it from the list, and afterward, click the Save settings button.
- With the new setting applied, your spreadsheet now updates every minute.
There you have it! You just learned how to auto-refresh a simple formula in Google Sheets. In addition to
RANDBETWEEN, the Recalculation setting also works for
RAND, and many other functions.
Frequently Asked Questions (FAQ)
- When to Enable Auto-Refresh in Google Sheets?
The auto-refresh setting is best used for instances wherein you need to include volatile data in your spreadsheet. Volatile data is data that frequently changes over time. A good example is, when you are keeping a record of the current stock prices of different companies in a spreadsheet using the GOOGLEFINANCE function. Of course, you’ll want to make sure that the prices indicated are always updated. To do so, change the Recalculation setting to On and every minute or every hour to force Google Sheets to fetch the most recent data from the Google Finance website.
- When Should You Not Use the Auto-Refresh?
While it may be tempting to apply the auto-refresh setting to your spreadsheets, it’s not always recommended. Enabling this setting will affect the performance of your computer, as Google Sheets will auto-refresh your spreadsheet every time. This should not be a problem for small datasets. However, if you’re working on a spreadsheet with too many complex formulas, you might experience some lag in your device. Having said that, you need to be wary of when you should enable the auto-refresh setting of your spreadsheets. This could get you in an embarrassing situation, especially when you are presenting your work to other people.
That’s it! You just learned how to auto-refresh formulas in Google Sheets. Now you can make sure that your spreadsheet contains the most recent data.
Take a look at our other articles on Google Sheets to learn more techniques.
Subscribe to our newsletter if you want to receive more useful articles about Google Sheets.