Formula shows as text instead of the result: how to fix it
The cell shows the formula itself, not what it calculates.
If a cell shows =SUM(B2:B9) instead of a number, the spreadsheet is treating your formula as plain text, or it's set to show formulas instead of results.
Common causes, ranked
- 1
The cell is formatted as Text
A formula typed into a Text cell (or into a cell that picked up a Text format) is stored as text.
Fix: Change the cell's format to General, then re-enter the formula: click the cell, press F2, then Enter. Changing the format alone isn't enough.
- 2
Show Formulas is turned on
Every formula cell shows its formula. It's easy to switch on by accident with a shortcut.
Fix: Excel: Formulas › Show Formulas, or Ctrl + ` (grave accent). Google Sheets: Ctrl + ~ shows or hides all formulas.
- 3
The formula doesn't start with =
Without the
=, a spreadsheet stores what you typed as text.Fix: Add the
=at the start.Shows the error
SUM(B2:B9)Fixed
=SUM(B2:B9) - 4
An apostrophe or a space before the =
A leading
'tells the spreadsheet “this is text”, and a space before=does the same.Fix: Edit the cell and delete the apostrophe or space before the
=.Shows the error
'=SUM(B2:B9)Fixed
=SUM(B2:B9)
Should you hide it?
This isn't an error you hide: fix the cause above and it goes away.
Excel vs Google Sheets
Google Sheets uses Ctrl + ~ to show or hide all formulas. Everything else here (start with =, no apostrophe in front) works the same way.
Sources
Practise this in 5 minutes
Fixing Formula shows as text is taught in Lesson 1: Your first formula (Unit 1, Cells and formulas), on a live sheet with instant feedback.
Open “Your first formula”Need a quick reference? The formula cheat sheet is free to read and print.