Sometimes you may find that the formulas in Excel are not updated automatically. Below are the steps to fix the problem that formulas are not automatically updated in Microsoft Excel.
Formulas are not automatically updated in Excel
As you can see in the image below, the formulas entered in column E are correct. However, the formulas do not return any results and are only displayed as text strings.
This unexpected behavior where users find the formula is not working in Excel and is shown in the form of a text string is usually due to the following reasons.
- The Show Formulas option is enabled in the worksheet.
- Calculations set to Manual
- Excel treats formula as text.
So, let’s go ahead and have a look at how to fix formulas not updating issue in Excel.
1. Uncheck Show formulas
The Show Formulas feature in Microsoft Excel lets you see all the formulas used in a worksheet so you can review and correct them as needed.
This function works like a toggle, when it’s on it shows the actual formulas used in the cells and when it’s off it shows the calculation results generated by the formulas.
You probably accidentally turned on formula mode, which causes formulas to appear in the worksheet.
The most common reason for accidentally turning on “Show Formulas” in Excel is due to user input control`which is actually a keyboard shortcut for Show Formula.
To turn off Show formulas, click formula tab in the top ribbon menu and then click show formulas Option in the Formula Audition section.
Now you should be able to see a normal worksheet with no formulas visible in the cells.
2. Calculations set to Manual
Another reason why formulas in Excel don’t update automatically is that the worksheet calculation option is set to manual mode.
To fix this, click formulas tab > calculation options > and then click on Automatically in the drop down menu.
Once the worksheet is set to automatic mode, you’ll see formulas update automatically and return results as you type them.
3. Excel treats formula as text
If you find that just a single formula or formulas aren’t working in a few cells, Excel is probably treating the formula in that particular cell or group of cells as text.
When Excel thinks a formula is text, it simply displays the formula that you type in the cells and doesn’t try to evaluate the formula.
There can be many reasons why Excel treats the formula as text. So let’s go ahead and rule out many of these reasons.
3.1. Formula enclosed in quotation marks
Make sure the formula is not enclosed in quotes (” “). If the formula is enclosed in quotation marks, Excel treats the formula as a text string.
This problem usually occurs when users copy formulas from websites that use double quotes (” “) to draw attention to the formula.
Click on that cell that contains the formula and check if the formula is enclosed in quotation marks.
Just remove the quotes and you will see the results of the formula.
Note: You can use quotation marks within the formulas, but do not enclose the formula in quotation marks.
3.2. No equal sign in the formula
When you enter a formula manually in Excel, the formula should always start with an equals sign (=).
If you forget to start your formula without an equal sign (=), Excel won’t be able to tell that you’re actually entering a formula.
3.3. Space before the equals sign
Another common reason for the formula not working in Excel is the presence of a space before the equal sign (=). As you can see in the image below, the formula that starts with a space before the equal sign is treated as text by Excel.
To fix this problem, click the cell contains formula > click in the formula bar and remove those place before the equals sign.
3.4. Cell format set to text
If everything about the formula appears correct and the formula still doesn’t work, it’s likely that the cell containing the formula is in text format.
Right click on the cell with the formula and click Format Cells… option in the drop down menu.
On the Format Cells screen, set the Format to General or a specific number format in which you want to display the result produced by the formula and click OK.
After changing the format, click in the formula bar and press the Enter key on your computer’s keyboard.
You should now see the formula update automatically and give you the result you want.