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 saved.
Excel Formulas Not Updating Until Save: Possible Reasons
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.
Excel Formulas Not Updating Until Save: 6 Handy Solutions
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: Excel Formulas Not Calculating Automatically
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
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]: Excel Formula Not Showing Correct Result
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.
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.
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.
Download Practice Workbook
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
