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.
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.
1. Excel Formula Not Updating Automatically Due to Calculation Options Set to Manual
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.
- Next, in the Calculation Options, select Automatic.
- 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%.
2. Enabled Show Formulas Option Prevents Excel Formula from Updating Automatically
Another reason for the Excel formula is not updating automatically if the Show Formulas option is on. The Show Formulas 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.
Now, Check your Excel formulas whether it is updating or not.
3. Incorrect Cell Format Blocks Formula to Update Automatically
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.
4. Circular References in Formula Prevents Excel from Updating
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.
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.
5. Excel Formula Not Updating Due to Manual Calculation Mode
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.
- 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 SubNote:
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.
6. Using Space Before Equal Sign in Formulas Blocks Automatic Updating
Sometimes we apply space before the 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.
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.
- If you don’t find it then put the equal (=) sign.
- Press Enter to apply the formula.
. Now, you can alter any variable, it will change the Excel formula value automatically.
Read More:Â [Fix:] Excel Formula Not Working Returns 0
7. Excel Treats Formula with Numbers in Double Quotes As Text and Will Not Update Automatically
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.
8. Using the Wrong Character to Separate Function Arguments in Excel Formula Prevents from Updating Automatically
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.
- Now, select Date from the Category.
- Next, change the Location.
- Finally, click on Ok.
Download Practice Workbook
Download the practice workbook
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.
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.