When working with google sheets in a few situations you will face issues like formulas not working or updating. This happens mostly when you are trying to open a fresh sheet. It also occurs while switching between sheets.
Though each of the error scenarios is unique, we have collected some of the common causes of this problem. In this tutorial, we will discuss how to fix issues with google sheets formulas not updating or working.
Try one of these steps:
1. Refresh your spreadsheet by pressing F5
2. Make a copy of the current sheet (File>Make a copy)
3. Try changing the calculation settings in File > Settings
4. Restore to an earlier version by visiting File > Version history
Refreshing or making a new copy of the sheet fixes the issue in many scenarios.
Let’s see the steps in action visually.
I have used these methods in different scenarios. You don’t know which one will work in your case but one of these methods should be able to fix the problems.
Refresh spreadsheet
Refreshing the google sheet fixes the issue in most cases. If your formulas are not working or updating, the first thing you should try is to refresh your spreadsheet.
Here is how you can refresh the google sheets application.
- Click on the reload button in your browser. This button is located at the top left corner next to the back/forward button
- You can also use the keyboard shortcut to refresh the sheet. Press F5 on your keyboard to start the refresh immediately. This is called soft refresh
- You can also hard refresh the window by holding down the shift button and pressing F5 on your keyboard. Hard refresh might clear the cache and ask you to re-login to your google account
Here is an video showing this in action:
Check and see if your issue has been fixed. If it is fixed then you are good to go, else follow the next step.
Copying to a new spreadsheet
Copying a new sheet is like creating a new spreadsheet from scratch. Google Sheets will try to create all cells and add formulas to them. Some experts suggest this should fix issues that are not fixed with refreshing.
To create a new sheet via copy, follow these steps:
1. Open google sheets on your computer and click on File from the top menu
2. Inside the File, click on the “Make a Copy” option
3. This will bring up the Copy document window
4. Name your file. Choose the folder where to copy
5. Click ok and google sheets will copy the file to the new destination
There is a shortcut method to do this if you are just copying one or a few sheets.
Press and hold the Control key in PC or Command key in MAC and then select the sheets you want to make a copy of.
Right-click on any of the sheet names to open the small popup menu. From here select Copy to > New spreadsheet.
This will copy the selected sheets to a new spreadsheet. You will see a confirmation message on your screen.
In the new sheet, check to see if the issue is fixed now. If it’s not then try the next method.
Changing calculation settings
You have tried refreshing the sheet and also created a copy of the sheet but the issue persists.
Let’s try to reset the calculation settings:
Here is how to change calculation frequency:
1. Click on File from the top menu and then click on settings
2. This will open the Setting options for the spreadsheet
3. Switch to the Calculation tab
4. Note down the current setting in the Recalculation tab. Try to change this to something else
5. If iterative calculation is on, then try switching it off
6. Click on Save settings
This should fix the problem.
Check if it’s fixed, if not then we have one last option to restore the sheet to an earlier working version.
Restoring to an earlier version
You have tried all options but none of them seem to work for your case. In this case, you can go ahead and restore your sheet to an earlier version.
Before you start, I highly recommend you take a backup of your current worksheet. Because restoring might change the current sheet.
To restore the sheet to earlier versions, follow these steps:
1. From the top menu, click on the File menu
2. Under File menu options, click on Version history
3. From the available options under Version history, choose to see version history
4. This will open a right sidebar window that has all the versions for your current sheet
5. All the version changes are listed with date and time along with the editor name
6. Click on any one version and google sheets will show you a preview of the version
7. Remember the last time this sheet was working fine. Try to restore this version by clicking on the Restore button at the top
Here is a video showing this in action:
How to get further help
We have discussed all possible ways to solve the issue of google sheets formulas not updating or working. But every problem is unique.
In case your issue is still not fixed, you can try to get help from the google sheets community.
You can post your problem to the official sheets community or on Stackoverflow.
Before posting, I recommend you to search the community for similar questions that are already answered there.
FAQ
How to refresh formulas in google sheets?
To refresh, click on the refresh button in the browser or hit the F5 key on your keyboard.
Google sheets formulas not updating automatically, what to do?
If you have set the formula to auto-update infrequent intervals and it’s not updating, check the Calculation settings from the File>Settings option.
Try to change the Recalculation settings to see if the problem gets fixed.
Wrapping up
In this tutorial, we discussed the google sheets formulas not updating problems that happen with many people. I have listed down the solutions which commonly fix this issue. Go ahead and try these methods step by step or in different combinations and this should solve your problem with your sheet.
Appendix
[1] Calculation settings in google sheets – Link
Further Reading
New to google sheets ? Start here
More related to Formulas and Logical functions:
Logical functions in google sheets
Learn more about Google sheets Formulas.
Error handling in google sheets
