See all Microsoft Excel tips
Strategy: There are three possibilities.
Possibility 1: As shown in Fig. 412, you forgot to start the formula with the Equal sign.
1) Select the cell, hit F2 to edit the cell, as shown in Fig. 413.
2) Hit home to move cursor to start of formula, as shown in Fig. 414.
3) Hit Delete key to delete the leading apostrophe. Type the = sign, as shown in Fig. 415.
4) Hit Enter to enter the formula, as shown in Fig. 416.
Gotcha: Typically, in a cell with general formatting, Excel will right align numbers and left-align text. Because you forgot to hit the Equal sign, Excel thinks you have a text cell and even after converting to a valid formula, the result might be left-aligned. Hit the Align Right icon in the Formatting toolbar, as shown in Fig. 417.
Possibility 2: The cell might have been assigned a numeric format of “@”, which is the code for a text cell. The maddening part 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.
1) Here’s how to fix this. Select the cell. Hit Ctrl+1 to show the Format Cells dialog. Confirm that the cell has a Text format assigned, as shown in Fig. 418.
2) As shown in Fig. 419, change to a numeric format.
3) This does not fix the formula! You can now edit the cell using the F2 key. As shown in Fig. 420, note that Excel added an apostrophe before the formula.
4) Hit the Home key to move to the beginning of the formula and the Delete key to delete the apostrophe. Hit Enter to accept the formula, as shown in Fig. 421.
5) The cell alignment is left-aligned because you entered a value while the cell was formatted as Text. Use the Right-Align icon on the Formatting toolbar to correct this.
Possibility 3: The third possibility, although the least likely, is that you are in Show Formulas mode, as shown in Fig. 422. In this mode, all of the cells that have formulas would be showing their formulas.
1) Hit CTRL+~ to toggle in and out of Show Formulas mode.
Summary: The three possible options to fix a formula that shows when you actually want the results to show in a cell are: (1) check to make sure you started the formula with an Equal (=) sign, (2) make sure the cell is formatted for numeric and, lastly, (3) make sure the worksheet is not in Show Formula mode.
Commands Discussed: F2 to enter edit mode; CTRL+~ for Show Formula mode
See all Microsoft Excel tips
