Excel Formula Shows as Text Instead of Calculating
Fix an Excel formula that shows as text instead of a result: Text format, Show Formulas, a leading apostrophe, manual calculation and scripts.
You type =SUM(A1:A5) and the cell shows =SUM(A1:A5) instead of a number. Or you open a workbook and every formula in a column displays as its own source. It looks like Excel has stopped working. It has not. The cell has been told to treat its contents as text, or the display has been told to show formulas, and a few seconds of looking finds which. This post lists the causes, how to tell them apart, and the correct way to write formulas from Python so they calculate. It is a spoke of understanding Excel errors when data comes from code.
First, tell the cases apart
Click the cell and look at three things: the formula bar, the Number Format box on the Home tab, and whether the whole sheet or one cell is affected.
| What you see | Likely cause |
|---|---|
| Every formula on the sheet shows its source | Show Formulas is on |
| One cell, format box says Text | Cell formatted as Text |
One cell, format says General, text starts '= in the bar | Leading apostrophe |
| Result is shown but is old | Manual calculation |
| Content from code, all cells in a column | The script wrote strings |
The pattern across cells is the quickest filter. If the whole sheet is affected, it is a display setting. If a column from an export is affected, it is how the data was written.
Show Formulas
Excel can display every formula on a sheet instead of its result. The shortcut is Ctrl+` (the grave accent, left of 1 on most keyboards), and the command is Formulas, Show Formulas. It is easy to hit by accident, and it is saved with the workbook, so the next person sees formulas everywhere. Press the shortcut again, or click the button to turn it off. Column widths often look widened while it is on, since formulas are longer than values.
The cell is formatted as Text
Select the cell and check Home, Number Format. If it says Text, Excel stores anything typed there as a string, a formula included. The cell shows =SUM(A1:A5) because that is what the text is.
Changing the format to General does not fix the cell by itself. The existing content is still a string, and Excel does not reparse it. You have to re-enter it: press F2 to edit the cell, then Enter. For a whole column, there is a faster way. Set the format to General, then use Data, Text to Columns, Finish, which re-parses every cell in the selection. Another way is Find and Replace: replace = with =, which forces re-entry of each cell containing an equals sign.
Cells become Text when a template was formatted that way, when pasting from a source with text formatting, or when a column was set to Text before a formula was typed (fixing numbers stored as text in Excel describes the same mechanism for numbers).
A leading apostrophe or space
If the content begins with an apostrophe, Excel stores a string and hides the apostrophe in the cell. The formula bar shows it: '=SUM(A1:A5). People add apostrophes to stop Excel converting what they type, and imported text can carry one. Remove the apostrophe and press Enter.
A leading space has a similar effect: =SUM(A1:A5) is text, since a formula must start with the equals sign. Check with =LEFT(A1, 1). A non-breaking space (character 160) does the same and is invisible. Clean the column with a SUBSTITUTE or Power Query step first (trimming spaces and non breaking spaces).
Manual calculation
With calculation set to Manual (Formulas, Calculation Options), Excel does not recompute until you press F9. Typed formulas may show a stale result or zero. This is not text, but people report it as "the formula doesn't work". Set it back to Automatic, or press F9. Workbooks produced by some scripts and added-in tools switch to manual deliberately to speed up large edits; check the setting after opening them.
When a script wrote the formulas
Code can write a formula so that it works or so that it shows as text, and the difference is small.
openpyxl. Assigning a string that begins with = sets the cell as a formula:
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
ws["A1"], ws["A2"] = 10, 20
ws["A3"] = "=SUM(A1:A2)" # formula
ws["B3"] = "'=SUM(A1:A2)" # text, because of the apostrophe
ws["C3"].value = "=SUM(A1:A2)"
ws["C3"].data_type = "s" # forced to string: shows as text
wb.save("demo.xlsx")The cell type is what matters. ws["A3"].data_type is "f" for a formula and "s" for a string. If a column shows as text, print the type of one cell.
A cell with number format Text. ws["A3"].number_format = "@" sets the Text format, which shows the formula source when the file is opened and the cell re-entered. Do not set @ on a cell that holds a formula.
XlsxWriter. worksheet.write("A3", "=SUM(A1:A2)") treats the string as a formula by default. write_formula states the intent. write_string writes text, and the constructor option strings_to_formulas: False turns the guessing off.
pandas. df.to_excel writes strings starting with = as formulas when the engine is openpyxl, because the library applies the same rule. That is a feature when you meant it and a hazard when the value came from user data (formula injection in Excel exports). To write a string that only looks like a formula, prefix a single quote in the data or set the cell type to string after writing with ws.cell(...).data_type = "s".
Libraries do not calculate formulas. A workbook written by openpyxl contains the formula text and no cached result, so tools that read cached values see empty cells until Excel opens and saves the file (handling Excel formulas and cached values). If a viewer that does not calculate, such as a preview pane or a web library, shows blanks or formulas, that is the cause. Write values for files meant for viewers, or have a real spreadsheet application recalculate and save.
Locale and syntax
Formulas written by code must use English function names and comma separators in the file, whatever the user's language. A string with semicolons, as in =SUM(A1;A2), copied from a European Excel, fails to parse. Excel may show it as text or report an error. Write the file syntax, and let Excel translate it for display (Excel REF NAME and VALUE errors explained).
Quick repair for a whole sheet
If a pasted range arrived as text formulas:
Select the range and set the format to General.
Press Ctrl+H, enter
=in both Find what and Replace with, and choose Replace All. Excel re-enters each cell and the formulas calculate.If that does nothing, use Text to Columns, Finish, on each column.
Check calculation mode and Show Formulas.
If you need to convert the other way, to freeze formulas into text for documentation, prefix with an apostrophe, or use FORMULATEXT(A1) to display another cell's formula as a string without changing it.
Check the result
After the fix, ISFORMULA(A3) returns TRUE, and a changed input changes the output. Compare one result with the value you expect. In a generated file, open it in Excel once and confirm that the formula column shows numbers before shipping it, and add that check to your report test (testing that a generated Excel report is correct).
When the cell is not supposed to calculate
You are documenting a formula. Showing it as text on purpose is fine; use FORMULATEXT or an apostrophe.
The value is user data. A cell containing =1+1 typed by a customer is data. Treat it as a string and neutralize it on export.
The viewer cannot calculate. A preview that never recalculates will show cached values or nothing. Write values.
The workbook is in Protected View. Calculations may be paused until editing is enabled; click Enable Editing.
Microsoft's page on displaying formulas instead of their results and the openpyxl documentation on using formulas cover the details. To check a small result as JSON, use the CSV to JSON converter.
The openpyxl documentation describes cell types and formulas.
