Excel is the most widely used tool when it comes to dealing with huge datasets. We can perform myriads of tasks of multiple dimensions in Excel. Sometimes, the formulas that we use do not get updated until we save it. This may occur due to various reasons. In this article, I will show 5 instances and solutions for Excel formulas not updating until save.
Download Practice Workbook
Reasons Behind Excel Formulas Not Updating Until Save
Due to several reasons, Excel Formulas do not get updated. The most common of them are listed below.
- The calculation mode is Manual.
- The cells are formatted as Text.
- Space before the Equal (=) sign.
- The Show Formula button is on.
- Presence of an Apostrophe (‘) before a Formula.
When these occur, Excel does not update the formulas automatically. For example, the image below shows a SUM formula.
When you change the Marks, the Total will not change. That’s the problem.
Now I will discuss 6 methods to solve this issue.
6 Handy Solutions for Excel Formulas Not Updating Until Save
This is the dataset that I am going to use. Here we have some students along with their marks in Physics and Math. I will calculate the total marks.
Now we will demonstrate six easy solutions to solve this problem.
1. Set Calculation Options to Automatic
Sometimes the formulas do not get updated if the calculation mode is Manual.
To resolve this issue follow the steps stated below.
Steps:
- Go to the Formulas >> select Calculation Options >> select Automatic.
- Now Excel will update the formulas every time you make a change. The complete table will look like this.
Read More: [Fixed!] SUM Formula Not Working in Excel (8 Reasons with Solutions)
2. Change Cell Format from Text
Sometimes, Excel formulas do not work or get updated if the cells are formatted as Text. In the image below, Cell E5 was in Text Format. That’s why the formula was not updated.
Follow the steps to solve this issue.
Steps:
- Select Cell E5. Then go to the Home Tab >> select the drop-down list >> select General.
- Now Excel will update the formulas.
- Use the Fill Handle to AutoFill up to E11.
Read More: [Fixed!] Formula Not Working and Showing as Text in Excel
Similar Readings
- How to Refresh Chart in Excel (2 Effective Ways)
- Excel VBA: Turn Off Screen Update
- How to Disable Background Refresh in Excel (2 Handy Methods)
- [Fixed!] Formula Result Showing 0 in Excel (3 Solutions)
- How to Refresh Excel Sheet Automatically (3 Suitable Methods)
3. Remove Extra Space Before Equal Sign
Sometimes Excel may not calculate any formula rather than simply demonstrating it in a cell.
This occurs because there is a hidden space before the equal (=) sign.
Steps:
- Remove the hidden space before the equal sign. Then recalculate.
- Excel will now update the formulas.
Read More: [Fixed!] Why Formula Is Not Working in Excel (15 Reasons with Solutions)
4. Turn of Show Formula Button
Sometimes Excel does not update or consider any change if the Show Formula button is ON.
Since the Show Formulas button is ON, Excel is showing the formula rather than the result.
Steps:
- Go to the Formula Tab >> select Formula Auditing >> turn the Show Formula OFF.
- Now Excel will show the results. The image below shows the complete calculation.
Read More: [Solved]: Excel Array Formula Not Showing Result (4 Suitable Solutions)
5. Remove Apostrophe Before Equal Sign
Sometimes Excel may not calculate any formula rather than simply demonstrating it in a cell.
This occurs because there is an Apostrophe (‘) before the equal (=) sign.
Steps:
- Remove the Apostrophe before the equal sign. Then recalculate.
- Excel will now update the formulas.
Read More: [Fixed!] Excel Cells Not Updating Unless Double Click (5 Solutions)
6. Get Rid Of Circular Referencing
Excel does not update any formula if circular referencing exists. Circular referencing occurs when the formula includes itself in its calculation or refers to another cell that depends on itself.
Here, in Cell E5 there is a formula that itself depends on the value of E5. That’s why circular referencing occurred and the formula is not getting updated.
Steps:
- Avoid circular referencing by correcting the formula.
- Now complete the rest of the table. Excel will update the formulas this time.
Read More: [Fixed]: Excel Formula Not Showing Correct Result (8 Methods)
Conclusion
In this article, I have explained 6 cases where Excel formulas are not updated until save. I hope these cases will be helpful to you. If you have any comments or suggestions, please leave them in the comment section
Related Articles
- Pivot Table Not Refreshing (5 Issues & Solutions)
- How to Refresh Formulas in Excel (2 Easy Methods)
- [Fixed!] Excel SUM Formula Is Not Working and Returns 0 (3 Solutions)
- [Fixed!] Excel Formulas Not Working on Another Computer (5 Solutions)
- [Solved:] Excel Formula Not Working unless Double Click Cell (5 Solutions)