Why Are Formulas Displayed Instead of Calculated in Excel? Causes and Solutions

スポンサーリンク
スポンサーリンク

Why Are Formulas Displayed Instead of Being Calculated?

When you enter a formula in Excel, you may encounter a situation where the formula itself is displayed in the cell instead of its calculated result. For example, instead of displaying 15, Excel may show "=SUM(A1:A5)".

This issue is often caused by the "Show Formulas" setting, a cell formatted as text, or an unintended character before the formula. Calculation settings can also cause formulas to display outdated results, although this is a different problem.

This article explains the possible causes and how to resolve them.

Cause 1: "Show Formulas" Is Enabled

Excel has a feature called Show Formulas that displays formulas in worksheet cells instead of their calculated results.

When this feature is enabled, Excel shows formulas such as "=SUM(A1:A5)" directly in the cells. However, the formulas themselves are still recognized and calculated by Excel.

If formulas appear in multiple cells, check the "Show Formulas" setting first.

Solution

  1. Click the Formulas tab in Excel.
  2. Find the Formula Auditing group.
  3. Click Show Formulas to turn it off if it is currently enabled.
  4. The cells should now display their calculated results instead of the formulas.

You can also use the keyboard shortcut Ctrl + ` (grave accent) to toggle between displaying formulas and calculated results. The location of this key may vary depending on your keyboard layout.

If only one or a few cells display formulas while other cells show calculated results normally, the cause is more likely related to the formatting or contents of those particular cells.

Cause 2: The Cell Is Set to "Text" Format

One of the most common causes is that the cell containing the formula is formatted as Text.

When you enter a formula into a cell that is already formatted as Text, Excel treats the entry as a text string rather than interpreting it as a formula.

For example, if you enter:

=SUM(A1:A5)

Excel may display the formula itself instead of calculating the sum.

Formulas entered into cells formatted as Text are treated as text rather than calculated.

Solution

  1. Select the cell containing the formula.
  2. Click the Home tab.
  3. In the Number group, change the cell format from Text to General.
  4. Press F2 to edit the formula.
  5. Press Enter to confirm the formula again.

The formula should now be recognized and calculated normally.

Important: Simply changing the cell format from Text to General may not immediately convert an existing text entry into a working formula. You usually need to re-enter the formula or press F2 followed by Enter.

When Multiple Cells Are Affected

If a large range contains formulas stored as text, manually editing each cell can be time-consuming.

In Excel for Windows, you can try the following method:

  1. Select the cells containing formulas stored as text.
  2. Change their number format to General.
  3. Click the Data tab.
  4. Select Text to Columns.
  5. Proceed without changing the default delimiter settings, ensuring that the column data format is General.
  6. Click Finish.

This can cause Excel to reinterpret text entries beginning with an equals sign as formulas.

Because Text to Columns may also change other data types, use this method only on the intended range and test it on a copy of the worksheet first.

Cause 3: A Leading Space Before the Formula

If a space is entered before the equals sign, Excel may treat the entry as text rather than a formula.

For example:

=SUM(A1:A5)

In this example, a space appears before the equals sign. As a result, the entry may be displayed as text instead of being calculated.

Solution

  1. Select the cell containing the formula.
  2. Click the formula bar or press F2 to edit the cell.
  3. Check whether any spaces or other unintended characters appear before the equals sign.
  4. Remove those characters so that the formula begins with =.
  5. Press Enter to confirm the formula.

For example, the formula should begin exactly as follows:

=SUM(A1:A5)

If the cell is also formatted as Text, change its format to General before re-entering the formula.

Cause 4: A Leading Apostrophe (') Before the Formula

In Excel, entering an apostrophe (') before a value forces Excel to treat the entry as text.

For example:

'=SUM(A1:A5)

This entry is treated as text even though it contains a valid formula after the apostrophe.

The leading apostrophe is normally not displayed in the worksheet cell, but it can be seen in the formula bar when the cell is selected.

This behavior is useful when you intentionally want to display a formula as text, but it can also cause confusion if the apostrophe was entered accidentally.

Solution

  1. Select the cell containing the formula.
  2. Check the beginning of the entry in the formula bar.
  3. If an apostrophe appears before the equals sign, remove it.
  4. Make sure the entry begins with =.
  5. Press Enter to confirm the formula.

After removing the apostrophe, Excel should calculate the formula normally, provided the cell is not formatted as Text and Show Formulas is disabled.

Cause 5: Calculation Mode Is Set to "Manual"

Excel normally recalculates formulas automatically when referenced values change. However, if the calculation mode is set to Manual, formula results may not update until recalculation is requested.

Manual calculation does not normally cause formulas themselves to appear as text. Instead, the cells generally continue displaying calculated results, but those results may be outdated.

For example, suppose A1 contains 10 and B1 contains:

=A1*2

The result in B1 is 20.

If calculation mode is Manual and you change A1 from 10 to 15, B1 may continue displaying 20 until the workbook is recalculated.

Solution

  1. Click the Formulas tab.
  2. In the Calculation group, select Calculation Options.
  3. Choose Automatic.
  4. Excel will then automatically recalculate formulas when their dependent values change.

Alternatively, in Excel for Windows, you can change the calculation mode through:

File → Options → Formulas → Workbook Calculation → Automatic

If you want to recalculate formulas manually, use the following shortcuts in Excel for Windows:

  • F9: Recalculates formulas requiring calculation in all open workbooks.
  • Shift + F9: Recalculates formulas requiring calculation in the active worksheet.
  • Ctrl + Alt + F9: Forces recalculation of all formulas in all open workbooks.

In desktop Excel, the calculation mode can affect other open workbooks, so consider this when working with multiple files.

If the cell displays the formula expression rather than an outdated result, check the Show Formulas setting and cell formatting first.

Cause 6: The Formula Does Not Begin with an Equals Sign

Excel formulas normally begin with an equals sign (=). This tells Excel that the entry should be evaluated as a formula.

If the equals sign is missing, Excel generally treats the entry as ordinary text or another type of value.

For example, entering:

SUM(A1:A5)

Does not perform a calculation because the entry does not begin with an equals sign.

The correct formula is:

=SUM(A1:A5)

Solution

  1. Select the cell containing the entry.
  2. Click the formula bar or press F2.
  3. Check whether the entry begins with an equals sign (=).
  4. If the equals sign is missing, add it to the beginning of the formula.
  5. Press Enter to confirm the formula.

If the formula still does not calculate, check whether the cell format is Text or whether Show Formulas is enabled.

Cause 7: Incorrect Formula Syntax

If the formula contains invalid syntax, Excel may be unable to evaluate it correctly.

Common mistakes include incorrect function names, missing parentheses, invalid cell references, and incorrect argument separators.

However, syntax errors do not normally cause a formula to be displayed as plain text. Excel may instead display an error message, reject the formula, or return an error value such as #NAME? or #VALUE!.

Solution

  1. Check that the formula begins with an equals sign (=).
  2. Verify that function names are spelled correctly.
  3. Check that parentheses are properly matched.
  4. Verify that cell references and operators are correct.
  5. Check that the argument separators match your Excel regional settings.
  6. Correct the formula and press Enter.

For example, the following formula is valid:

=SUM(A1:A5)

But the following formula contains an incorrect function name:

=SUMM(A1:A5)

Excel may return a #NAME? error because SUMM is not a recognized function.

In this case, the formula is being interpreted as a formula, but Excel cannot evaluate the unrecognized function name.

Additional Checkpoints

If formulas still do not behave as expected, consider the following points:

  • Only one cell displays a formula:
    Check whether that cell is formatted as Text, contains a leading apostrophe or space, or has an incorrectly entered formula.
  • Many cells display formulas:
    Check whether Show Formulas is enabled. This feature affects the display of formulas throughout the worksheet.
  • Formula results do not update:
    Check whether calculation mode is set to Manual. This causes outdated results rather than normally displaying formula expressions.
  • The formula returns an error value:
    If Excel displays #VALUE!, #NAME?, #REF!, or another error, the entry is generally being interpreted as a formula. Investigate the specific error rather than treating it as a formula display problem.
  • Referenced cells contain invalid data:
    Text, errors, or unexpected values in referenced cells may affect calculation results. However, these problems do not normally cause the formula expression itself to appear in the cell.
  • The formula was copied from another application:
    Formulas copied from websites, documents, or text editors may contain leading spaces, apostrophes, or other unintended characters. Check the beginning of the entry and remove any unnecessary characters.
  • The formula is visible only in the formula bar:
    This is normal Excel behavior. When a cell contains a working formula, the formula bar displays the formula while the worksheet cell normally displays its calculated result.

Conclusion

When Excel displays formulas instead of calculated results, the most common causes are the Show Formulas setting, cells formatted as Text, or unintended characters before the equals sign.

If formulas appear throughout the worksheet, first check Formulas → Show Formulas. If only a specific cell is affected, change its format to General and re-enter the formula using F2 followed by Enter.

Also check for leading spaces or apostrophes that may cause Excel to treat formulas as text.

Manual calculation mode and incorrect formula syntax can also prevent formulas from producing the expected results, but these issues are different from displaying a formula as plain text.

By identifying whether Excel is displaying formula text, showing an error, or failing to update an existing result, you can choose the appropriate solution and restore normal formula behavior.