Excel When Entering a Formula, You Get the Formula Instead of the Result
This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
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.
Figure 299 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.
Figure 300 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. Press Ctrl+1; the Format Cells dialog will appear.
2. Confirm that the cell has a Text format assigned.
Figure 301 A format of Text will show the formula and not the result.
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.
Figure 302 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.