Excel SUM Not Working Stop Making This Mistake!
Your numbers are RIGHT THERE, but SUM says 0 — and every cell wears a tiny green triangle. Excel is storing your numbers as TEXT, and SUM silently skips text. No error. Just silence. Here are all 6 fixes for Excel and Google Sheets — including the bomb most guides get wrong (changing the format to Number converts NOTHING) and the locked #6: the one click that silently DESTROYS product codes.
Screenshot the decision card at the end and route yourself to your exact fix.
Timestamps
00:00 SUM = 0?! The montage + the bomb (format ≠ convert)
00:29 Fix 1 — Imported as text: the ⚠ dropdown → Convert to Number (one click)
01:07 Fix 2 — The format LIES: re-enter (F2) or Data → Text to Columns → Finish
01:47 Fix 3 — The apostrophe liar: SUM is wrong but not 0 — hunt the ' in the formula bar
02:27 Fix 4 — The ghost space CHAR(160): LEN test, then VALUE + TRIM + SUBSTITUTE
03:09 Fix 5 — No triangles at all? Re-enable background error checking
03:49 Fix 6 (locked) — Convert DESTROYS codes: 00123 → 123 (keep codes as text!)
04:30 3 bonus power-moves (ISNUMBER · COUNT vs COUNTA · prevention)
05:06 Screenshot-this decision card + what is next
THE 6 FIXES
1. Imported/exported data arrives as text — select the range, click the yellow warning dropdown, Convert to Number. Whole selection, one click. (Google Sheets has NO triangle — fakes lean left; use a =VALUE helper.)
2. The format LIES — switching the format to Number does NOT convert existing text. Re-enter the cell (F2, Enter), or bulk-convert a column with Data → Text to Columns → Finish.
3. The apostrophe liar — SUM is wrong but not zero: one cell holds '85. The apostrophe never displays and Find and Replace cannot find it. F2, delete it — or copy a 1 and Paste Special → Multiply over the range.
4. The ghost space — pasted from the web/PDF, no triangle, and =VALUE throws #VALUE!. Diagnose with =LEN (4 chars on a 3-digit number) and =CODE (160 = non-breaking space). Fix: =VALUE(TRIM(SUBSTITUTE(A2, CHAR(160), ""))) — SUBSTITUTE first, then TRIM.
5. No triangles anywhere — background error checking is switched off. File → Options → Formulas → Enable background error checking (also check the rules list + Reset Ignored Errors, same pane). In Sheets, alignment IS your triangle: fakes lean left.
6. LOCKED — Convert to Number DESTROYS codes: 00123 becomes 123. Codes, zips, IDs and phone numbers are labels, not maths — their triangle is a question, not an error. Convert amounts; keep codes as text (Ignore Error). Ctrl+Z resurrects the zeros while the file is open — save and CLOSE, and they are gone for good.
BONUS
=ISNUMBER(A2) → FALSE means fake. Identical in Excel and Sheets.
COUNT vs COUNTA: COUNT 0, COUNTA 5 → five fakes, counted before you fix one.
Prevention: format the column BEFORE data goes in; never double-click a CSV — import via Data → Get Data so types are assigned on arrival.
Works in Excel and Google Sheets.
Subscribe for more spreadsheet fixes.
Next up: the #REF! error — the one that ambushes you when you delete a row or copy a formula.
#Excel #GoogleSheets #ExcelTips