If your Excel cell shows the formula text (like =A1+B1) instead of the calculated result, the most common cause is that the cell is formatted as Text, or you accidentally turned on Show Formulas mode. Press Ctrl+` (the backtick key, usually above Tab) to toggle Show Formulas off, and if that doesn’t fix it, check the cell’s number format and change it from Text to General, then re-enter the formula.
Why Does Excel Display the Formula Instead of the Result?
Excel normally calculates formulas and shows the result in the cell, with the formula itself visible in the Formula Bar. When the cell shows the raw formula text, one of three things is happening: the cell is formatted as Text, the worksheet is in Show Formulas mode, or the formula was entered with a leading apostrophe ('). Each has a distinct fix, and knowing which one you have saves time.
The Text format is the most common culprit. When a cell is formatted as Text, Excel treats everything you type as literal text, including an equals sign. This often happens after you import data, copy from a web page, or apply a Text format to a column before entering data. The Show Formulas mode is a global worksheet toggle that displays all formulas instead of results—useful for auditing but easy to turn on accidentally with a keyboard shortcut.
How to Turn Off Show Formulas Mode
Show Formulas mode is a worksheet-level setting. When it’s on, every formula in the sheet appears as text, and columns widen to show the full formulas. To turn it off:
- Press
Ctrl+`(Windows) orCtrl+`(Mac) to toggle it off. - Or go to the Formulas tab and click Show Formulas (in the Formula Auditing group) to uncheck it.
- If you’re in Excel for the web, go to View tab and toggle Show Formulas off.
After toggling, all cells should immediately show results. If only one cell (or a small range) still shows the formula, the issue is likely the cell format, not the worksheet mode.
Fix Cells Formatted as Text
If a specific cell shows the formula but others don’t, the cell is probably formatted as Text. Here’s how to fix it:
- Select the cell or range showing the formula.
- On the Home tab, in the Number group, change the format from Text to General.
- Click into the formula bar and press Enter (or double-click the cell and press Enter) to re-enter the formula.
Re-entering is key—changing the format alone doesn’t recalculate the cell. If you have many cells, select the whole range, change format to General, then use Find & Replace to replace = with = (yes, same character) to force Excel to re-evaluate each formula. Alternatively, use the Text to Columns wizard: select the range, go to Data > Text to Columns, click Finish immediately—this often forces Excel to treat the content as formulas again.
Check for a Leading Apostrophe or Space
Sometimes a formula appears as text because it was typed with a leading apostrophe (') or a space before the equals sign. The apostrophe is invisible in the cell but visible in the Formula Bar. To check:
- Click the cell and look at the Formula Bar. If you see an apostrophe before the equals sign, remove it and press Enter.
- If you see a space, remove it and press Enter.
- If the cell was imported from a CSV or external source, it may have a non-breaking space or other invisible character—use the
CLEANandTRIMfunctions in a helper column to strip them out.
For example, if cell A1 contains '=SUM(B1:B5), select A1, delete the apostrophe in the Formula Bar, and press Enter. The formula will then calculate.
Common Related Problems and Misconceptions
A frequent misconception is that the cell is “broken” or that Excel can’t handle the formula. In reality, it’s almost always a formatting or display setting. Another related issue is when a formula returns a result but it looks wrong—for example, a date shows as a number (like 45231) because the cell is formatted as General. That’s not the same problem, but it’s a similar “why is my cell showing this?” moment.
Another common situation: you copy a formula from a website or email, and it pastes as text. To avoid this, use Paste Special > Values or paste into the formula bar directly. Also, if you’re using a laptop, the Ctrl+` shortcut might require pressing Ctrl+Shift+` depending on your keyboard layout—check the Formulas tab to confirm the mode.
How to Prevent This in the Future
To avoid the frustration of seeing formulas instead of results:
- Don’t pre-format cells as Text unless you’re entering phone numbers or ZIP codes.
- When importing data, use Data > From Text/CSV and set column types to General or appropriate formats.
- Avoid typing an apostrophe before formulas—it’s a habit some users pick up to keep leading zeros, but it breaks formulas.
- If you frequently toggle Show Formulas by accident, consider disabling the shortcut via Excel Options > Customize Ribbon > Keyboard Shortcuts (though this is advanced and not recommended for most users).
Also, if you’re sharing a workbook and someone else sees formulas, it’s likely they have Show Formulas on their end—it’s a view setting, not saved with the file.
When the Formula Still Won’t Calculate
If you’ve fixed the format and toggled Show Formulas off, but the cell still shows text, check for these:
- The cell contains a non-breaking space or other Unicode character before the equals sign—use
=CLEAN(A1)in a helper cell to see the cleaned version. - The formula was entered as an array formula with
Ctrl+Shift+Enterin older Excel versions—this is rare but can cause display quirks. - The workbook is in Manual Calculation mode. Go to Formulas > Calculation Options and select Automatic. If it’s on Manual, formulas only recalculate when you press F9—but they should still show results, not text.
If none of these work, try copying the cell to a new blank cell and pressing Enter. If that works, the original cell may have corrupt formatting—clear it with Home > Clear > Clear All and re-enter the formula.
Frequently Asked Questions
Why does my Excel show formulas instead of values when I open a file?
This usually means the file was saved with Show Formulas mode on, or the cells were formatted as Text. Toggle Show Formulas off with Ctrl+`, and check the cell format. If it’s a shared file, the sender may have had Show Formulas on when saving—the setting is per-worksheet and saved with the file.
How do I get Excel to show numbers instead of formulas?
Turn off Show Formulas mode (Ctrl+`) and ensure cells are formatted as General or Number. If a specific cell still shows text, change its format to General and re-enter the formula by clicking in the formula bar and pressing Enter.
Why is my formula showing as text with a green triangle?
The green triangle indicates an error or inconsistency, often “Formula Omits Adjacent Cells” or “Text-formatted number.” If the cell shows the formula as text, it’s likely formatted as Text—fix the format and re-enter. The green triangle will disappear after recalculation.
Does pressing F2 help fix a formula showing as text?
Sometimes. Press F2 to edit the cell, then press Enter. This forces Excel to re-evaluate the cell content. If the cell is formatted as Text, you’ll still need to change the format first—F2 alone won’t override Text format.
Can a formula show as text if the workbook is in compatibility mode?
No, compatibility mode (for older Excel versions) doesn’t cause formulas to display as text. It’s still a formatting or Show Formulas issue. However, if you’re using a .xls file, some newer functions like XLOOKUP won’t work—but that would show a #NAME? error, not text.
Why do formulas show as text after copying from another spreadsheet?
When you copy a cell from another workbook, the destination may have Text formatting. Use Paste Special > Values to paste only the result, or paste into a General-formatted cell and then re-enter the formula. Also, if the source cell was formatted as Text, the copied formula inherits that.
Related
- Python split() and join() Explained with Examples
- Snowflake SPLIT_PART: Syntax, Examples, and Edge Cases
- Java String Built-In Methods: Uses, Examples, and Tips
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Formulas Not Working Between Sheets? Fix ThemIf an Excel formula is not working between sheets, first check that it points to the right tab…
- COUNTIFS Formula Not Working? Fix Common ErrorsIf COUNTIFS is not working, first check that every criteria range is the same size and that each…
- Why Won’t Data Validation Work in Excel? Common FixesExcel data validation usually seems not to work because the rule is missing or applied to the wrong…
- VLOOKUP Match Not Working? Fix Common CausesIf VLOOKUP is not finding a match, first use exact-match mode by setting its fourth argument to FALSE,…
- Excel Formula Is Correct but Not Working? How to Fix ItIf a formula is correct but not working in Excel, first check whether the cell is formatted as…
- VLOOKUP Not Working with General Format? Fixes That WorkYes—VLOOKUP can work with cells set to General; that format alone does not prevent a lookup. If the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.