Your Google Sheets spreadsheet likely contains 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 your spreadsheet is updated. Here are the 7 best fixes for formulas not working in Google Sheets.
Such problems can occur for various reasons, including simple typos, incorrectly configured account settings, andCorrupted spreadsheetsBelow, we will walk you through all the possible solutions to resolve the underlying issue.
1. Double-check your formula.
You may have already done this, but it's a good idea to double-check your formulas. This is especially important if Google Sheets isn't calculating formulas in just a few cells. Check for spelling errors, incorrect cell range names, or invalid values in the formula, and correct them if necessary.
If you notice Formula parsing error in Google Sheets, such as #N/A, #REF!, #VALUE!, etc., take steps to fix them.
2. Update your spreadsheet
Sometimes, it may Google Sheets fails to load This error occurs in your browser, preventing it from displaying or updating formula results. You can resolve such issues by refreshing the Google Sheets page.
Click the refresh icon at the top to refresh your spreadsheet and see if that makes the formulas work as expected.
3. Modify spreadsheet account settings
Another reason why Google Sheets formulas don't work or update is incorrect account settings. Here's how to fix it.
Step 1: Open Google Sheets spreadsheetClick on the menu. "a file" At the top, select "Settings" from the list.
Step 2: Switch to tab "the account" Use the drop-down menu within "recalculation" To determine "When changing." Then use the drop-down menu under Recurrence Calculation to select Stop and click the button. Save settings.
Refresh your spreadsheet after updating your account settings and check if the issue persists.
4. Check the cells the formula refers to.
Google Sheets formulas may stop working if there are issues with the cell references in your formula. This can happen if the cells the formula references are invalid, moved, or deleted.
Open a Google Sheets spreadsheet and select the cell formula that isn't working or updating. Google Sheets will highlight all formula precedents. Examine those cells and correct them if necessary. Repeat this for all formulas that aren't working.
5. Disable the Show Formulas option.
Are your Google Sheets formulas appearing as text? If so, you may have accidentally enabled the option to show formulas 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 Google Sheets spreadsheet.
Step 2: Click Menu "an offer" At the top, go to "show", and select Formulas from the submenu. Alternatively, you can also press the keyboard shortcut Ctrl+` To quickly show or hide formulas.
6. Restore a previous version or copy spreadsheet data to a new file
If your Google Sheets formulas still aren't working or updating, your spreadsheet may be corrupted. In this case, your best option is to restore a previous version of the spreadsheet. Here's how to do it.
Step 1: Open Google Sheets spreadsheetClick on the menu. "a file" At the top, go to “Release History”, and select View 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 icon Three-dot menu Select Restore this version.
If there are no older versions of the spreadsheet available, you can try copying the spreadsheet data to a new file to see if that resolves the issue. Here are the steps for the same.
Step 1: Open Your spreadsheetPress the keyboard shortcut. Ctrl + A followed by a keyboard shortcut Ctrl + C To copy everything.
Step 2: Click Menu "a file" At the top, go to "new", and select "table Data.
Step 3: In the new spreadsheet, click Menu. "Release" and select "sticky".
After you've moved your data to a new spreadsheet, check if your formulas are working as expected.
Fix 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 solutions above to fix formulas not working in Google Sheets, and let us know what works for you in the comments below.
