Excel Cell Reference Shows Formula Not Value? Fix It

Errors & fixes · Updated November 22, 2026

If your Excel cell shows the formula text (like =A1+B1) instead of the calculated result, the cell is almost certainly formatted as Text, or the worksheet is in “Show Formulas” mode. To fix it immediately: select the cell(s), press Ctrl+1 (Mac: Cmd+1), set the format to General or Number, then press F2 and Enter to recalculate. If that doesn’t work, check the Formulas tab and make sure Show Formulas is not highlighted.

Why Excel Sometimes Displays the Formula Instead of the Result

Excel has two distinct display modes: one shows calculated values, the other shows the underlying formulas. When a cell shows =A1+B1 literally, one of three things is happening:

  • The cell is formatted as Text. This is the most common cause. When you type a formula into a cell already formatted as Text, Excel treats it as a string and doesn’t evaluate it.
  • Show Formulas is toggled on. This is a worksheet-level view that displays every formula in the sheet, usually to audit your work.
  • An apostrophe (‘) is at the start. If you accidentally typed an apostrophe before the equals sign, Excel treats the entire entry as text.

Less common causes include a missing equals sign (typing A1+B1 without the =) or a broken calculation setting (though that usually shows a cached value, not the formula text).

How to Fix a Text-Formatted Cell

If the cell is formatted as Text, changing the format alone won’t recalculate the formula—Excel needs a nudge to re-evaluate. Here’s the reliable sequence:

  1. Select the cell or range showing formulas.
  2. Press Ctrl+1 (Mac: Cmd+1) to open Format Cells.
  3. On the Number tab, choose General (or Number if you want decimal places). Click OK.
  4. Click into the formula bar, then press Enter. Or press F2 then Enter on the cell.

For multiple cells, select them all, repeat steps 1–3, then use Ctrl+H to open Find & Replace. In “Find what” type =, in “Replace with” type =, then click Replace All. This trick forces Excel to re-enter every formula as a formula, even in text-formatted cells.

Turning Off Show Formulas Mode

Show Formulas is a toggle, not a cell property. If your entire worksheet shows formulas instead of values:

  • Go to the Formulas tab on the ribbon.
  • In the Formula Auditing group, click Show Formulas to turn it off.
  • Alternatively, press Ctrl+` (the grave accent key, usually above Tab) on Windows. On Mac, press Cmd+`.

This view is useful for auditing, but it’s easy to toggle accidentally. The shortcut works as a toggle, so pressing it again restores values.

Why Text Formatting Causes the Problem (and How to Prevent It)

When a cell is formatted as Text before you type a formula, Excel disables formula evaluation. This is a deliberate design choice: text formatting tells Excel “treat whatever I type as literal text.” So =SUM(A1:A5) becomes a string, not a calculation.

To avoid this in the future:

  • Don’t pre-format entire columns as Text unless you’re entering phone numbers or ZIP codes.
  • If you import data from a CSV or text file, check the column format—Excel often guesses Text for columns with leading zeros.
  • Use the Text to Columns wizard (Data > Text to Columns) to convert a column of text-formatted numbers back to real numbers, which also fixes formulas.

Common Related Problems and Their Fixes

Sometimes the issue isn’t the formula display itself, but a similar symptom that confuses users:

  • Cell shows #NAME? or #VALUE! This is a different error—usually a typo in the function name or wrong argument type. Check the formula bar for misspellings.
  • Cell shows 0 instead of the expected result. This often means the formula references empty cells or cells formatted as text. Use =ISNUMBER(A1) to test if a referenced cell contains a number.
  • Formula result doesn’t update when you change inputs. If calculation is set to Manual (Formulas > Calculation Options > Manual), press F9 to recalculate. On Mac, press Cmd+=.
  • Cell shows the formula but only in the formula bar. That’s normal—the formula bar always shows the underlying formula; the cell shows the result (unless Show Formulas is on).

How to Choose Between Showing Formulas and Values

There’s no “right” mode for all situations—it depends on what you’re doing:

  • Auditing or debugging: Show Formulas mode is great for tracing dependencies. Use Ctrl+` to toggle it temporarily.
  • Printing a report: Always turn off Show Formulas before printing, unless you intentionally want to print the formulas as documentation.
  • Sharing a workbook: Keep values visible so collaborators don’t panic. If you need to share formulas, consider exporting a PDF of the formula view.

If you frequently need both views, consider using the FORMULATEXT function in a separate column. For example, with a formula in A1, put =FORMULATEXT(A1) in B1 to display the formula text while A1 shows the value.

Frequently Asked Questions

Why does my Excel cell show =A1+B1 but not the sum?
Most likely the cell is formatted as Text. Change the format to General, then press F2 and Enter. If that fails, check if Show Formulas is toggled on (Formulas tab > Show Formulas).

How do I force Excel to recalculate a text-formatted formula?
After changing the format to General, use Find & Replace: find = and replace with =. This re-enters the formula as a formula. Alternatively, click each cell, press F2, then Enter.

What does Ctrl+` do in Excel?
It toggles Show Formulas mode on and off. When on, every cell displays its formula instead of the calculated value. Pressing it again restores normal view.

Can I have some cells show formulas and others show values?
Yes, but not with Show Formulas mode (which affects the whole sheet). Use the FORMULATEXT function in a helper column to display the formula text for specific cells while the original cells show values.

Why did my formula turn into text after I copied it from another program?
When you paste from a text editor, Excel may interpret the content as text, especially if the cell was already formatted as Text. Use Paste Special > Values, then reformat as General and re-enter the formula.

Is there a way to prevent this from happening when importing a CSV?
In the Text Import Wizard (Data > From Text/CSV), choose “General” for column data format instead of “Text”. This prevents Excel from treating numeric-looking columns as text, which can cause formulas to be stored as text.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.