Fix for formulas not updating automatically in Excel

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.

Formulas displayed as text strings in Excel

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.

  1. The Show Formulas option is enabled in the worksheet.
  2. Calculations set to Manual
  3. 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.

Show formulas option in Excel

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.

Set calculations in Excel to automatic mode

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.

Excel formula enclosed in quotes

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 (=).

Excel formula without equal 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.

Space before formula in 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.

Format cell with formula

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.

Set the cell format to General

After changing the format, click in the formula bar and press the Enter key on your computer’s keyboard.

Formula bar in Excel

You should now see the formula update automatically and give you the result you want.

Related

Leave a Reply

Your email address will not be published.