When an Excel total stays stuck on its old value, or a formula appears as text instead of a result, test four things in order: the calculation mode, whether the cell is formatted as Text, whether there is a leading apostrophe or space, and whether Show Formulas is switched on. The first one fixes most cases.
Test 1: is calculation set to Manual?
Go to Formulas, Calculation Options. If Manual is selected, Excel only recalculates when you press F9. The status bar may show the word Calculate at the bottom left. Switch to Automatic and the totals update.
This setting belongs to the Excel program rather than to a single file, and it follows the first workbook opened in a session. If you open a file saved with Manual calculation first, every file you open after it can behave the same way. Close everything, open a file that is set to Automatic first, then open the others.
Test 2: is the cell formatted as Text?
If you type =B2+C2 into a cell formatted as Text, Excel keeps what you typed and shows the formula itself rather than a result. Select the cell, change the format on the Home tab to General, then press F2 and Enter to re-enter the formula. If the whole column has this problem, change the format for the column and re-enter each formula; formatting by itself does not retrigger the existing ones. Select the column, choose Data, Text to Columns and press Finish, which re-reads every cell.
Test 3: is there an apostrophe or a space at the start?
A leading apostrophe makes Excel treat everything after it as text. You do not see it in the cell, but you can see it in the formula bar. A space before the equals sign does the same. Click into the cell, press Home, and delete the apostrophe or space. Copying from a note-taking app, a web page or a WhatsApp message brings both in surprisingly often.
Test 4: is Show Formulas on?
Press Ctrl and the grave accent key (above Tab) to toggle Show Formulas. When it is on, every cell shows its formula and the columns look wider. The same switch is on the Formulas tab, labelled Show Formulas. Switch it off and the results come back.
| Symptom | Test | Fix |
|---|---|---|
| Totals do not change when you edit an amount | Calculation Options | Switch to Automatic, press F9 |
| The formula itself is displayed in the cell | Cell format on Home | General, then F2 and Enter |
| Shows =B2+C2 only in some cells | Formula bar | Delete leading apostrophe or space |
| Every cell shows a formula | Show Formulas | Ctrl plus the grave accent key |
What if the numbers are the problem?
If formulas do calculate, but a total misses some rows, the amounts may be text, which SUM ignores. A number that is left-aligned in its cell is usually text. Check with =COUNT($C$2:$C$500) against =COUNTA($C$2:$C$500). If the counts differ, convert the text cells to numbers: the small green triangle in the corner of a cell offers Convert to Number.
Could a circular reference be the cause?
If a formula refers to its own cell, directly or through a chain, Excel warns once and may leave the result at zero or stale. The status bar shows Circular References with a cell address. Under Formulas, Error Checking, Circular References lists them. Rewrite so the formula does not feed itself, for instance by keeping a running balance in a different column from the amounts.
Quick checklist in test order
- Formulas, Calculation Options, Automatic.
- Cell format General, then F2 and Enter.
- No apostrophe or space before the equals sign.
- Show Formulas off.
- Amounts are numbers, not text: compare COUNT and COUNTA.
- No circular reference warning in the status bar.
If you rebuild totals by hand every month from statement lines, SpendixAI can import the statement and give you report totals without any formulas to maintain.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


