Why Excel SUM Ignores Some of Your Numbers (and How to Fix It)

You add up a column, the total looks wrong, and you check a few cells. They all contain numbers. So why is SUM ignoring some of them?

In most cases, some of those numbers are not numbers to Excel. They are text that looks like a number. SUM skips text without any warning, so your total is quietly too low.

How to spot numbers stored as text

• Alignment: by default, real numbers sit on the right of a cell and text sits on the left. A column with a mix of both is a warning sign.
• Green triangles: Excel often marks these cells with a small green triangle in the top-left corner. Select the cell and the warning says the number is stored as text.
• The status bar: right-click the status bar and switch on Numerical Count. Select the column. If Count is higher than Numerical Count, some cells hold text.
• A quick formula: =ISTEXT(A2) returns TRUE for any cell that holds text.

Where they come from

• Exports from accounting, CRM or banking systems
• Numbers copied from websites or PDFs, which often bring hidden spaces with them
• A leading apostrophe typed to keep a leading zero, such as '0123
• Currency symbols or commas typed into a cell formatted as Text
• Columns set to Text format before the data was entered

How to fix them

Option 1: Convert to Number

Select the affected cells, click the warning icon that appears next to them, and choose Convert to Number. This is the quickest fix for a single column.

Option 2: Text to Columns

Select the column, go to Data, Text to Columns, and click Finish without changing any settings. Excel re-reads every cell and converts the numbers it recognises.

Option 3: Remove hidden characters

If the first two options do nothing, the cells probably contain hidden characters, often a non-breaking space copied from a web page. In a helper column, use =VALUE(TRIM(SUBSTITUTE(A2,CHAR(160)," "))), then copy the results and paste them back as values.

Option 4: Fix it once in Power Query

If the data arrives from the same export every month, load it through Power Query and set the column type to Decimal Number or Whole Number. The conversion then happens automatically on every refresh.

How to stop it happening again

• Set number columns to a number format before anyone types into them
• Use Data Validation to accept only whole or decimal numbers in those columns
• Bring exports in through Power Query rather than copy and paste
• Check new files for the problem before they feed a dashboard or an AI tool

Why this matters more with AI

A dashboard or AI assistant reading the same column can make the same mistake as SUM, or read the values differently. Either way, the answer does not match the number you expect, and it is hard to see why.