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.
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.
- 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%.
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.
- 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.
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.
- 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.
- [Fixed!] Unable to Open Excel Files Directly by Clicking on the File Icon
- How to Fix Excel Not Responding Without Closing (16 Possible Solutions)
- [Fixed!] Excel Not Responding When Deleting Rows (4 Possible Solutions)
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.
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.
- 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 Sub
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.
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.
- 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.
- 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.
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.
- 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.
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.
- 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.
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.
- [Fixed!] Excel Keeps Crashing When Opening File (11 Possible Solutions)
- [Fixed!] Excel Freezes When Copying and Pasting (13 Possible Solutions)
- How to Open Excel in Safe Mode (3 Handy Methods)
- [Fix:] Excel File Opens but Does Not Display
- [Fixed!] Excel Attachments Not Opening from Outlook (6 Quick Solutions)
- Fix Excel Not Responding and Save Your Work