You type a formula into an Excel cell, but it shows as plain text instead of calculating a result. This prevents your spreadsheet from performing any math or logic. The issue is typically caused by incorrect cell formatting or a specific workbook setting. This article explains how to change the cell format, check for leading apostrophes, and adjust the workbook calculation mode to fix the problem.
Key Takeaways: Fixing Formulas Showing as Text
- Format Cells > Number > General: Changes a cell formatted as Text so it can evaluate formulas correctly.
- Remove the leading apostrophe: Deletes the hidden character that forces Excel to treat the cell content as literal text.
- Formulas > Calculation Options > Automatic: Ensures Excel recalculates all formulas automatically after you edit them.
Why Excel Shows Formulas as Text
Excel displays a formula as text when it cannot recognize the entry as a valid calculation command. This happens because of three main settings. First, if a cell’s number format is set to Text, Excel treats everything you type as literal characters, including the equals sign. Second, a leading apostrophe entered before the formula forces this text behavior. Third, if the workbook’s calculation mode is set to Manual, formulas may appear static until you force a recalculation. Understanding which condition applies is the first step to fixing your sheet.
Steps to Convert Text to a Working Formula
Follow these methods in order. Start with the simplest fix for the cell format.
Method 1: Change Cell Format from Text to General
- Select the problem cell
Click on the cell where the formula shows as text. You can select multiple cells if the issue is widespread. - Open the Format Cells dialog
Right-click the selected cell and choose Format Cells from the context menu. Alternatively, press Ctrl+1 on your keyboard. - Set the number format to General
In the Format Cells dialog, click the Number tab. Select General from the category list on the left. Click OK to apply the change. - Re-enter the formula
Double-click the cell to enter edit mode, or press F2. Simply press Enter. The formula should now calculate and display a result.
Method 2: Find and Remove Leading Apostrophes
- Check for an apostrophe in the formula bar
Select the cell with the text formula. Look in the formula bar at the top of the Excel window. If you see a single quote (‘) before the equals sign, that is the cause. - Delete the apostrophe
Click into the formula bar. Place your cursor just before the equals sign and press Backspace to delete the apostrophe. Press Enter. - Use Find and Replace for many cells
If many cells are affected, press Ctrl+H to open Find and Replace. In the Find what field, type a single apostrophe (‘). Leave the Replace with field empty. Click Replace All.
Method 3: Set Workbook Calculation to Automatic
- Navigate to calculation options
Go to the Formulas tab on the Excel ribbon. Look in the Calculation group on the right side. - Select Automatic calculation
Click the Calculation Options button. A dropdown menu will appear. Select Automatic. This setting applies to the entire workbook. - Force a manual recalculation
After changing the setting, press F9 on your keyboard. This key recalculates all formulas in all open workbooks immediately.
Common Mistakes and Limitations
Even after applying the fixes, some related issues can prevent formulas from working.
Cell Format Changes Back to Text After Editing
If you change a cell’s format to General and the formula still does not calculate, the cell may be part of a pre-formatted table column or have a conditional formatting rule that overrides it. Check for table styles or conditional rules that set the format to Text. Also, copying and pasting values from another source can reapply the Text format. Use Paste Special > Values and Number Formats to avoid this.
Formula Shows as Text After Importing Data
Data imported from CSV files, web pages, or other external systems often arrives with all cells formatted as Text. The fastest fix is to use the Text to Columns wizard. Select the column with the formulas. Go to Data > Text to Columns. In the wizard, just click Finish on the first step without changing any settings. This process resets the cell format for the selected range.
Circular Reference Warning Appears
After fixing the text display, your formula might calculate but show a zero or an error. A common result is the circular reference warning. This means the formula refers to its own cell, either directly or through a chain of other formulas. Check the formula logic and remove any reference to the cell it is written in. The status bar at the bottom of the Excel window will indicate which cell has the circular reference.
Text Format vs. General Format: Key Differences
| Item | Text Format | General Format |
|---|---|---|
| How Excel treats input | All characters are literal text | Excel interprets numbers and formulas |
| Leading apostrophe visibility | Hidden in cell, visible in formula bar | Not applicable |
| Formula calculation | Formula displays as text string | Formula executes and shows result |
| Default alignment | Left-aligned | Numbers right, text left |
| Best use case | Preserving numbers with leading zeros | Standard data entry and calculations |
You can now fix cells where Excel formulas display as plain text. Remember to first check the cell’s number format in the Format Cells dialog. For persistent issues, use the Text to Columns tool on imported data. An advanced tip is to use the ISTEXT function in a helper column to quickly scan and identify all cells formatted as Text in a large dataset.