Is your Google Sheets spreadsheet filled with formulas that just won’t cooperate? From basic math calculations to complex functions like SUMIF and COUNTIF, formulas are essential for analyzing data and gaining valuable insights. But when they stop working or updating, they become useless. Don’t fret! We’ve got you covered with these effective solutions to tackle the underlying issues.
1. Check Your Formula
You might have already double-checked, but it’s crucial to go through your formulas again. This step is especially important if Google Sheets only fails to calculate formulas in a few cells. Look out for typos, incorrect cell range names, and invalid values, and correct them if necessary. If you come across formula parsing errors like #N/A, #REF!, #VALUE!, and more, take the necessary steps to rectify them.
2. Refresh Your Spreadsheet
Sometimes, Google Sheets may not load correctly in your browser, preventing it from displaying or updating formula results. You can solve most of these issues by simply refreshing the Google Sheets page. Click the refresh icon at the top to refresh your spreadsheet and check if the formulas are functioning as expected.
3. Modify Spreadsheet Calculation Settings
Incorrect calculation settings can be another reason why your Google Sheets formulas may not work or update. Here’s how you can fix them.
Step 1: Open your Google Sheets spreadsheet. Click on the File menu at the top and select Spreadsheet settings from the list.
Step 2: Switch to the Calculation tab and use the Recalculation dropdown menu to select On change. Then, use the Iteration calculation dropdown menu to select Off. Click the Save settings button.
Refresh your spreadsheet after updating the calculation settings and check if the problem persists.
4. Verify the Cells Referenced in the Formula
Your Google Sheets formulas may stop working if there are issues with the cell references in your formula. This can happen if the cells referenced by your formula are invalid, moved, or deleted.
Open your Google Sheets spreadsheet and select the non-working or non-updating cell formula. Google Sheets will highlight all the preceding cells in the formula. Examine these cells and correct them if needed. Repeat this step for all the formulas that aren’t working.
5. Disable Show Formulas Option
Are your Google Sheets formulas appearing as text? If so, you may have accidentally enabled the Show Formulas option in Google Sheets, either through the View menu or by using a keyboard shortcut. Here’s how to disable it so you can display formula results.
Step 1: Open your Google Sheets spreadsheet.
Step 2: Click on the View menu at the top, go to Show, and select Formulas from the submenu. Alternatively, you can also use the keyboard shortcut Ctrl + ` to quickly toggle the display of formulas.
6. Restore a Previous Version or Copy the Spreadsheet Data to a New File
If your Google Sheets formulas still refuse to work or update, it’s possible that the spreadsheet is corrupted. In such cases, your best option is to restore a previous version of the spreadsheet. Here’s how:
Step 1: Open your Google Sheets spreadsheet. Click on the File menu at the top, go to Version history, and select See version history from the submenu.
Step 2: In the right-hand Version history panel, hover over the version you want to restore. Click on the three-dot menu icon and select Restore this version.
If there are no previous versions of the spreadsheet available, you can try copying the data from your spreadsheet to a new file to see if that resolves the issue. Here are the steps to follow:
Step 1: Open your spreadsheet. Use the Ctrl + A keyboard shortcut followed by the Ctrl + C keyboard shortcut to copy everything.
Step 2: Click on the File menu at the top, go to New, and select Spreadsheet.
Step 3: In your new spreadsheet, click on the Edit menu and select Paste.
Once you’ve transferred your data to a new spreadsheet, check if your formulas are working as intended.
Resolving Formula Issues with Style
When formulas stop working or updating, it can significantly diminish the usefulness of your spreadsheets. Before considering a switch to Microsoft Excel, try out the solutions mentioned above to solve the problem. Let us know in the comments below which solution works for you!
Discover more tips and techniques to optimize your Google Sheets usage at Crawlan.com.
