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

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

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

  1. 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.

  2. 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.

  3. Text formats will show the formula and not the results.
    1. Change the cell to any format other than Text.
    2. 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.

  4. 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.