[Fixed!] Why Is My Excel Formula Not Updating Automatically (8 Solutions)

Sometimes you find a difficult situation in Excel while working, your Excel formula is not working automatically. That means even if you change the dependent cells, it’ll still show the previous result which is very painful. That can happen for many reasons. This article will break down all possible solutions for why your Excel formula is not updating automatically. I hope you enjoy the whole article and gain some valuable knowledge.


Download Practice Workbook

Download the practice workbook


8 Possible Solutions to Fix Excel Formula Not Updating Automatically

Excel Formula not updating automatically is a painful problem to have. But if you are aware of these problems occurrence and know how to fix them then you will not feel stressed that much. We have found eight possible solutions through which you can fix the Excel formulas not updating automatically. To show the solution, we take a dataset having income and tax to be paid by each person. Here, income and tax on income are two dependent variables.

Fix Excel Formula Not Updating Automatically


Solution 1: Change Calculation Options to Automatic

One of the main reasons not to update Excel formulas automatically is because you change the calculation from automatic to manual. You must find it confusing that is there any situation that appears when anyone needs manual calculation in Excel? The answer is Yes. When you are working with a large dataset, the automatic calculation may slow down your system. To change the calculation, follow the following steps.

Steps

  • First, go to the Formula tab in the ribbon.
  • Select Calculation Options.

Check Manual Calculation to Fix Excel Formula Not Updating Automatically

  • Next, in the Calculation Options, select Automatic.

Check Manual Calculation to Fix Excel Formula Not Updating Automatically

  • Now, you can change any variable, Excel formula will update it automatically.
  • Here, we alter the percentage of tax to be paid from 10% to 15%.


Solution 2: Use Show Formulas Command

Another reason for the Excel formula is not updating automatically if the Show Formula option is on. Show Formula button is used basically when you audit formulas instead of end result.

Steps

  • First, go to the Formula tab in the ribbon.
  • Uncheck the Show Formulas option from the Formula Auditing group.

Turn Show Formulas Option On to Fix Excel Formula Not Updating AutomaticallyNow, Check your Excel formulas whether it is updating or not.

Read More: [Fix:] Excel Formula Not Working Returns 0


Solution 3: Change Format of the Cells

Sometimes, you apply the formula in the formula box but set them as Text. In the Text formula, the Excel formula will not be updated automatically.

Steps

  • First, go to the Home tab in the ribbon.
  • Next, in the Number group, check the format.

  • If it is in Text format then change it to General.

After that, check the Excel formula whether it is updating or not.


Similar Readings


Solution 4: Check Circular Reference

Another reason can be the circular reference. Circular reference can be defined as a formula where it includes itself or refers to another cell that depends on itself.

Fix Excel Formula Not Updating Automatically

Circular reference can cause Excel to slow down. At the same time, it can cause not to update Excel formulas automatically. So, check the circular reference and then recalculate your datasheet.


Solution 5: Switch to Manual Calculation Using VBA

Sometimes, people use the Macro to convert into the manual calculation. The main reason for that is when someone uses Macro, Excel can’t apply the formula automatically. They use a code to convert automatic to manual.

Steps

  • First, check the VBAProject sheet or Module in Visual Basic.
  • To check it, open the developer tab by pressing Alt+F11.

Fix Excel Formula Not Updating Automatically

  • Now, you can delete it or use the following code in place of this to get the automatic calculation.
Sub AutomaticCalculation()
Application.Calculation = xlCalculationAutomatic
End Sub

Note:

This process is used for large datasets. People use manual calculation before applying any Macro but when they want to finish the process, they forget to change it to automatic mode. That actually brings the entire problem of the Excel formulas not updating automatically.

Read More: [Fixed!] Excel Not Responding When Running Macro (9 Possible Solutions)


Solution 6: Fix Issues with Equal Sign in the Formula

Sometimes we apply space before equal sign to apply formulas. It is one of the main reasons why we don’t get the result after applying the formula.

Steps

  • First, check the formula one by one.
  • Search any space before the equal (=) sign.

  • If you find any space, just delete it.
  • Next, press Enter to apply the formula.

  • Now, you can alter any variable, it will change the Excel formula value automatically.

Fix Excel Formula Not Updating Automatically

Now, sometimes we write formulas without giving any equal sign prior to it. In that case, Excel counts it as a normal text value. This is one of the main reasons why the Excel formula is not working or not updating automatically. So, follow the below steps to solve your issue.

Steps

  • Check your formula box one by one.
  • Search the equal sign before the formula.

Fix Excel Formula Not Updating Automatically

  • If you don’t find it then put the equal (=) sign.

Fix Excel Formula Not Updating Automatically

  • Press Enter to apply the formula.

. Now, you can alter any variable, it will change the Excel formula value automatically.

Remove Space Before Equal Sign


Solution 7: Remove Double Quotes

When you enter any formula that must contain a number or cell reference. But if you enter numbers with double quotes, it will count as a text value in Excel. In that case, your Excel formula won’t work and won’t update further.

Steps

  • Check the formula in the formula box if there is any double quote to enclose the number.

  • Delete those quotes and recalculate the formula by pressing Enter.

Now, your Excel formula will work and at the same time, it will update automatically.

Read More: [Fixed!] Excel File Not Opening on Double Click (8 Possible Solutions)


Solution 8: Separate Function Argument with a Proper Character

Most of us are accustomed to using commas to separate function arguments. This, however, does not apply to all Excel versions. In North America and a few other nations, the comma is the standard list separator. In European countries, the decimal symbol is a comma, while the list separator is frequently a semicolon.

Steps

  • Go to the Home tab in the ribbon.
  • Select Number Format from the Number group.

  • Next, select More Number Formats from the drop-down box.

Separate Function Argument with a Proper Character

  • Now, select Date from the Category.
  • Next, change the Location.
  • Finally, click on Ok.


Conclusion

We have shown nine different and effective solutions for Excel formulas not updating automatically. All of these solutions are easy but tricky. If you understand these carefully, you will not face any difficulties in the future. If you have any questions, feel free to ask in the comment box. Don’t forget to visit our Exceldemy page.


Related Articles

Durjoy Paul

Durjoy Paul

Hi there! I'm Durjoy. I have completed my graduation from the Bangladesh University of Engineering and Technology. I am working and doing research on Microsoft Excel and here I will be posting articles related to it.

2 Comments
  1. Hello, I added a sumcolor function in my VBA, and that function won’t update automatically when I change a cell’s color. Any suggestions?

  2. Hello Nathan,
    first of all, if you want to use sum color function, you can use the following code.

    Function SumByCol(SumRange As Range, SumColor As Range)
    Dim SumColValue As Integer
    Dim TotSum As Long
    SumColValue = SumColor.Interior.ColorIndex
    Set pCell = SumRange
    For Each pCell In SumRange
    If pCell.Interior.ColorIndex = SumColValue Then
    TotSum = TotSum + pCell.Value
    End If
    Next pCell
    SumByCol = TotSum
    End Function

    Then, close the VBA code and select any cell where you want to put the sum value. After that, write the following formula
    =SumByCol(C5:C10,C13)
    Where the range of cells C5 to C10 defines the range. C13 defines the color. You need to show only color in one cell. From where you can take the reference. Here, C13 denotes that color reference. So, when you want to change the color, you have to change the color reference also.
    null

Leave a reply

ExcelDemy
Logo