When Excel shows a formula as text instead of its result, the cell is formatted as Text or the formula starts with an apostrophe ('=B2*C2) or a space. Remove the apostrophe or space, or set the cell to General, then click into the cell and press Enter. If every formula on the sheet shows as text, Show Formulas is on: press Ctrl+` to turn it off. When results do not change as you edit numbers, calculation is set to Manual: choose Formulas > Calculation Options > Automatic.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Price | Qty | Total |
| 2 | Desk | 240 | 2 | =B2*C2 |
| 3 | Chair | 85 | 4 | =B3*C3 |
| 4 | Lamp | 40 | 3 | 120 |
D2 and D3 show the formula itself. D2 starts with an apostrophe, which tells Excel "this is text" and is hidden in the cell (you see it in the formula bar). D3 starts with a space before the =. Only D4 is a formula, and it shows 120. Click D2, delete the apostrophe so the cell holds =B2*C2, and press Enter: it shows 480.
The cell is formatted as Text
A cell formatted as Text keeps whatever you type exactly as typed, formulas included. This happens when a column was set to Text to keep leading zeros, or when an import set the column type to Text.
- Select the cells and choose Home > Number Format > General (the drop-down in the Number group).
- Changing the format is not enough: Excel only reads a formula when it is entered. Click into each cell (or press F2) and press Enter.
- For a whole column, select it and use Data > Text to Columns > Finish. That re-enters every cell at once.
The same steps fix numbers that were typed into Text cells, which then do not add up (see the section after next).
Show Formulas is on
If every formula on the sheet shows its text, Show Formulas is turned on: Formulas > Show Formulas, or Ctrl+ (the key under Esc; Control+ on a Mac). Press it again to switch back. It is easy to press by accident.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Price | Qty | Total |
| 2 | Desk | 240 | 2 | 480 |
| 3 | Chair | 85 | 4 | 340 |
| 4 | Lamp | 40 | 3 | 120 |
| 5 | Total | 940 |
Click Formulas above this sheet: every cell shows its formula, just like Excel's Show Formulas. Click it again to see 480, 340, 120 and the total, 940. The mode belongs to the sheet, not the workbook, so other sheets can still show results.
Numbers stored as text: SUM returns 0 or too little
The formula is fine, but some of the numbers it adds are text. SUM skips text without any error, so the total is just too low.
| A | B | C | |
|---|---|---|---|
| 1 | Month | Sales | Type |
| 2 | Jan | 120 | number |
| 3 | Feb | 80 | text |
| 4 | Mar | 95 | number |
| 5 | Apr | 60 | text |
| 6 | |||
| 7 | Total | 215 |
B7 shows 215, the sum of only January and March. In Excel, text numbers are aligned to the left of the cell and carry a green triangle; select them, click the warning icon and choose Convert to Number. If the data is refreshed from a source you cannot change, convert inside the formula, as in the task at the end of this page. The text to number page has every method.
Calculation is set to Manual
In Manual mode, formulas keep their old results until you recalculate. The sheet looks fine, but the numbers are out of date, and a formula copied down shows the first row's result in every row.
- Switch back: Formulas > Calculation Options > Automatic.
- Recalculate once without switching: F9 for all open workbooks, Shift+F9 for the active sheet, Ctrl+Alt+F9 to recalculate every formula. On a Mac, Cmd+= and Shift+Cmd+=.
- Automatic Except for Data Tables also leaves data tables (What-If Analysis) stale.
Manual mode is contagious: Excel takes the mode from the first workbook opened in a session, so a file saved in Manual mode switches everything opened after it. Very large workbooks are sometimes set to Manual on purpose to stop a recalculation after each edit; the status bar then shows Calculate when results are out of date.
A formula that calculates, but not what you expect
Sometimes the complaint is "the formula does not work" while Excel is calculating exactly what was written. Three common cases:
- A reference that moved when the formula was copied. A rate in one cell, read without
$, slides down with the formula, and the rows below multiply by empty cells. - A circular reference. The formula depends on its own result, Excel shows 0 and a warning in the status bar. See circular reference.
- An error name instead of a number, such as
#NAME?for a misspelled function. Each error has its own page in this chapter.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Item | Price | With tax | Tax rate | ||
| 2 | Desk | 240 | 288 | 20% | ||
| 3 | Chair | 85 | 85 | |||
| 4 | Lamp | 40 | 40 |
D2 is right (288), but D3 shows 85 and D4 shows 40: their formulas read F3 and F4, which are empty, so the tax is 0. Click D2, change F2 to $F$2, and the column follows. Absolute references explain why.
Total numbers stored as text
| A | B | |
|---|---|---|
| 1 | Month | Sales |
| 2 | Jan | 120 |
| 3 | Feb | 80 |
| 4 | Mar | 95 |
| 5 | Apr | 60 |
| 6 | Total |
Your turn: February and April were imported as text, so =SUM(B2:B5) misses them. Write a formula in B6 that adds all four months.
-- (two minus signs) turns each value into a number: text that looks like a number is converted, real numbers stay as they are. =SUMPRODUCT(B2:B5*1) and =SUM(VALUE(B2:B5)) work the same way. Once the formula is right, it keeps working however many of the cells are text.
Frequently Asked Questions
Why is Excel showing the formula and not the result?
Either the cell was formatted as Text before the formula was typed, the formula starts with an apostrophe or a space, or Show Formulas is on. Set the format to General (Home > Number Format > General), click into the cell, press Enter, and press Ctrl+` if every formula on the sheet shows as text.
Why are my Excel formulas not updating automatically?
Calculation is set to Manual. Go to Formulas > Calculation Options and pick Automatic. Until then, F9 recalculates the workbook and Shift+F9 the active sheet. A workbook saved in Manual mode switches every workbook opened after it to Manual too.
Why does my total leave out some numbers?
Those numbers are stored as text, often after an import, and SUM skips text without an error. They are usually aligned to the left of the cell with a green triangle in the corner. Select them, click the warning icon and choose Convert to Number, or sum them with =SUMPRODUCT(--B2:B5).
How do I force Excel to recalculate?
Press F9 to recalculate all open workbooks, Shift+F9 for the active sheet, and Ctrl+Alt+F9 to recalculate every formula even if Excel thinks nothing changed. On a Mac, Cmd+= recalculates and Shift+Cmd+= does the active sheet.
