Fix an Excel Formula Displayed as Text in a Cell
Question details
The user needs to make a formula that is displayed as plain text evaluate normally in Excel.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A formula such as SUBTOTAL or OFFSET has been entered in a worksheet cell and should return a result.
- Observed behavior
- The cell shows the formula characters instead of calculating the formula result.
Confirm that the entry starts with an equals sign (=) and that the formula is intended to calculate in the current workbook. If you copied the formula from another source, check whether it uses commas or semicolons as argument separators.
Change the cell to General and re-enter the formula
Use this when one or more formula cells are formatted as Text, causing Excel to treat the formula as plain characters.
Changing a cell from Text to General does not automatically recalculate an existing text entry. Edit the cell and confirm it again after changing the number format.
Click the cell that displays the formula text, such as =SUBTOTAL(...) or =OFFSET(...).
On the Home tab, open the Number Format list and select General.
Press F2 to edit the cell, then press Enter. Excel should evaluate the formula instead of displaying it as text.
Remove a leading apostrophe and turn off Show Formulas
Use this when the formula begins with an apostrophe or when every worksheet cell shows formulas rather than results.
Use the regional list separator required by Excel
Use this when Excel accepts the formula as an entry but reports a formula error after you correct the cell format and formula display mode.
Display and Calculate Spreadsheet Formulas Correctly in WPS Spreadsheet
WPS Spreadsheet lets you correct cells that show formulas as text by changing the number format, editing the formula entry, and checking formula display settings. Its familiar spreadsheet workspace makes it easy to move Excel worksheets and continue working with formulas.
- 1. Open the workbook in WPS Spreadsheet: Launch WPS Office, choose Spreadsheets, and open the Excel .xlsx or .xls workbook containing the formula.
- 2. Change the cell format: Select the affected cell, go to Home, open the number format list, and choose General.
- 3. Edit and confirm the formula: Double-click the cell or press F2, remove any apostrophe before =, and press Enter to calculate the formula.
- 4. Check formula display mode: Open the Formulas tab and turn off Show Formulas if the sheet is displaying formulas instead of results.
Frequently Asked Questions
Why does Excel show =SUM(A1:A5) instead of the answer?
The cell is commonly formatted as Text, or the formula has a leading apostrophe. Set the cell to General on Home, press F2, remove any apostrophe, and press Enter.
Why are formulas visible in every cell of my Excel worksheet?
Show Formulas is likely enabled. Go to Formulas > Show Formulas to switch it off, or press Ctrl+`.
Do I need to type the formula again after changing Text to General?
Yes. After selecting General, press F2 and then Enter in the existing cell. This makes Excel interpret the current entry as a formula.
Should Excel formulas use commas or semicolons between arguments?
Use the separator required by your regional settings. If a comma-separated formula fails, replace the commas with semicolons and enter the formula again.
Can SUBTOTAL calculate only visible rows?
Yes. SUBTOTAL can ignore filtered-out rows when you use the appropriate function number, such as 103 for COUNTA. Ensure the formula is entered as a formula and not stored as text.
