MrExcel.com - Your One Stop for Excel Tips & Solutions

When Entering a Formula, You Get the Formula Instead of the Result

January 21, 2022 - by

When Entering a Formula, You Get the Formula Instead of the Result

Problem: When entering a formula, Excel shows me the formula in the cell instead of the result.

You type =C9/(C9+C10). You don't get the answer. You get the formula.
Figure 287. Excel displays the formula.

Strategy: There are three possible problems in this case.

Possibility 1: You may have forgotten to start the formula with an equals sign.

Perhaps you forgot to start the formula with the equals sign. Type C9/(C9+C10) without an equals sign and Excel thinks you are entering text.
Figure 288. You forgot to start the formula with an equals sign.

Follow these steps to correct the formula:

1. Select the cell and press F2 to edit the cell.

  • 2. Press the Home key to go to the beginning of the formula.

  • 3. If there see a hidden apostrophe, delete it using the Delete key.

  • 4. Type the = sign.

  • 5. Press Enter. Excel shows the result.

Possibility 2: The cell might have been assigned the numeric format @, which is the code for a text cell. The maddening part of this problem is that this format can get set even without you knowing it. A column can inherit a text format if you import a text file and use the text setting for the import. Here’s how you fix this problem:

  • 1. Select the problematic cell. Look in the Number group in the Home tab of the ribbon.

  • 2. Confirm that the cell has a Text format assigned.

Another cause: if the cell is formatted as Text, then the formula will appear instead of the result.
Figure 289. Text formats will show the formula and not the results.

  • 3. Change the cell to any format other than Text.

  • 4. This does not fix the formula! Edit the cell using the F2 key and then press Enter.

Possibility 3: The third possibility, which is the least likely, is that you are in Show Formulas mode, as shown here. In this mode, all the cells that have formulas show their formulas.

Another cause: in Show Formulas mode, all of the formulas will appear instead of the values. If your worksheet only has one formula, this might be the cause.
Figure 290. See all formulas.

To fix this problem, you press Ctrl+` to toggle in and out of Show Formulas mode. (On U.S. keyboards, this character is below the Esc key, on the same key as the tilde.)

When a cell shows a formula rather than a result, there are three possible reasons: (1) You forgot to start the formula with an equals (=) sign, (2) the cell is not formatted for numeric data, or (3) the worksheet is in Show Formula mode.

This article is an excerpt from Power Excel With MrExcel

Title photo by Dan-Cristian Pădureț on Unsplash