How to Fix Formula Not Working in Excel

Have you just tried entering a formula in Excel, only to find that it displays as a text string instead of producing a result? Below are the steps to fix the formula not working issue in Excel.

Formula not working in Excel

As you can see in the image below, the formulas entered into the cells are correct. However, the formulas do not return any result and are only displayed as text strings.

Formulas displayed as text strings in Excel

This unexpected behavior in Excel 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 the steps to fix the formula not working issue in Excel.

1. Uncheck Show formulas

The Show Formulas feature in Excel allows you to view all the formulas used in the worksheet so that 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 the Show Formulas option in Excel is due to user input control` which is actually a keyboard shortcut for Show Formula.

1. Choose formula tab in the top menu bar and 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 the results of the formulas visible in the cells.

2. Enable automatic calculations

It is possible that formulas do not work because the worksheet calculation option is set to manual mode.

1. Choose formulas Tab in the top menu bar > click calculation options and select that Automatically option in the drop down menu.

Set calculations in Excel to automatic mode

Once calculations are set to automatic mode, you’ll see formulas return results as soon as you type them.

3. Excel treats formula as text

If you find that a single formula or formulas are not working in some cells, the problem is not because the Show Formulas option is enabled.

In this case, the problem is that Excel is treating the formula in a specific cell or group of cells as text.

When Excel treats a formula as text in any cell, it simply displays whatever the formula was entered or typed into the cells and doesn’t attempt 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 quotation marks (” “). 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 along with the quotation marks (” “) used to emphasize the formula.

1. Click in the 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 can see how the formula works and gives the desired result.

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 a 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 place 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 issue, select the cell with Excel 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.

1. Right click on the cell contains formula and select Format Cells… option in the drop down menu.

Format cell with formula

2. On the Format Cells screen, set the Format to General or to 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

3. After changing the number format, select that cell Included formula > 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 working and delivering results.

Related

Leave a Reply

Your email address will not be published.