Suppose you have created a spreadsheet of salary of the workers of your institution for the management committee. Just before you print the copy, you notice that the Excel formulas are not updating automatically and all are showing previous results. In this, article I’ll show you how to update Excel Spreadsheet Formulas automatically.
Download Practice Workbook
Reasons for Formulas Not Updating Automatically
There are several reasons for not automatically updating formulas in Excel. Among them, the most common and frequently encountered causes are:
i) Calculation option is set to Manual.
ii) The cells are formatted in the Text format.
iii) Circular Reference is induced.
iv) Space used before the equal sign.
v) Show Formulas button is turned on.
5 Common Fixes for Not Updating Formulas Automatically in Excel Spreadsheet
In this section, you’ll find 5 methods for fixing the problem of not updating Excel Spreadsheet formulas automatically. Let’s check them now!
1. Set Automatic Calculation for Updating Excel Formulas
This is one of the most frequently seen reasons which occurs Excel formulas are not updating automatically.
To fix this, just follow the procedure.
- Go to the Formulas tab and ensure that the Calculation Options is selected Automatic not Manual.
That’s how we can fix the problem of not updating formulas in an Excel spreadsheet.
2. Checking Cell Format
Let’s say, we have a dataset for the salary of the employees of an institution and we want to calculate the total salary using the SUM function.
So, let’s start the procedure.
- First, apply the Formula
And press ENTER.
- If you don’t get the SUM, go to the Home tab and check whether the cell is formatted as something like Text.
- Change the format from Text to Currency.
- Again apply the previous Formula and now you will get the desired result.
Thus we can update Formulas by checking the format of the cell in an Excel worksheet.
3. Checking for Circular Reference
The Circular reference is a formula that itself is present within a formula or refers to a cell that works on the basis of this formula. This is another culprit that causes Excel Formulas not to update automatically. For the previous Dataset, let’s try again to get the total salary.
- Firstly, apply the following formula:
- C11 = the cell where you want to get the result
- Press ENTER and a dialogue box will show up indicating Circular reference and you won’t get the result if it shows up.
- Now apply the formula correctly without including the cell itself:
- Press ENTER and you will get the desired output.
That’s how we can overcome the problem of circular reference and update Excel Formulas automatically.
4. Checking Space before “Equal” Sign of Formula
For the same Dataset and the same result, we will now show how adding a Space ( “ “) before the equal sign hinders getting the output.
- Firstly, add space before applying the following Formula:
Here, the cell will not recognize the formula for adding a “space”.
- Remove the space before the “=” sign and press ENTER. You’ll get the desired output.
In this way, we can get the output by removing space before the “=” sign as it hinders getting the result.
5. Examine Show Formulas Button
If the Show Formulas button on the Formulas tab is turned on, the formulas won’t work. It shows the formula instead of ending the result. However, this Tab is very useful while troubleshooting formula glitches. Let’s check how to fix this. For our previous dataset and previous Formula.
- If applying the formula doesn’t give you the result and rather only shows you the formula, then go to the Formulas Tab and check whether the Show Formulas button is turned on.
- Turn the button off if it is on and then you will get your result.
In this way, we can get the output by turning off the Show Formulas button.
In this article, we have learned how to fix the problem of not updating formulas in Excel automatically. I hope from now on you can easily fix this to update the formulas automatically in an Excel workbook. If you have any queries regarding this article, please don’t forget to leave a comment below. Have a great day!