Excel guide · 3 min read
Excel formula showing as text instead of the result
Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Cost | What you see | |
| 2 | Paper | 18.5 | =SUM(B2:B5) | |
| 3 | Toner | 74.99 | ||
| 4 | Folders | 12.25 | ||
| 5 | Labels | 9.8 |
Fix 1: the cell is formatted as Text
This is the most common cause. If a cell (or the whole column) is set to Text format, Excel stores whatever you type exactly as typed, including the equals sign. It happens a lot in imported files and in columns someone set to Text to keep leading zeros.
Changing the format to General is not enough on its own: the cell already holds text. You have to re-enter it.
- Select the cells showing formulas.
- Home > Number Format drop-down > General.
- Press F2 then Enter in each cell to re-enter the formula.
- For a whole column at once: Data > Text to Columns > Finish. This re-enters every cell without changing anything else.
Fix 2: Show Formulas is turned on
If every formula on the sheet is showing, and columns look wider than usual, Show Formulas is on. It is easy to toggle by accident because the shortcut is Ctrl + ` (the key left of 1, under Esc).
Press Ctrl + ` again, or go to Formulas > Show Formulas and click it off. This setting is per sheet, so other tabs can look normal.
Fix 3: a space or apostrophe before the equals sign
A formula must start with =. If it starts with a space, Excel treats the whole entry as text. This often comes from copying a formula out of an email, a web page or a chat message. An apostrophe at the start ('=SUM...) also forces text; you only see it in the formula bar.
Check the first character: =LEFT(D2,1)=" " returns TRUE when the entry starts with a space. Click the cell, delete everything before the =, and press Enter.
Result: TRUE
TRUE: the entry in D2 starts with a space, so Excel shows it as text instead of calculating it.
After the fix: the formula calculates
Once the cell is General and the entry starts with =, Excel calculates it straight away.
Result: 115.54
The four supply costs add up to 115.54.
Related: the formula shows an old result
If the formula calculates but does not update when you change the inputs, the workbook is set to manual calculation. You will often see "Calculate" in the status bar.
Go to Formulas > Calculation Options > Automatic. Press F9 to recalculate right now. Manual mode can be saved inside a workbook, so a file someone else sent you can switch your Excel to manual without you noticing.
Common mistakes
Changing the format to General and expecting it to work
The cell still holds text. Re-enter it with F2 + Enter, or run Data > Text to Columns > Finish on the column.
Typing the formula without an equals sign
SUM(B2:B5) is just text. Every formula starts with =.
Setting a whole column to Text to keep leading zeros
Any formula typed later in that column will show as text. Format only the ID cells as Text, or use a custom number format like 00000.
FAQ
Why is Excel showing my formula and not the answer?
Either the cell is formatted as Text, Show Formulas is on, or the formula does not start with =. Check Ctrl + ` first because it affects the whole sheet.
What is the shortcut to show formulas in Excel?
Ctrl + ` (grave accent, the key left of 1). Press it again to switch back to results. On a Mac it is Control + `.
How do I fix a whole column of formulas showing as text?
Set the column to General, then select it and use Data > Text to Columns > Finish. Excel re-enters every cell and the formulas calculate.
Why does my formula not update when I change a number?
Calculation is set to Manual. Go to Formulas > Calculation Options > Automatic, or press F9 to recalculate once.
Functions used
Related guides
- #VALUE! error in Excel: causes and fixes
- #NAME? error in Excel
- How to remove spaces in Excel
- Excel formulas cheat sheet: the 25 everyone should know
- #NULL! error in Excel: why it happens and how to fix it
Updated