Excel formula shows as text or doesn't update

1Show Formulas is turned on

How to recognise it

Every formula on the sheet shows as text and the columns look wider. Other sheets are fine.

Fix

Click Formulas > Show Formulas to turn it off, or press Ctrl+` (the grave accent key, usually left of 1). The setting is per sheet, which is why it's easy to switch on by accident with that shortcut.

2The cell is formatted as Text

If a cell is formatted as Text before you type, Excel keeps whatever you type as text, formulas included.

How to recognise it

Only some cells show the formula. Select one: the number format box on the Home tab says Text.

Fix

Set the format to General. Changing the format alone doesn't recalculate the cell, so click into it, press F2 and then Enter.

For a whole column: set it to General, then select it and use Find & Replace (Ctrl+H) to replace = with = and click Replace All. Excel re-enters every formula. Data > Text to Columns > Finish on one column works too.

Prevent it. When a column of IDs needs Text format, keep the formulas in a different column that uses General.

3Something before the = sign, or no = at all

How to recognise it

The formula bar shows an apostrophe ('=A1+B1), a space ( =A1+B1) or no equals sign (SUM(A1:A5)). Formulas pasted from websites and emails often start with a space.

Fix

Delete the apostrophe or space in the formula bar, or add the missing =, and press Enter. For leading spaces in many cells, use Find & Replace to replace = (space, equals) with =.

4Calculation is set to Manual

How to recognise it

Formulas show results, but they don't change when you edit the numbers. When you fill a formula down, every row shows the same value as the first. The status bar may show Calculate.

Fix

Go to Formulas > Calculation Options and choose Automatic. The same setting is under File > Options > Formulas > Workbook Calculation.

Shortcuts if you keep Manual on purpose: F9 recalculates all open workbooks, Shift+F9 recalculates the current sheet, and Ctrl+Alt+F9 forces every formula to recalculate.

Excel uses one calculation mode for all open workbooks, taken from the first workbook you open. A file saved in Manual mode can switch your other files to Manual too.

5The formula is fine but its inputs are text

If a formula calculates but always shows 0 or ignores some values, the numbers it uses are probably stored as text. See why Excel SUM shows 0 for how to convert them.