How to Fix Excel AutoSum Not Adding Numbers
Question details
The Excel AutoSum function fails to calculate the sum of selected cells, returning zero or an incorrect total.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Trying to sum a column or row of data using the AutoSum feature, but the formula ignores the values and fails to calculate the total correctly.
- Observed behavior
- AutoSum returns a value of zero or skips specific cells because the underlying data contains numbers formatted as text, hidden spaces, or mismatched decimal separators.
Click on a few cells within your data range and look at the formula bar to check for a leading apostrophe ('), which indicates the number is stored as text.
Convert Numbers Stored as Text to Numeric Values
Imported or pasted data is often formatted as text, which AutoSum ignores. Converting these cells back to numbers is the most common fix.
Excel cannot perform mathematical calculations on text strings. Even if a cell looks like a number, formatting it as text will cause AutoSum to skip it entirely.
Highlight all the cells you are trying to sum that are currently not being calculated.
Look for a small yellow diamond with an exclamation mark appearing next to your selection. This is Excel's error checking tool warning you about numbers stored as text.
Click the error icon to open the drop-down menu and select 'Convert to Number'.
If the icon does not appear, select the column, go to the 'Data' tab, click 'Text to Columns', and immediately click 'Finish' to force Excel to evaluate the data as numbers.

Extract Numbers Using the VALUE Formula
If standard conversion tools fail due to stubborn formatting, use the VALUE function to force a text string into a recognizable numeric value.
Remove Hidden Spaces and Fix Decimal Separators
Non-breaking spaces or mismatched regional decimal settings (like commas instead of periods) prevent Excel from reading values as numbers.
Use WPS Spreadsheet for Flawless Calculations and Easy Data Conversion
WPS Spreadsheet offers an intuitive interface for managing data, performing calculations, and fixing formatting issues effortlessly. With smart built-in error checking, it quickly identifies text formatted as numbers and lets you fix the entire column in one click, ensuring your AutoSum works perfectly every time.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your spreadsheet document.
- 2. Fix data formatting: Highlight the problematic cells, click the smart alert icon that appears, and select 'Convert to Number'.
- 3. Apply AutoSum: Select the empty cell below your data column and click the 'AutoSum' button on the Home or Formulas tab to instantly calculate your total.

Frequently Asked Questions
Why does AutoSum show zero even though there are numbers in the cells?
This happens when the numbers in your cells are actually stored as text. AutoSum only calculates numeric values and completely ignores text strings. You must convert these text values into numbers using the 'Convert to Number' tool or the 'Text to Columns' feature before AutoSum will work.
Can formatting a cell as currency fix the AutoSum issue?
No, simply changing the visual format (like Currency, Accounting, or Number) does not alter the underlying data type if it is already stored as text. Financial formatting alone does not determine whether a value is numeric. You must convert the actual values to numbers first.
How do I remove invisible characters preventing AutoSum from working?
You can use the Find and Replace feature (Ctrl+H) to remove standard spaces. For stubborn non-breaking spaces often imported from web data, you can use the =CLEAN(TRIM(A1)) formula or =VALUE(SUBSTITUTE(A1, CHAR(160), "")) in a helper column to strip the spaces and convert the text to numbers.
Why does AutoSum select the wrong range of cells?
AutoSum looks for contiguous numeric data. If there are blank cells or cells containing text within your column, AutoSum will stop scanning when it hits that gap. You can manually drag the selection box using your mouse to include the entire desired range regardless of gaps.




