Excel Showing Formula Instead of Result

Sometimes, it happens that instead of showing the result of a formula in Excel, you see the formula itself.

Below, I have an example where I have a formula in column C, and instead of showing the result of the formula, Excel shows the formula itself.

This is a pretty common occurrence with Excel users, and you can easily fix it.

In this article, I will cover possible reasons why Excel is showing the formula instead of the result and how to fix it.

Reason 1: Show Formulas Option is Enabled

Excel has an inbuilt ‘Show Formulas’ option that displays all the formulas in the cells instead of showing you the result of those formulas,

The most common reason that your Excel file is showing formulas instead of values is that this option may be enabled.

How to fix this?

Here are the steps to disable the show formulas option in Excel:

1. Click the Formulas tab in the ribbon
2. Click on the Show Formulas option in the Formula Auditing group.

If this issue were because the Show Formulas option was enabled, the above steps would disable it, and you would now see the results of the formulas instead of the formulas themselves.

Note: When the show formulas option is enabled, you will see that option in the ribbon in a slightly darker shade of gray (indicating that it is currently active). It works as a toggle where when you click on it, it gets enabled, and when you click on it again, it gets disabled.

Pro Tip: You can also use the keyboard shortcut Control + ~ to enable or disable the show formulas option. To use this, hold the central key and then press the ~ key.

When the Show Formula option is enabled, it is applied to the entire worksheet. So, all the formulas in the worksheet would be shown as formulas instead of the results when it is enabled.

If this doesn’t solve your problem, let’s try the other methods.

Also read: Show Formulas in Excel Instead of the Values

Reason 2: Cells with Formulas are Formatted as Text

The cells in a worksheet in Excel can be formatted in different ways to show the value in the cell differently.

For example, you can format the cells to show the numbers, dates, text values, currency, accounting numbers, etc.

When the cells are formatted as text, and then you enter a formula in that cell, Excel shows you the formula itself instead of showing you the result of the formula.

This is because the back end of that cell expects to receive a text value and not a formula.

Below is an example where I have some formulas in column D, and you can see that it shows the formula in the cell as the format of the cell is set to Text.

How to fix this?

I’m sure you’ve already guessed it – you need to change the cell format so that it can start showing the formula results.

Here are the steps to do this:

1. Select the cells for which you want to change the cell format.
2. Click the Home tab in the ribbon.

3. In the Number group, click on the Formatting drop-down.

4. Select the General Option

The above steps would change the format of the selected cells to General, which means that now if you enter a formula in these cells, it will show you the result of the formula.

But what about those cells that already had formulas before we made the change of cell formatting to General?

In those cells, you would continue to see the formula instead of the formula result.

To correct this, you need to select the cell that is showing the formula, press the F2 key (to get into the edit mode), and then press the Enter key. Doing this forces the cell to recalculate the formula and show you the result.

Alternatively, you can select all the cells that have the formula that is showing as text, then press the F2 key to get into the edit mode, and then hold the Control key and press the Enter key to convert all the formulas into their result in one go.

Also read: How to Convert Formulas to Values in Excel

Reason 3: Formula Equal-To Sign is Missing

Another common issue could be a missing equal-to sign (=) at the beginning of the formula.

Every formula in Excel needs to start with an equal-to sign. This sign tells Excel to interpret anything following it as a formula.

And if a formula does not start with an equal-to sign, Excel would treat it as regular text.

This is why, if the equal-to sign is missing, Excel will simply display the formula as is without performing any calculations.

How to fix this?

The fix is simple – add the equal-to sign before the formula:

• Select the cell where the formula is being displayed as text.
• Click into the formula bar or double-click directly on the cell to edit it.
• Add an equal-to sign (=) at the beginning of the formula. Ensure there are no spaces or other characters before the = sign.
• After adding the equal-to sign, press Enter. Excel should recognize the formula and display the calculated result instead of the formula text.

Also read: How to Hide Formulas in Excel (and Only Display the Value)

Reason 4: Apostrophe or Space Character Before the Formula

In Excel, when you add an apostrophe (‘) or a leading space at the beginning of a cell’s content, it tells Excel to treat the cell content as text.

This is useful when you want to display numbers or formulas as literals (for example, to show a phone number without formatting or a formula for instructional purposes).

However, if you unintentionally put an apostrophe or a space character before a formula, Excel will not recognize it as a formula and instead treat it as a text string.

Instead, it will display the formula text as-is.

Below is an example where I have a proper formula in cell A1, a formula with a leading apostrophe in cell A2, and a formula with a leading space character in cell A3.

Note that while there is an apostrophe in cell D2, you don’t see that in the cell itself. However, when you look at the formula bar, you will notice the apostrophe before the equal to sign.

How to fix this?

Here are the steps to fix this:

• Select the cell where the formula is being displayed as text.
• Click into the formula bar or double-click directly on the cell to edit it.
• Delete any apostrophe (‘) or space that is before the equal-to sign (=). Make sure the formula begins exactly with the equal-to sign.
• Press the Enter key after making the changes. Excel should now show you the formula result instead of showing the formula text.

Also read: How to Copy and Paste Formulas in Excel without Changing Cell References

Reason 5: Formula is Wrapped in Quotes (or Double Quotes)

Just like having an apostrophe before the formula, when a formula in Excel is wrapped in quotes (or double quotes), it causes the formula to be treated as a text string.

While sometimes this may be done intentionally (when you want to display the formula text for instructional or documentation purposes), it could also happen when you get your Excel files from the web or from databases.

Unlike an apostrophe, this is easier to spot as you can see the extra quotes in the cell itself.

How to fix this?

This can be fixed by simply removing the quotes or double quotes around the formula and making the formula recalculate.

Here are the steps to do this:

• Select the cell in which the formula is being displayed as text.
• Click into the formula bar or double-click on the cell to edit it.
• Remove the quotes (”) or double quotes (“”) surrounding the formula. Ensure that the formula starts with an equal-to sign (=) and has no text characters before it.
• Press the Enter key after making the changes.

Also read: Highlight Cells With Formulas in Excel

Reason 6: FORMULATEXT is Being Used

The FORMULATEXT function in Excel is designed to display the formula used in a referenced cell as a text string. This function is particularly useful for auditing or documenting the formulas in your spreadsheet.

When you use FORMULATEXT, Excel will show the exact formula contained in the referenced cell, not the result of that formula.

For example:

• If cell A1 contains the formula =SUM(B1:B20)
• Using =FORMULATEXT(A1) in another cell will display =SUM(B1:B20) as text.

How to fix this?

If you want to display the result of a formula instead of the formula itself, you should not use FORMULATEXT.

Instead, you can directly reference the cell containing the formula.

Other Excel articles you may also like: