Your Google Sheets spreadsheet is likely to contain a variety of formulas, from basic math to complex functions like SUMIF and COUNTIF. These formulas can help you perform calculations, analyze data, and gain valuable insights into your information. However, these Google Sheets formulas are useless if they stop working or updating your spreadsheet.
Such issues can occur for various reasons, including simple typos, misconfigured calculation settings, and corrupted spreadsheets. Below, we will walk you through all the possible solutions for resolving the underlying problem.
1. Double-Check Your Formula
You might have already done this, but it is a good idea to double-check your formulas. That’s essential if Google Sheets is not calculating formulas in only a few cells. Check your formula for typos, incorrect cell range names, or invalid values, and correct them if necessary.
If you notice the formula parse error in Google Sheets, such as #N/A, #REF!, #VALUE!, and others, take the necessary steps to fix them.
2. Refresh Your Spreadsheet
At times, Google Sheets may fail to load correctly in your browser, preventing it from displaying or updating formula results. You can most resolve such issues by refreshing the Google Sheets page.
Click the refresh icon at the top to refresh your spreadsheet and see if that gets the formulas to work as expected.
3. Modify Spreadsheet Calculation Settings
Another reason Google Sheets formulas may not work or update is incorrect calculation settings. Here’s how you can fix them.
Step 1: Open your Google Sheets spreadsheet. Click the File menu at the top and select Settings from the list.
Step 2: Switch to the Calculation tab and use the drop-down menu under Recalculation to select On change. Then, use the drop-down menu under the Iteration calculation to select Off and click the Save settings button.
Refresh your spreadsheet after updating the calculation settings and check if the issue remains.
4. Check the Cells That the Formula Is Referencing
Your Google Sheets formulas may stop working if there are issues with the cell references in your formula. This can happen if the cells your formula is referencing are invalid, moved, or deleted.
Open your Google Sheets spreadsheet and select the cell formula that is not working or updating. Google Sheets will highlight all the precedents of the formula. Examine those cells and correct them if necessary. Repeat this for all the formulas that are not working.
5. Disable the Show Formulas Option
Are your Google Sheets formulas appearing as text? If so, it’s likely you have accidentally enabled the Show Formulas option in Google Sheets, either through the View menu or a keyboard shortcut. Here’s how to turn it off so you can view formula results.
Step 1: Open your Google Sheets spreadsheet.
Step 2: Click the View menu at the top, go to Show, and select Formulae from the sub-menu. Alternatively, you can also press the Ctrl + ` keyboard shortcut to show or hide formulas quickly.
6. Restore an Earlier Version or Copy Spreadsheet Data to a New File
If your Google Sheets formulas are still not working or updating, the spreadsheet may be corrupted. In that case, your best option is to restore an earlier spreadsheet version. Here’s how to do that.
Step 1: Open your Google Sheets spreadsheet. Click the File menu at the top, go to Version history, and select See version history from the submenu.
Step 2: In the Version history panel on your right, hover your mouse over the version you want to restore. Click the three-dot menu icon and select Restore this version.
If there aren’t any older spreadsheet versions available, you can try copying your spreadsheet data to a new file to see if that fixes the problem. Here are the steps for the same.
Step 1: Open your spreadsheet. Press the Ctrl + A keyboard shortcut followed by Ctrl + C keyboard shortcut to copy everything.
Step 2: Click the File menu at the top, go to New, and select Spreadsheet.
Step 3: In your new spreadsheet, click the Edit menu and select Paste.
After transferring your data to a new spreadsheet, check if your formulas work as expected.
Fixing Formulas with Finesse
When formulas stop working or updating, it can significantly reduce the usefulness of your spreadsheets. Before you consider switching to Microsoft Excel, try the above solutions to fix the problem, and let us know which works for you in the comments below.
Last updated on 27 October, 2023
The above article may contain affiliate links which help support Guiding Tech. However, it does not affect our editorial integrity. The content remains unbiased and authentic.