Every user will agree that Microsoft Excel is one of the most useful tools in the antiquity of modern computing. Every single day, millions of people use Microsoft Excel spreadsheets to perform a plethora of tasks, starting from the simple ones like maintaining journal entries or records to advanced data analysis based on complex formulas. Irrefutably, Excel Formulas play a vital role in data processing. But there are times you may find that the Excel Formulas are not updating automatically.
Excel Formulas not updating automatically
It can be very frustrating when your formulas are not updating automatically on Excel. Building on that, here are the possible reasons why it may be happening. In this post, we explain four such scenarios:
- The calculation is configured to “Manual”
- The cell is formatted as Text
- Show Formulas button is turned on
- Space entered before the Equal sign.
Let us see each of them in details.
1] Calculation is configured to “Manual”
This is one of the most common reasons and the first check that you need to perform. Sometimes, the calculation option is configured to “manual,” and this is the main glitch causing the cells not to update the formulas in Microsoft Excel. You can fix this problem.
Launch Excel, Click the “Formulas Tab” and then the “Calculation Options” button. Now set the calculation option to “Automatic”.
This setting can be altered by macros, or by other excel workbooks opened earlier. Hence, if you aren’t aware of this setting, it could possibly be causing this problem.
2] The cell is formatted as Text
Accidentally formatting the cells including formulas as “Text” could be another issue causing this problem. When in Text format, the cell will not calculate as intended to.
Click on the cell and check the Number group of the Home tab. If it displays “Text”, alter it to “General” using the drop-down menu.
Now, re-calculate the formula in the cell by double-clicking on the cell and hit “Enter”.
3] Show Formulas button is turned on
If the “Show Formulas” button on the Formulas tab is turned on, the formulas do not work. This can be done accidentally by the user, or by someone who would be using this workbook previously. This button is primarily used when auditing formulas, hence it displays the formula instead of the end result. However, this tab is very helpful when troubleshooting formula glitches.
To fix this, simply click the “Show Formulas” button and Turn it Off.
4] Space entered before the Equal sign
The final reason could be, while typing formula if you mistakenly enter a space before the “Equal (=)”, hence the formula will not calculate. This one mistake is quite difficult to notice, hence very often goes unrecognized.
Check if there is a space by Double-clicking the cell if so, delete it and the formula will update.
I hope these tips helps many of you solve your problem with Excel formulas not updating automatically. Regular Excel users will come across these issues more than once, but now you can tackle them well enough!