How to diagnose why excel sum returns zero by finding text-stored numbers
- Step 1Confirm SUM is the symptom — If
=SUM(range)returns 0 or a number lower than the visible figures suggest, you almost certainly have text-stored numbers. A quick test in Excel:=COUNT(range)counts only numeric cells; if it's lower than the row count, the difference is your text cells. This inspector finds exactly which ones. - Step 2Save the file as .xlsx — The inspector reads
.xlsxonly — it needs the per-cell type metadata to tell text from number. If your data is a CSV, open it in Excel and Save As an Excel Workbook first; a CSV has no type info to compare. - Step 3Drop the workbook onto the tool — SheetJS parses it locally in your browser. No upload. The scan reads every sheet, so even if your SUM references a value on another tab, that tab is audited too.
- Step 4Read the list of text-stored numbers — Each line is
[Sheet] Cell: stored as text, looks like number ("value"). These are precisely the cells SUM is skipping. The count panel shows how many there are total; the report text lists the first 100 inline. - Step 5Convert the flagged cells in Excel — Select the column (or the flagged range), click the yellow warning diamond → 'Convert to Number'. For a whole column at once: Data → Text to Columns → Next → Next → Finish, which re-parses each cell as a number. The inspector tells you which cells need it; Excel does the conversion.
- Step 6Re-scan and re-check SUM — Drop the fixed file back in. 'No format mismatches found' means every numeric cell is now a real number — recalculate and your SUM should total correctly. If a residual cell remains (e.g. an accounting-paren negative the detector skips), the re-scan helps you narrow it down.
Why SUM returns zero — and what the inspector does about it
How text-stored numbers manifest in aggregation formulas, and what the detector reports for each.
| Symptom | Cause | Inspector behaviour |
|---|---|---|
=SUM(range) returns 0 | Every cell in the range is text | Flags all of them as 'looks like number' |
=SUM(range) returns a too-small total | Only some cells are text; SUM skips those | Flags only the text cells — pinpoints the partial offenders |
=AVERAGE(range) returns #DIV/0! | Zero numeric cells (all text) | Flags every cell so you know none are numeric |
=COUNT(range) lower than row count | The gap equals the number of text cells | The report count matches that gap |
(1,234.00) accounting negatives ignored by SUM | Parentheses make it text-and-unparseable | NOT flagged — convert parens to minus first |
$1,234 ignored by SUM | Currency symbol makes it text | NOT flagged — strip the $ first, then re-scan |
Detector specifics for numeric cells
Exactly which text values the number detector matches, per the implementation.
| Text value | Matched as number? | Note |
|---|---|---|
1234 | Yes | Plain integer |
1,234.56 | Yes | Commas stripped before the test |
-50 | Yes | Leading minus allowed |
12.00 | Yes | Decimal allowed |
00045 | Yes | Pure digits — flagged even if zero-padded |
(1,234) | No | Parentheses break the pattern |
$50 | No | Currency symbol breaks the pattern |
50% | No | Percent sign breaks the pattern |
SKU-12 | No | Letters break the pattern |
Cookbook
Real SUM-returns-zero diagnoses. Each shows the formula problem, the inspector's report, and the fix.
Whole column is text — SUM returns exactly zero
A revenue column pasted from a web report is entirely text. SUM returns 0 even though the visible numbers are large. The inspector flags every cell, confirming the whole column needs converting.
=SUM(B2:B5) -> 0
Sheet 'Revenue', column B:
4500 (text)
3200 (text)
7800 (text)
1100 (text)
Inspector report:
Found 4 format mismatch(es):
[Revenue] B2: stored as text, looks like number ("4500")
[Revenue] B3: stored as text, looks like number ("3200")
[Revenue] B4: stored as text, looks like number ("7800")
[Revenue] B5: stored as text, looks like number ("1100")
Fix: select B2:B5 -> warning diamond -> Convert to Number
Then =SUM(B2:B5) -> 16600Partial-range — SUM is too small, not zero
The trickier bug: most cells are real numbers but two were pasted as text. SUM returns a believable-but-wrong total. The inspector flags only the two offenders so you fix exactly those.
=SUM(C2:C5) -> 900 (should be 1500)
Sheet 'Sales', column C:
C2 = 500 (real number)
C3 = 400 (real number)
C4 = 350 (text) <- skipped by SUM
C5 = 250 (text) <- skipped by SUM
Inspector report:
Found 2 format mismatch(es):
[Sales] C4: stored as text, looks like number ("350")
[Sales] C5: stored as text, looks like number ("250")
Fix C4:C5 -> =SUM(C2:C5) -> 1500Comma-formatted figures ignored by SUM
Amounts were stored as text with thousands commas. SUM ignores them all. The detector strips the commas internally, so it correctly recognises them as text-stored numbers.
=SUM(D2:D4) -> 0
Sheet 'P&L', column D:
1,234.56 (text)
98,000 (text)
12.50 (text)
Inspector report:
Found 3 format mismatch(es):
[P&L] D2: stored as text, looks like number ("1,234.56")
[P&L] D3: stored as text, looks like number ("98,000")
[P&L] D4: stored as text, looks like number ("12.50")AVERAGE returns #DIV/0! because every cell is text
When a whole column is text, AVERAGE has zero numeric cells to divide by and errors. The inspector flags every cell, confirming the column is 100% text.
=AVERAGE(E2:E4) -> #DIV/0!
Sheet 'Scores', column E:
88 (text)
91 (text)
76 (text)
Inspector report:
Found 3 format mismatch(es):
[Scores] E2: stored as text, looks like number ("88")
[Scores] E3: stored as text, looks like number ("91")
[Scores] E4: stored as text, looks like number ("76")Accounting negatives the SUM still misses after the obvious fix
You fixed the plain text numbers, but SUM is still slightly off. The reason: parenthesised negatives like (1,234) are text the detector does NOT flag. Replace the parens with a minus sign in Excel, then re-scan to confirm.
Sheet 'Ledger', column F:
5000 (text -> flagged, you fixed it)
(1,234) (text -> NOT flagged by detector)
First scan: 1 mismatch (the 5000) — you fix it.
SUM still wrong because (1,234) is text & unparseable.
Fix in Excel: Find & Replace ( -> - and ) -> nothing
so (1,234) becomes -1234, then re-scan:
[Ledger] F3: stored as text, looks like number ("-1234")
Convert -> SUM now correct.Edge cases and what actually happens
CSV uploaded instead of XLSX
RejectedThe detector needs XLSX per-cell type metadata to tell text from number. In a CSV every value is text, so there's nothing to compare. Open the CSV in Excel, Save As an Excel Workbook, then scan — or clean the CSV directly with a CSV-side tool.
Accounting parentheses negatives
Not detected(1,234.00) doesn't match the numeric pattern because of the parentheses, so it's not flagged — yet SUM still ignores it. If your SUM is off after fixing the obvious cells, replace ( and ) with a minus sign in Excel and re-scan to catch these.
Currency symbol or percent in the cell
Not detected$1,234 and 50% are text the SUM ignores, but the leading $ and trailing % break the numeric pattern so they aren't flagged. Strip the symbol with Find & Replace first; once the cell is bare digits-and-commas it's caught on the next scan.
SUM returns 0 but the inspector finds nothing
Look elsewhereIf the scan is clean but SUM is still 0, the cause is probably outside this detector's scope: parenthesised/currency text (see above), a calculation set to Manual (press F9), the range pointing at the wrong cells, or hidden filtered rows excluded by SUBTOTAL. The inspector only catches plain text numbers and text dates.
Leading-zero codes flagged
Expected00045 is pure digits, so it's flagged as 'looks like number'. If that column is a SUM target, convert it. If it's an ID you want to keep zero-padded, leave it as text — converting would strip the zeros. The detector flags; you decide based on whether SUM should include it.
Whole-column text vs partial text
Both reportedThe detector doesn't care whether it's the whole range or two stray cells — it reports every text-stored numeric cell. The partial case is the valuable one: SUM returns a plausible-but-wrong total, and the report tells you exactly which two cells are missing from the sum.
Report truncated at 100 lines
Truncated displayA massively broken column may exceed the 100-line inline cap; the report appends '... and N more.' and the count panel shows the real total. When the count is large, fix the whole column at once in Excel (Text to Columns) rather than chasing individual cells.
Numbers already correct
PreservedCells already stored as real numbers (t === "n") are skipped — they're what SUM already adds correctly. The inspector never flags a working numeric cell, so a clean column produces no noise and confirms SUM will behave.
File over the Free-tier size cap
Rejected (size limit)Free tier allows 5 MB / 10,000 rows. A bigger ledger is rejected before scanning. Upgrade to Pro (50 MB / 100,000 rows) for large finance workbooks, or split by sheet and scan each piece.
Text dates mixed into a numeric column
Flagged as dateIf a column you SUM accidentally contains a value like 2024/01/15, it's flagged 'looks like date' rather than 'looks like number'. SUM would skip it either way; the date label tells you it's not a stray amount but a misplaced date that needs different handling.
Frequently asked questions
Why does my Excel SUM return zero?
Almost always because the cells in the range are numbers stored as text. SUM (and AVERAGE, SUMIF, SUMPRODUCT) silently ignore text values — they don't error, they just skip them, so SUM returns 0 (if all text) or a too-small total (if some text). This inspector finds the exact text cells so you can convert them and get the right total.
Does this tool convert the cells so SUM works again?
No — it detects, it doesn't convert. You get a report of exactly which cells are text-stored numbers. The fix is one click in Excel: select the cells, hit the warning diamond → 'Convert to Number', or use Data → Text to Columns → Finish on the column. The inspector removes the guesswork of finding which cells are broken.
How can I confirm text numbers are the cause before scanning?
In Excel, put =COUNT(yourRange) next to your SUM. COUNT only counts numeric cells. If COUNT is lower than the number of filled rows, the difference is your text cells — and that's exactly what this inspector will list. If COUNT equals the row count but SUM is still wrong, the cause is something else (manual calc, wrong range, parenthesised negatives).
It found nothing but SUM is still zero — now what?
The detector only catches plain text numbers and numeric-shaped text dates. If SUM is still wrong with a clean scan, check: parenthesised negatives like (1,234) and currency text like $50 (the detector skips these — convert them first), calculation set to Manual (press F9), the SUM range pointing at the wrong cells, or SUBTOTAL excluding filtered rows.
Will it scan a column on a different sheet that my SUM references?
Yes. The scan walks every sheet in the workbook, including hidden ones. So if your SUM pulls from a value on another tab and that tab has text-stored numbers, they'll appear in the report with the correct sheet name.
What about thousands separators like 1,234.56?
Handled. The number detector strips commas before testing, so 1,234.56 stored as text is correctly recognised as a text-stored number that SUM is ignoring. It's reported as 'looks like number' with the original value shown.
Are my financial figures sent to a server?
No. The scan runs entirely in your browser with SheetJS. Your ledger, revenue, and totals never leave your machine. Free tier requires no upload and no account; only an anonymous usage counter may be recorded for signed-in users, never file content.
What file types does it accept?
.xlsx only. It needs the per-cell type metadata that XLSX stores. CSV has no type metadata (everything is text). If your data is a CSV, open it in Excel and Save As an Excel Workbook first.
How big a file can I scan?
Free tier: 5 MB / 10,000 rows. Pro: 50 MB / 100,000 rows. Pro-media: 200 MB / 500,000 rows. Developer: 500 MB / unlimited. Files over your tier's limit are rejected before scanning; split a huge workbook by sheet if needed.
What's the fastest way to fix a whole text-number column?
In Excel, select the column, go Data → Text to Columns → Next → Next → Finish. Excel re-parses every cell as a number in one move. Or select the range and use the warning diamond → 'Convert to Number'. The inspector tells you which columns need it; this is the bulk fix.
Does it tell me how many cells SUM is missing?
Yes — the count panel above the report shows the total number of mismatches found, which equals the number of text cells SUM is skipping in those columns. The report text then lists each cell (first 100 inline) so you know precisely where they are.
What other tools help debug a broken Excel formula?
After fixing types, the error locator finds #VALUE!/#REF!/#DIV/0! cells, the formula explainer breaks down a complex SUMIFS, and the dependency map shows which cells feed your total. Use this inspector first to rule out the most common cause — text-stored numbers.
Privacy first
Every JAD Excel tool runs entirely in your browser using SheetJS and ExcelJS. Your spreadsheets, formulas, and data never leave your device — verified by zero outbound network requests during processing.