If your Excel cell shows the formula text (like =A1+B1) instead of the calculated value, the most common cause is that the cell is formatted as Text, or the Show Formulas mode is on. To fix it immediately: press Ctrl+` (grave accent, the key above Tab) to toggle Show Formulas off, then check the cell’s number format under Home > Number and set it to General, and re-enter the formula by pressing F2 then Enter.
Why Excel Displays Formulas Instead of Values
Excel normally calculates formulas automatically and shows the result. When you see the raw formula text, one of these three things is usually happening:
- Show Formulas mode is enabled – This toggles the entire worksheet to display formulas in every cell, making columns wider to show the text.
- The cell is formatted as Text – Text formatting forces Excel to treat the entry as a string, not a calculation.
- The formula was entered with a leading apostrophe – Typing
'=A1+B1(with a single quote before the equals sign) makes Excel treat it as literal text.
Less common causes include the cell being in a table with a calculated column that got overwritten, or a corrupted workbook, but the three above cover 99% of cases.
How to Quickly Toggle Show Formulas
The fastest fix is to check if Show Formulas is on. This mode is easy to trigger accidentally by pressing Ctrl+` (Windows) or Ctrl+` (Mac, same key). When it’s on, every cell shows its formula, not just one.
- Press Ctrl+` once to toggle it off. The grave accent key is directly above the Tab key on most keyboards.
- If that doesn’t work, go to the Formulas tab and look for the Show Formulas button in the Formula Auditing group. Click it to turn it off.
- After toggling, check if your specific cell still shows the formula. If yes, move to the next fix.
On Excel for the web, Show Formulas is not available as a toggle. Instead, you’d need to check the cell format.
Fix a Cell Formatted as Text
When a cell’s number format is set to Text, anything you type, including a formula, is stored as a string. Excel does not evaluate it. This often happens when you import data or paste from another source, or when you set the format before typing.
To fix a single cell or a range:
- Select the cell(s) showing the formula.
- Go to Home > Number and change the format from Text to General.
- Click into the formula bar, or press F2, then press Enter to re-enter the formula. This forces Excel to recalculate and display the value.
If you have many cells, you can select the entire column, set it to General, then use Data > Text to Columns (with Delimited, then Finish) to convert text-formatted numbers and formulas back to normal. For formulas, you may need to re-enter them.
Remove a Leading Apostrophe
If you typed a formula and it shows as text, check the formula bar. If you see a single quote (‘) before the equals sign, that’s the culprit. The apostrophe is invisible in the cell itself but visible in the formula bar.
To fix it:
- Select the cell.
- Press F2 to edit.
- Delete the apostrophe at the beginning.
- Press Enter.
If you have many such cells, use Find & Replace (Ctrl+H). In the Find what box, type a single apostrophe (‘) and leave Replace with empty, then click Replace All. Note: this works only if the apostrophe is the first character; it won’t remove apostrophes inside text.
Why Does Excel Do This? Background and Behavior
Excel’s default behavior is to calculate formulas automatically. The Show Formulas mode is a diagnostic tool meant for auditing – it lets you see all formulas at a glance to trace dependencies. It’s not a bug, but it’s easy to trigger accidentally because the shortcut is right next to other common keys.
Text formatting is a legacy from spreadsheet design where users might want to enter numbers as text (like ZIP codes or phone numbers). Excel respects that format and won’t override it, which is why a formula in a text cell is treated as literal text.
When you type a formula into a cell that’s already formatted as Text, Excel doesn’t even try to evaluate it. You’ll see the formula exactly as typed, and the cell will align left (text alignment) instead of right (number alignment).
Common Related Problems and Misconceptions
“I changed the format to General but it still shows the formula.” – Changing the format alone doesn’t recalculate the cell. You must re-enter the formula (F2 then Enter) or press Ctrl+Alt+F9 to force a full recalculation.
“My formula works in one cell but not another.” – Check if the other cell is formatted as Text. Also check if the formula bar shows an apostrophe.
“The formula shows a #NAME? error instead of the value.” – That’s different: it means Excel doesn’t recognize a function name, often because of a typo or missing quotes. That’s not the same as showing the formula text.
“Show Formulas is off and the format is General, but it still shows the formula.” – This can happen if the cell contains a leading space before the equals sign. Delete the space and re-enter.
Preventing This in the Future
To avoid this issue:
- Don’t format cells as Text unless you truly need text entries.
- Be careful when pasting from web pages or other programs – use Paste Special > Values if you don’t need formatting.
- Avoid typing a leading apostrophe unless you intend to create a text string.
- If you work with imported data, run a quick check for text-formatted cells before entering formulas.
- Use the Error Checking feature under Formulas > Formula Auditing – it can flag cells that contain formulas stored as text.
Frequently Asked Questions
Q: How do I turn off Show Formulas in Excel?
Press Ctrl+` (grave accent) on Windows or Mac. Alternatively, go to the Formulas tab and click the Show Formulas button in the Formula Auditing group to toggle it off.
Q: Why is my Excel cell showing the formula and not the result after I press Enter?
Most likely the cell is formatted as Text. Change the number format to General, then click into the formula bar and press Enter again. If that doesn’t work, check for a leading apostrophe.
Q: Does changing the font or color affect formula display?
No. Font and color have no effect on whether a formula is evaluated. The display depends on the cell’s number format and the Show Formulas mode.
Q: Can I make Excel show formulas in only one cell?
No, Show Formulas is a worksheet-wide toggle. To see a single formula, click the cell and look at the formula bar, or press F2 to edit in-cell.
Q: What if I have a formula in a merged cell that shows as text?
Merged cells behave the same as normal cells for this issue. Apply the same fixes: check format, re-enter the formula, and ensure Show Formulas is off.
Q: Why does Excel show the formula when I type =1+1 but not when I type =A1?
If it shows the formula for both, it’s the same issue. If it shows the formula for =1+1 but calculates =A1, then the cell might have a custom number format that displays text for certain values, or the cell contains a line break. Try clearing the cell and retyping.
Related
- MS Access Split Form: Put the Datasheet on the Bottom
- Java split() Usage: Syntax, Examples, and Common Traps
- Databricks SQL CONTAINS: Find Text in a String
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Worksheet Won’t Scroll? Fix the CauseIf an Excel worksheet won’t scroll, first check whether Scroll Lock is on; then check for frozen panes…
- Excel Formula Not Working Today? Fix Common CausesIf the Excel TODAY formula is not working, enter =TODAY() in a cell, set the cell to a…
- TRIM and CLEAN Not Working in Excel? Fix the Hidden SpacesTRIM and CLEAN can seem broken because they do not remove every character that looks like a space.…
- VLOOKUP Not Working? Fix Numbers Stored as TextYes—VLOOKUP can return #N/A when the lookup value is a number but the matching value in the table…
- Save Failed in Excel? Fix It and Recover Your FileIf saving fails in Excel, keep the workbook open and try File > Save As > Browse to…
- Heat Map in Excel for Crash Data: A Practical GuideYes—you can make a heat map in Excel for crash data, but the right method depends on what…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.