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:
- Select the cell or range showing formulas.
- Press Ctrl+1 (Mac: Cmd+1) to open Format Cells.
- On the Number tab, choose General (or Number if you want decimal places). Click OK.
- 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
- Excel Browser Power Query: What It Is & How to Use
- Excel Blank Calendar Template: How to Get One
- Excel Biweekly Timesheet Template with Lunch
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Locate Circular References in Excel: Find and Fix ThemIn Excel for Microsoft 365 on Windows, select Formulas > Error Checking > Circular References to see listed…
- Excel MATCH Function Not Working With Numbers? FixesIf Excel’s MATCH function cannot find a number, first check whether the lookup value and the numbers in…
- Why XLOOKUP Is Not Working in Excel: Causes and FixesXLOOKUP usually fails because Excel does not support the function, the lookup and return ranges do not line…
- Excel XLOOKUP Spill Error: Causes, Fixes, and ExamplesAn Excel XLOOKUP spill error usually means the formula is returning multiple results but Excel cannot place them…
- Excel File Won’t Scroll? Causes and Fixes to Try FirstIf an Excel file will not scroll, first press Scroll Lock to turn it off, then check View…
- Hyperlink in Word Not Working? Fixes That Usually HelpIf a hyperlink in Word is not working, first try following it with Ctrl+click on Windows or Command+click…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.