Google sheets will automatically refresh formulas whenever you change the value of any cell in a sheet. If your data is based on the value of a volatile function like NOW(), TODAY(), RAND() or RANDBETWEEN() you can automate a recalculation of sheets formulas every minute or every hour by using sheet settings.
To refresh your formulas and calculations whenever you desire, you need to force a cell change on the sheet. An example of how to do this would be to opening an empty cell by selecting it and hitting Enter (↵), or double-clicking, then entering a space, and committing the edit using Enter (↵) or by clicking out of the cell. We’ve a quick trick to make this easier below.
Based on our testing, a cell update will trigger all formulas based on volatile cells to recalculate. Including any INDIRECT() references to those cells, and also any conditional format rules based on data from those cells. If you find your formulas do not refresh or they are not based on volatile data functions such as NOW(), the best way to force an update would be to copy the cells and paste them over themselves, forcing a cell change.
A quick trick to force an update
Another handy trick for forcing an update is to add a checkbox to your sheet. Like we have in the example sheet used to create this article where ticking or un-ticking the box roll a die 10 times and perform some other date based other calculations.
To do this, pick a cell in your sheet that is used for nothing else and use the Insert > Tick box option from the menu.
You’ll then have a tick box in the cell on your sheet that will trigger a cell change every time you tick or un-tick it. This change will force the sheet to recalculate.
How to recalculate formulas every minute or hour
If your sheet is based on data volatile functions (NOW, RAND, TODAY or RANDBETWEEN). You can set sheets to recalculate all cell values every hour, or every minute.
To do this, open the File menu and select Setttings.
In the window that opens, click the Calculation tab at the top and under the Recalculation heading are 3 options. Recalculation ‘On change’ (this is the default), ‘On change and every minute’, and ‘On change and every hour’. Sheets will always calculate on change, but you can set your sheet to recalculate additionally every minute or every hour.
Whilst this refresh will occur whilst your sheet is open, it won’t continue to happen when the file is closed. This is to preserve resources when nobody is looking! If you really need your sheet to update even when it is closed, you’ll need to use Apps Script to force a cell change, and a Trigger to run the Apps Script periodically.
Do sheets formulas refresh when you open a sheet?
Yes, if you re-open a sheet, or reload your browser tab, you’ll notice that volatile functions update and all cells that depend upon that data also update.
NOTE: Refreshing or re-opening a sheet does NOT guarantee a refresh of data generated by using IMPORTRANGE.
How do you force IMPORTRANGE data to refresh?
If you want to force a refresh of data imported from another sheet using IMPORTRANGE, the quickest way is to force a cell change by overwriting the import formula with the same formula. Select the cell or cells containing your IMPORTRANGE formula, hit Edit > Copy or Ctrl+c (⌘+c on Mac) and then Edit > Paste or Ctrl+v (⌘+v on Mac) to copy the same formula into the same cells. This should force a refresh.
Using IMPORTRANGE to import data from another sheet into your sheet works slightly differently from a regular cell refresh. In order to prevent overload of the Google servers, IMPORTRANGE will only automatically check for updates every hour while your document is open, even if the formula and spreadsheet don’t change.
Also, if you open and reload your document, it doesn’t always trigger a reload of IMPORTRANGE data.
If you use IMPORTRANGE to reference a cell, or a range of cells, in another sheet that contains volatile data. Such as cells using NOW(), RAND() or RANDBETWEEN(), you may receive a cell #ERROR! Sheets will return this error in situations that may cause it to overload by continually requesting new data. We used this technique in our example sheet for this article. But didn’t receive this error. However, the amount of data we are importing is tiny (1 cell).