Guide Data integrity
SUM is ignoring half my column
The total is far too low, or zero, and the column is plainly full of numbers. They are not numbers — they are text that looks like numbers, and every arithmetic function in Excel skips them without a word.
SUM silently skips text, so a column of numbers stored
as text totals to zero or to only the part that is genuinely numeric. Detect it with
=AND(ISTEXT(A2),ISNUMBER(VALUE(A2))) — TRUE means the cell is being ignored.
Convert with Text to Columns, with Paste Special multiply-by-1, or by re-importing the column as a
number. The most dangerous case is a partly text column, because the total looks
plausible.
Why the wrong total is more dangerous than no total
A SUM that returns 0 is at least obvious. The costly case is the column
where most values are numeric and a handful are text — typically the rows that came from a
different export, or that had a stray space, or that a person typed by hand.
SUM returns a number that is too small but entirely believable. It gets copied into a
report, and nothing about it looks wrong. Excel shows a small green triangle in the corner of the
affected cells, but that is easy to miss and often switched off in a shared workbook.
This is the standard state of anything exported from an accounting or ERP system, where numbers frequently arrive with currency symbols, thousands separators or trailing spaces attached.
| What you see | What actually happened | What fixes it |
|---|---|---|
SUM returns 0 | The entire column is text | Convert the column to numbers |
| The total is too low but plausible | Only some rows are text — the dangerous case | Test every row, not a sample |
| Values are left-aligned | Excel left-aligns text and right-aligns numbers | Alignment is the fastest visual check |
| A small green triangle in the corner | Excel's own 'number stored as text' warning | Select the range and use Convert to Number |
COUNT is lower than COUNTA | COUNT counts numbers only; COUNTA counts anything non-empty | The gap is the number of text cells |
How to find and convert numbers stored as text
Measure it with COUNT against COUNTA
Before converting anything, find out how bad it is:
=COUNTA(A2:A500)-COUNT(A2:A500)COUNTA counts every non-empty cell; COUNT counts only
numeric ones. The difference is the number of cells SUM is ignoring. Zero means the
column is clean.
Flag the exact rows
To see which rows:
=AND(ISTEXT(A2),ISNUMBER(VALUE(A2)))TRUE means the cell holds text that would convert cleanly to a number — exactly
the cells SUM is skipping. Filter on TRUE to inspect them before you convert
anything.
Convert with Text to Columns — the fastest whole-column fix
Select the column, Data → Text to Columns, and press Finish immediately — you do not need any of the wizard's options. This forces Excel to re-parse each value, and the numeric ones become real numbers. It is the fastest fix for a whole column and needs no helper column.
Or multiply by 1 with Paste Special
Type 1 in a spare cell and copy it. Select the affected range, then
Paste Special → Multiply. Multiplying by one forces a numeric conversion in place,
which is useful when Text to Columns would disturb the layout.
If the values carry symbols, strip them first
VALUE fails on anything with a currency symbol or a thousands
separator still attached:
=VALUE(TRIM(SUBSTITUTE(SUBSTITUTE(A2,"$",""),",","")))Extend the nested SUBSTITUTE calls for whichever symbols your export
carries. Wrap the whole thing in IFERROR if some rows are genuinely not numbers.
Everything on this page, as a workbook you can use on your own data
The Excel Data Cleanup Kit — a seven-tab workbook that finds all nine of these faults in a pasted column and hands back a cleaned version, a five-page PDF guide, and a short read-me. Free, no email required.
- Excel-Data-Cleanup-Workbook-Free.xlsx — paste a column, read the diagnosis, take the cleaned output
- Excel-Data-Survival-Guide.pdf — the eight failures, the import routine that prevents them, every formula explained
Plain .xlsx: no macros, no add-ins. Opens in Excel, Google Sheets,
Apple Numbers and LibreOffice Calc.
Why this happens so often to accounting exports
Financial systems export numbers with their formatting attached: $1,234.56,
1 234,56, (500) for a negative, or a trailing space where a currency code
was stripped. Each of those makes the value text as far as Excel is concerned.
Negatives in parentheses are a particular trap. Accountants write (500) for minus
500; Excel usually reads that as text. Convert those explicitly rather than hoping:
=IF(LEFT(TRIM(A2),1)="(",-VALUE(SUBSTITUTE(SUBSTITUTE(A2,"(",""),")","")),VALUE(A2))Detects the bracket form, strips the brackets and negates the result; anything else converts normally.
The opposite problem
Sometimes text is what you want. An ID column of digits should be text — that is what keeps the leading zeros. Do not convert a column to numbers just because it contains digits. Ask whether you would ever do arithmetic on it. If not, leave it as text.
Preventing it at the source
On import, set genuinely numeric columns to a numeric type in Power Query, with the correct locale if the file uses a decimal comma. Getting the type right on the way in avoids the whole conversion exercise.
Questions people ask
Why does SUM return 0 when the column is full of numbers?
They are text that looks numeric. SUM only adds numeric cells and skips text without any warning or error.
How do I tell whether a cell is text or a number?
Numbers align right and text aligns left by default. To be certain, use =ISTEXT(A2), or compare COUNT and COUNTA over the range.
What is the fastest way to convert a whole column?
Select the column, Data > Text to Columns, and click Finish on the first screen. That re-parses every value and converts the numeric ones in place.
Why does VALUE return an error on my cells?
Something non-numeric is still attached — a currency symbol, a thousands separator, a trailing space or accounting brackets. Strip those with SUBSTITUTE and TRIM before applying VALUE.
What is the green triangle in the corner of the cell?
Excel's own warning that a number is stored as text. Selecting the range gives you a Convert to Number option, though it is often disabled in shared workbooks.
Should I convert every column of digits to numbers?
No. IDs, SKUs and postcodes should stay as text — that is what preserves their leading zeros. Only convert columns you would actually do arithmetic on.
Related guides
Take the workbook with you
The Excel Data Cleanup Kit — a seven-tab workbook that finds all nine of these faults in a pasted column and hands back a cleaned version, a five-page PDF guide, and a short read-me. Free, no email required.
- Excel-Data-Cleanup-Workbook-Free.xlsx — paste a column, read the diagnosis, take the cleaned output
- Excel-Data-Survival-Guide.pdf — the eight failures, the import routine that prevents them, every formula explained
Plain .xlsx: no macros, no add-ins. Opens in Excel, Google Sheets,
Apple Numbers and LibreOffice Calc.