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

This sample dataset holds the 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


Method 1 – Checking if Calculation Options is Set to Manual

Steps

  • Go to the Formula tab in the ribbon.
  • Select Calculation Options.

Check Manual Calculation to Fix Excel Formula Not Updating Automatically

  • In Calculation Options, select Automatic.

Check Manual Calculation to Fix Excel Formula Not Updating Automatically

  • Now, when a variable is changed, any Excel formula in use will update automatically.
  • Here, the percentage of tax to be paid is changed from 10% to 15% which automatically updates the relevant column.


Method 2 – Show Formulas Option Prevents Excel Formula from Updating Automatically

 

Steps

  • 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 AutomaticallyCheck if the Excel formula are updating or not.


Method 3 – Incorrect Cell Format Blocks Formula to Update Automatically

The formula will not update automatically if the Number format is set to Text.

Steps

  • Go to the Home tab in the ribbon.
  • In the Number group, check the format.

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

Check if the Excel formulas are updating or not.


Method 4 – Circular References in Formula Prevents Excel from Updating

Circular references can be defined as a formula that includes itself or refers to another cell that depends on it.

 

Fix Excel Formula Not Updating Automatically

Circular references can cause Excel to slow down and prevent Excel formulas automatically updating. Check the circular reference and then recalculate your datasheet.


Method 5 – Excel Formula Not Updating Due to Manual Calculation Mode

When Macro is used, Excel can’t update the formula automatically.

Steps

  • Check the VBAProject sheet or Module in Visual Basic by pressing Alt+F11.

Fix Excel Formula Not Updating Automatically

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

Method 6 – A Space Before Equal Sign in Formulas Blocks Automatic Updating

 

Steps

  • Check any active formulas one by one for a space before the equal (=) sign.

  • If you find any space delete it.
  • Press Enter to apply the formula.

  • The Excel formula will update changes automatically.

Fix Excel Formula Not Updating Automatically

If a formula is written without an equal sign, 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.

Steps

  • Check your formula box one by one.
  • Ensure there is an equal sign before every formula.

Fix Excel Formula Not Updating Automatically

 

Fix Excel Formula Not Updating Automatically

  • Press Enter to apply the formula.

  • The Excel formula will update changes automatically.

Remove Space Before Equal Sign


Method 7 – Excel Treats Formula with Numbers in Double Quotes As Text and Will Not Update Automatically

 

Steps

  • Check in the formula box if there are any double quote mark around the number.

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

  • The Excel formula will update changes automatically.

Method 8 – Using the Wrong Character to Separate Function Arguments Prevents Automatic Updates

Not every country uses the same symbol to separate lists.

Steps

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

  • Select More Number Formats from the dropdown box.

Separate Function Argument with a Proper Character

  • Select Date from the Category.
  • Change the Location.
  • Click Ok.


Download Practice Workbook

Download the practice workbook

 


<< Go Back to Excel Formula Errors | Excel Formulas | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Durjoy Paul
Durjoy Paul

Durjoy Kumar, with a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, is a dedicated contributor to the ExcelDemy project. His substantial contributions include crafting numerous articles and demonstrating expertise in Excel and VBA. Durjoy adeptly automates Excel challenges using VBA macros, offering valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, SPSS, C, C++, C#, JavaScript, Python Web Scraping, Data Entry... Read Full Bio

4 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

  3. Hi, I have a spreadsheet on Sharepoint that will not calculate some forumale. When we open the document in the app it all works fine – it’s ONLY on Sharepoint. Since 4-5 people need to access to the file – often 2-3 at once, we need it on Sharepoint. Any ideas why it’s ONLY online that we have an issue?

  4. Hello Craig,
    We are not dealing with SharePoint at this moment. So, I can’t give you a proper solution to this problem. In the future, if we work with SharePoint, we will get back to you and provide you with a useful solution.
    Thanks.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo