Excel Formula Not Calculating? Manual Mode and Text Cells

Thurlby Joinery quoted a hotel fit-out at £63,504.00 and signed it as a fixed price. The sheet behind the quote held the right costs and the wrong totals, and nothing on screen said so.

The costs were current. The timber price list dated 3 March was typed into column C that morning — £5,360.00 up across four lines — and the quote went out the next day.

What never happened was a recalculation. The workbook was set to Manual, inherited from the template it was copied from, so every figure in column E was still the one Excel worked out on 12 February.

Cost on the sheet                54,440.00
The quote those costs justify    73,494.00
The quote that was sent          63,504.00

Four lines were stale, £7,236.00 between them. A fifth, the lacquer line, held =C10*D10 as text rather than as a formula, so its £2,754.00 was in no total at all.

The file opened, scrolled and printed like every other workbook in the folder. Excel does not mark a stale cell, and the one word that would have said so sits in a corner of the status bar nobody reads.

01Nine Lines, Two Faults, One Total

Column E is what the quote carried. Column F is what the same formulas return once the sheet recalculates, and the two columns agree on five rows out of nine.

The four that disagree are the timber lines whose cost changed on 3 March. The one missing from the total altogether is row 10, where the formula is a piece of text.

02Which Fault It Is, in Ten Seconds

Three different things get described as "the formula is not working", and they have nothing to do with each other. What is on screen tells you which one you have:

The first one is the dangerous one, because the other three announce themselves. A stale total is an ordinary number in an ordinary cell, and it survives every check that reads the sheet rather than the sheet's arithmetic.

03Manual Calculation and Where the Switch Sits

The setting is on the Formulas tab, under Calculation Options, and it has three positions: Automatic, Automatic Except for Data Tables, and Manual. The same three sit in File ▸ Options ▸ Formulas.

Manual exists for a reason. On a workbook that takes forty seconds to recalculate, every edit costs forty seconds, and switching to Manual while you work is the difference between a morning and a day.

What makes it dangerous is that it is invisible. The ribbon tab has to be open to show it, and the cells go on displaying their last result as though nothing has changed.

The status bar says Calculate when results are outstanding. It is one word, in a grey bar, at the bottom left of the window, and it is the only thing Excel does to tell you.

One mercy: "Recalculate workbook before saving" is on by default, so a file that gets saved is usually current by the time it is closed. A file that is opened, read, quoted from and closed without saving is not.

04The Mode Travels With the First File You Open

Calculation mode is saved inside the workbook, but it is applied to the whole of Excel. The first workbook opened in a session sets the mode for every workbook opened after it, and a template saved in Manual passes Manual to everything.

That is how this happened at Thurlby. The quote file was copied from Quote_Template.xlsx, which had been saved in Manual during a slow afternoon in 2024, and every quote built from it since has opened the same way.

So the fault is rarely in the file anybody is looking at. It is in whichever file was opened first that morning, which may have been closed hours ago.

05F9, Shift+F9 and Ctrl+Alt+F9 Are Not the Same Key

Excel tracks which cells are "dirty" — changed, or dependent on something changed — and a plain recalculation only visits those. That is normally exactly what you want, and occasionally the reason a refresh does not fix anything.

Ctrl+Alt+F9 is the one to run before a number leaves the building. It ignores the dirty list entirely, which means it also catches the cases where the list was wrong — a custom function, a reference built by INDIRECT, a link to a file that changed while this one was closed.

On a Mac the same keys work, with fn held down where the function keys are set to media controls by default.

06A Cell Formatted as Text Keeps Your Formula

Format a cell as Text, then type =C10*D10 into it, and Excel stores nine characters. There is no result, no error, no #VALUE! — just the text you typed, sitting against the left edge of the cell the way text does.

Nothing about the sheet objects. SUM ignores text, so the total is simply smaller than it should be, and a column of right-aligned numbers with one left-aligned formula in it does not look wrong at a glance.

The Text format gets there by itself. A CSV column imported as Text, a row pasted in from an email, a column put through Text to Columns with Text chosen at step 3, a Table column that inherits the format of the row above — none of these announce themselves either.

Two functions see straight through it. =ISFORMULA(E10) returns FALSE on a cell that only looks like a formula, and =FORMULATEXT(E10) returns #N/A where a real formula would come back as text.

=SUMPRODUCT(--ISFORMULA(E2:E10))    →  8      nine priced lines
=ISFORMULA(E10)                     →  FALSE
=FORMULATEXT(E10)                   →  #N/A

ISFORMULA and FORMULATEXT need Excel 2013 or later; both work in Microsoft 365, and neither exists in Excel 2010. On an older file, =ISTEXT(E10) returning TRUE on a cell that should hold a number is the same news in weaker words.

07Changing the Format Does Not Convert the Cell

This is the part that wastes the afternoon. Select the cell, set it to General, and nothing happens: the formula text sits there exactly as before.

A number format decides how a value is displayed, not what it is. Changing it tells Excel what to do with the next thing you type into that cell, and says nothing about what is already in it.

The cell has to be entered again. For one cell that is F2 then Enter, which hands Excel the same characters to parse, this time against a General format.

For a column, Text to Columns does the re-entry in one pass: select the column, set it to General, then Data ▸ Text to Columns, Delimited, with every delimiter box cleared, and Finish. Nothing is split, every cell is re-read.

Text to Columns writes into the columns to the right of the one you selected if the split produces more than one column. With every delimiter cleared it produces exactly one, so nothing is overwritten — but check the Destination box before you press Finish, because the dialog remembers whatever it did last.

Find and Replace does the same job on a selection: replace = with =, with Look in set to Formulas. Every cell it touches is rewritten, which is enough to make Excel parse it again.

08Show Formulas Puts the Whole Sheet in Code

If every cell on the sheet shows its formula and the columns have gone wide, nothing is broken. Ctrl and the backtick key — the one above Tab, to the left of 1 — toggles Show Formulas, and so does Formulas ▸ Show Formulas.

It is a view setting, held per worksheet, saved with the file and used by the printer. A colleague who sends you a workbook with it switched on has sent you a working workbook that looks like a listing.

The widened columns are Excel's doing and they go back when you toggle it off. The one thing to know is that it is per sheet: fixing the sheet you are looking at does not fix the other eleven.

09The Apostrophe and the Space Before the Equals Sign

A leading apostrophe tells Excel to treat the rest of the cell as text. It is not shown in the cell and it is not part of the value — it appears only in the formula bar, as '=C10*D10.

A leading space does the same job in plain sight. =C10*D10 begins with a character that is not =, so Excel has no reason to read it as a formula and files it as text.

Both arrive by paste. Copy a formula out of an email, a web page or a PDF and the apostrophe or the space comes with it, and the cell it lands in looks like every other cell in the column.

The fix is the one from section 7: re-enter the cell. TRIM will not help here, because the content is text either way — TRIM cleans the spaces inside a value it has already accepted as text.

10Numbers Stored as Text Sum to Nothing

The same fault moves one column over. A cost stored as "18400" rather than 18400 is text, SUM skips it in silence, and the total is short by exactly one line.

Two counts find it. =COUNT(C2:C10) counts numbers and =COUNTA(C2:C10) counts anything at all, so 8 against 9 means one cell in that column is not the type you think.

Lookups react differently, and more honestly. XLOOKUP, VLOOKUP and INDEX/MATCH all return #N/A when a text key is matched against a numeric one, because an error is the correct answer to a question with no match.

The criteria functions do not even do that. COUNTIF, COUNTIFS, SUMIF and SUMIFS return 0 for a criterion that matches nothing, and 0 is a number that goes into a report without a word.

Three fixes, in the order worth trying. =VALUE(TRIM(C2)) for a stray space, =NUMBERVALUE(C2,",",".") where the file came from another locale, and Paste Special ▸ Multiply by a cell holding 1 to convert a block in place.

=COUNT(C2:C10)        →  8
=COUNTA(C2:C10)       →  9
=LEN(C2)              →  6     "18400 " with a trailing space
=VALUE(TRIM(C2))      →  18400

11Four Checks That Catch a Stale Sheet

The reconciliation. One cell that rebuilds the total from the inputs and subtracts the total on the sheet:

=ROUND(SUMPRODUCT(C2:C10,D2:D10)-SUM(E2:E10),2)     →  9990

It is 0 on a sheet that has recalculated, whatever the costs happen to be, and it catches a stale cell and a text formula with the same arithmetic.

The verdict. Wrap it so a human reads a word rather than a number, with LET naming the difference once:

=LET(gap, SUMPRODUCT(C2:C10,D2:D10)-SUM(E2:E10),
     IF(ROUND(gap,2)=0, "Current", "RECALCULATE"))

The formula count. =SUMPRODUCT(--ISFORMULA(E2:E10)) against the number of priced rows. Eight out of nine is a cell somebody typed over, pasted into, or formatted as Text.

The timestamp. =NOW() in a cell formatted dd/mm/yyyy hh:mm and labelled Calculated. It recalculates with everything else, so in Manual mode it freezes, and a stamp from three weeks ago is the warning nothing else gives you.

IFERROR has no place in any of these. A check that cannot error is a check that cannot tell you anything, and =IFERROR(VALUE(E10),"not a number") belongs in the repair, not in the audit.

12Eight Things That Bite

1. Reading a total without pressing F9. In Manual mode the number on screen is a historical record, and it looks exactly like a current one.
2. Assuming Automatic because this file is. The first workbook opened in the session set the mode, and that file may have been closed hours ago.
3. Trusting F9 alone. It recalculates what Excel flagged as dirty; Ctrl+Alt+F9 recalculates the rest, which is where custom functions and external links live.
4. Changing a cell's format to fix its content. General applies to the next thing typed into the cell. The text already in it stays text until the cell is re-entered.
5. Pasting a row from an email into a priced column. The paste brings a format, the format is usually Text, and the next formula typed there never runs.
6. Mistaking Show Formulas for damage. It is a saved view, it prints, and Ctrl with the backtick puts it back.
7. Letting SUM be the check. SUM ignores text without complaint, so a missing line and a mistyped line look identical: a total that is simply a bit low.
8. Leaving Manual on in a template. One file saved that way in 2024 has set the calculation mode of every quote built from it since.

13Mini Exercises

1. Set a spare cell to Text, type =1+1, then set it to General. Confirm it still reads =1+1, press F2 and Enter, and confirm it reads 2.
2. On the sample sheet, write =SUMPRODUCT(C2:C10,D2:D10)-SUM(E2:E10). Expect 9990, then fix row 10 and recalculate and expect 7236.
3. Build the two counts on column E: =COUNT(E2:E10) and =SUMPRODUCT(--ISFORMULA(E2:E10)). Expect 8 and 8 against nine priced lines.
4. Switch a copy of the workbook to Manual, change a cost in C2, and read the status bar. Find the word Calculate, then press F9 and watch it go.
5. Add =NOW() labelled Calculated above the total, switch to Manual, edit a cost, and confirm the stamp does not move. That frozen timestamp is the whole article in one cell.

What to Take Away

A formula that is not calculating is almost never a broken formula. It is a setting — Manual calculation, a Text format, a view toggle, an apostrophe — and every one of them leaves the formula itself perfectly correct.

That is why the symptom to fear is the quiet one. A cell showing =C10*D10 is embarrassing and harmless; a cell showing 21,060 when the answer is 24,840 goes out in a quote and gets signed.

So check the arithmetic rather than the cells. One reconciliation formula, one =NOW() stamp and Ctrl+Alt+F9 before anything leaves the building would have made the difference between £63,504.00 and £73,494.00 — on a sheet where every cost was right, every formula was right, and nobody had done anything wrong since 12 February.