formulastreak

Formula shows as text instead of the result: how to fix it

=A1

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. 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. 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. 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. 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”

Get the formulastreak newsletter

Bite-sized formula tips you can use at work the next day, plus a heads-up when new free tools and lessons land. It arrives now and then, never in a flood.

No spam, unsubscribe in one click. We store your email, this consent and when you gave it. Details in our privacy policy.

Need a quick reference? The formula cheat sheet is free to read and print.