This sample dataset holds the income and tax to be paid by each person. Here, income and tax on income are two dependent variables.
Method 1 – Checking if Calculation Options is Set to Manual
Steps
- Go to the Formula tab in the ribbon.
- Select Calculation Options.
- In Calculation Options, select Automatic.
- 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.
Check 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.
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.
- Now, you can delete it or use the following code to get the automatic calculation.
Sub AutomaticCalculation()
Application.Calculation = xlCalculationAutomatic
End SubMethod 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.
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.
- Press Enter to apply the formula.
- The Excel formula will update changes automatically.
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.
- 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!
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?
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.
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?
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.