Excel is not calculating because calculation mode is set to Manual. But even after switching to Automatic, your formulas still sit there like frozen statues — and that’s where most people give up and retype everything.
The Setup
You’re auditing Q1 sales for six regional reps at
Veridian Dynamics. The raw sheet has inconsistent formatting: some numbers are stored as text, dates are misaligned, and column headers have trailing spaces. You need to compute total revenue per rep, average deal size, and flag deals over $25,000 — all using formulas in columns E, F, and G.
| A: Rep Name |
B: Deal Size |
C: Date Closed |
D: Region |
| Liam Torres |
"$18,450" |
2024-02-11 |
West |
| Priya Mehta |
"$32,100" |
2024-01-29 |
East |
| Diego Ruiz |
"$9,750 " |
2024-03-05 |
South |
| Anya Petrova |
"$27,600" |
2024-02-22 |
North |
| Marcus Lee |
"$14,300" |
2024-01-17 |
West |
| Tasha Boone |
"$41,200" |
2024-03-14 |
East |
| Rafael Kim |
"$22,850" |
2024-02-08 |
South |
| Zara Hassan |
"$19,900" |
2024-01-30 |
North |
The Challenge
You type
=SUM(B2:B9) in cell B11. It displays
=SUM(B2:B9) — not the result. You double-click the cell: it shows the formula, not the value. You press F2 then Enter — nothing changes. You copy-paste values into a new sheet — still text. You check for circular references — none found. What’s going on?
The beauty of this approach is that Excel isn’t broken. It’s just obeying hidden rules you didn’t know existed — especially around number storage, cell formatting, and calculation triggers. And the most counterintuitive part? Your formulas *are* calculating — they’re just calculating the wrong thing: text strings, not numbers.
Walking Through It
We’ll fix this step-by-step, showing before/after for each intervention. Start with the original range B2:B9.
Step 1: Check Calculation Mode
Press
Alt + M + X — this opens the Calculation Options menu. If “Manual” is highlighted, press Enter to switch to “Automatic.” This alone fixes ~30% of ‘not calculating’ cases — but not ours. Our formulas still display as text.
Step 2: Detect Text-Formatted Numbers
Select B2:B9. Look at the status bar at the bottom of Excel. If it says “Count: 8” instead of “Sum: 186,150”, those cells are text. Also, numbers aligned left (not right) in default font = red flag.
Before:
| Cell |
Value (as seen) |
Actual Type |
| B2 |
"$18,450" |
Text |
| B3 |
"$32,100" |
Text |
Now apply the
Text to Columns fix: select B2:B9 → Data tab → Text to Columns → Delimited → Next → Next → Column data format: “General” → Finish. Excel converts quoted numbers into true numbers.
After:
| Cell |
Value (as seen) |
Actual Type |
| B2 |
18450 |
Number |
| B3 |
32100 |
Number |
Step 3: Strip Nonbreaking Spaces & Hidden Characters
Look again at B4: “$9,750 ” — notice the trailing space? That’s ASCII 160 (nonbreaking space), invisible to the eye but deadly to calculations. Use
=CLEAN(SUBSTITUTE(B4,CHAR(160)," ")), then wrap in VALUE:
=VALUE(CLEAN(SUBSTITUTE(B4,CHAR(160)," "))). Paste that formula down B2:B9, then copy → Paste Special → Values back into B2:B9.
Step 4: Force Recalculation
Even with correct data types, Excel sometimes holds cached results. Press
Ctrl + Alt + F9 — full recalculation across all open workbooks. Not F9 (which only recalculates active sheet). This is the nuclear option — and it works when nothing else does.
The Result
Now formulas behave as expected. In E2:E9,
=B2*1.07 computes 7% commission. In F2:F9,
=IF(B2>25000,"High Value","Standard") correctly flags Tasha Boone and Anya Petrova. And B11 finally shows
$186,150 — not the formula.
| Rep Name |
Deal Size |
Commission (7%) |
Tier |
| Liam Torres |
18,450 |
1,236.15 |
Standard |
| Priya Mehta |
32,100 |
2,150.70 |
High Value |
| Diego Ruiz |
9,750 |
653.25 |
Standard |
| Anya Petrova |
27,600 |
1,849.20 |
High Value |
| Marcus Lee |
14,300 |
958.10 |
Standard |
| Tasha Boone |
41,200 |
2,760.40 |
High Value |
| Rafael Kim |
22,850 |
1,530.95 |
Standard |
| Zara Hassan |
19,900 |
1,333.30 |
Standard |
What Could Go Wrong
Here are three specific mistakes — not vague warnings — that cause Excel to appear unresponsive, with how to spot and fix each.
Mistake #1: Apostrophe Prefix (‘) Left Behind
You imported data from a CSV where every number had a leading apostrophe. Even after removing visible quotes, Excel remembers the text prefix. Solution: Select the range → Home tab → Find & Select → Replace → Find what:
' → Leave “Replace with” blank → Replace All. Then run Text to Columns.
Mistake #2: Cell Formatting Set to “Text” Before Entry
If column B was formatted as Text *before* pasting values, Excel stores everything as text — even numbers typed directly. You’ll see left-aligned entries and no sum in the status bar. Fix: Reformat column as “General” or “Number”, then press F2 → Enter on each cell (or use the double-unary trick:
=--B2 in a helper column).
Mistake #3: Formula Bar Shows =SUM() But Cell Shows Formula Text
This happens when the cell is formatted as Text *and* you pressed Enter while editing. Excel treats the whole thing as literal text. Don’t delete and retype. Instead: press F2 to enter edit mode, add an apostrophe at the start (
'=SUM(B2:B9)), press Enter, then remove the apostrophe and press Enter again. Yes — it’s weird. Yes — it works.
Quick-Reference Fix Matrix
When Excel is not calculating, skip guessing. Use this table to match symptoms to solutions.
| Symptom |
Likely Cause |
Fix Shortcut / Command |
Time to Fix |
| Formula shows in cell, not result |
Cell formatted as Text |
Ctrl+1 → Number tab → General → OK, then F2+Enter |
12 seconds |
| Status bar shows “Count”, not “Sum” |
Numbers stored as text |
Data → Text to Columns → Finish (no delimiter needed) |
8 seconds |
| Formulas update only after manual F9 |
Calculation mode = Manual |
Alt+M+X → Enter (switches to Automatic) |
3 seconds |
| SUM returns 0 despite visible numbers |
Nonbreaking spaces (CHAR(160)) |
=VALUE(SUBSTITUTE(B2,CHAR(160),"")) |
22 seconds (first time) |
