[Fixed:] Excel Spreadsheet Formulas Not Updating Automatically

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.

Excel Spreadsheet Formulas not updating Automatically

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.

Not Updating Excel Spreadsheet Formulas Automatically

So, let’s start the procedure.

  • First, apply the Formula
=SUM(C5:C10)

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.

Excel Formulas Not Updating Automatically

  • Change the format from Text to Currency.

Excel Formulas Not Updating Automatically

  • 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:
=SUM(C5:C11)

Here,

  • C11 = the cell where you want to get the result

dataset for checking not to update Excel Formulas

  • 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:
=SUM(C5:C10)

  • 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:
' =SUM(C5:C10)’

Here, the cell will not recognize the formula for adding a “space”.

Not updating Excel formulas in spreadsheet

  • 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.


Conclusion

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!

Rafi

Rafi

Hey there! I am Md. Rafiul Hasan. Currently I am working as an Excel & VBA content developer. I like new ideas and want to explore the field of innovation. Excel has saved our worktime and made it easy for us to quick calculations. I am trying to make it easier for you to overcome the obstacles you face while working on Excel. Stay connected!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo