Windows  ·  medium  ·  Microsoft Office

Excel formulas show as text, or never update

A formula sits in the cell as literal text, or displays a result that never changes when its inputs do. Three unrelated settings produce this, and the one you are looking at is identifiable in seconds.

What you see

Either =SUM(A1:A10) is displayed exactly as typed, or a number is shown that stays the same no matter what is edited above it. Pressing F9 may fix it until the file is reopened.

What is actually wrong

Formula shown as text means the cell was formatted as Text before the formula was entered, or Show Formulas is switched on for the sheet. A result that never updates means calculation is set to Manual — and the setting is taken from the first workbook opened in the session, so one badly saved file sets it for everything opened afterwards in that instance of Excel.

Codes and articles

formula shows as textexcel not calculatingcalculation manualshow formulasF9 recalculate

Start here — find out which fix applies

A script that runs the 1 inspection command from the write-up below and prints what each one returned. It reads the machine and changes nothing — every command that could write, delete, start or stop is excluded from it by construction. Run this first, then pick the fix its output points at.

Download the read-only diagnosticchanges nothing · safe to run before reading

Fixes (3)

Check Show Formulas first
Excel2 minuteslow riskreversible

Every formula on the sheet is showing as text at once. Check this before anything else — it is one keystroke and it costs nothing to rule out.

  1. Press Ctrl and the grave accent key (the ` above Tab). This toggles Show Formulas.

    It sits next to Ctrl+1 and Tab and gets hit by accident constantly. The giveaway is that columns also become wider than usual, because the view widens them to fit the formula text.

  2. Or use the ribbon: Formulas → Formula Auditing → Show Formulas.

  3. Note that this is a per-sheet view setting saved with the workbook, so a file that arrives this way was saved with it on and will do it for everyone who opens it.

Confirm it workedResults are shown instead of formulas, and the column widths return to normal.
If you need to undo itPress the same keys again.
Set calculation back to automatic
Excel10 minuteslow riskreversible

Results are stale and F9 updates them.

  1. Formulas → Calculation Options → Automatic. Or File → Options → Formulas → Workbook Calculation.

  2. Understand where the setting came from before you close it. Excel takes the calculation mode from the FIRST workbook opened in that instance, and then applies it to every workbook opened afterwards in the same instance.

    This is why the problem appears to move between files at random, and why it comes back tomorrow. The file that is actually carrying the Manual setting is usually not the one being complained about — it is whichever one gets opened first, often something on the desktop or in a startup folder.

  3. Find the workbook that is carrying it. Close everything, open the suspect file on its own, and look at the calculation mode.

  4. With that file open on its own, set calculation to Automatic and save it. That is what makes the fix stick.

  5. Check the XLSTART folder, since anything in it opens first every time.

    PowerShell
    Get-ChildItem "$env:APPDATA\Microsoft\Excel\XLSTART" -ErrorAction SilentlyContinue | Select-Object Name, LastWriteTime
Confirm it workedChange an input cell and the dependent formulas update immediately. Close Excel, reopen the file, and confirm it is still Automatic.
If you need to undo itSet the mode back to Manual if it was deliberate — some very large models are set that way on purpose, and switching them to Automatic can make the file unusably slow.
Download this fix as a PowerShell script4 steps you do yourself · asks before each step
Convert cells that were formatted as text
Excel20 minuteslow riskreversible

Individual formulas show as text while others on the same sheet work.

  1. Select the affected cells and check the number format on the Home tab. If it reads Text, that is the cause — a formula typed into a Text cell is stored as a string.

  2. Set the format to General. Note that this alone does not fix the existing cells, which is where people get stuck.

    Changing the format tells Excel how to display the cell, but the content is already stored as text and nothing re-evaluates it. The cell has to be re-entered before the format change takes effect.

  3. Re-enter the cells in bulk with Text to Columns: select the column, Data → Text to Columns → Next → Next → Finish. This re-parses every cell in place with no other change.

    This is the quickest way to re-enter a whole column without retyping. It has the useful side effect of also fixing numbers that are stored as text.

  4. Check for a leading apostrophe or a leading space, both of which force text storage regardless of the cell format. Put =CODE(LEFT(A1,1)) in a spare cell — 39 is an apostrophe and 32 is a space.

  5. If the data came from an export or a paste from a web page, fix it at the import step instead — Data → From Text/CSV lets you set the column type before it lands.

Confirm it workedThe formulas evaluate, and ISTEXT on a converted cell returns FALSE.
If you need to undo itUndo reverses Text to Columns. Work on a copy of the sheet if the data matters and the column contains anything with a leading zero, which this will strip.

Related faults

Office applications open slowly, hang, or start with a blank windowWindows"Microsoft has blocked macros from running because the source of this file is untrusted"WindowsA document will not open — Protected View, "file format is not valid", or a corrupt fileWindows"File is locked for editing by another user" — including when that user is youWindowsDouble-clicking a file opens Office but not the documentWindowsPrinting from Office fails, prints blank, or goes to the wrong printerWindows

Where this stops. This write-up was written and checked by hand. It says what each step changes, how to confirm it worked and how to reverse it, and anything destructive is flagged before you reach it. If it does not match what your machine is doing, search the Support Centre for the exact code or message — and when something needs a person, get in touch.