Formula Errors

Fix an Excel for the Web SUM Formula Returning Zero

Kushani NimanthikaKushani Nimanthika Nov 18, 2026 869 views

Question details

The user needs a SUM formula in Excel for the Web to total imported or pasted values correctly.

Fix a SUM Formula Returning Zero in Excel for the Web
Product
Excel for the Web
Device & OS
not provided
Scenario
Summing values that were imported or pasted into a workbook.
Observed behavior
The SUM formula returns zero; the source values may be stored as text or contain hidden characters.
Before you start

Identify the cells included in the SUM formula and check whether the expected values use the decimal and thousands separators supported by the workbook's locale.

Solution 1Recommended

Check and convert values stored as text

Use this method when SUM ignores imported or pasted values because Excel does not recognize them as numbers.

1
Check a source value

In an empty cell, enter =ISNUMBER(A2), replacing A2 with a cell in the SUM range. FALSE indicates that the value is not stored as a number.

2
Inspect the original cell

Select the source cell and inspect its contents in the formula bar. Remove any unexpected characters, then press Enter and check whether ISNUMBER returns TRUE.

3
Convert text values

In an empty helper column, enter =IFERROR(VALUE(SUBSTITUTE(A2,CHAR(160),"")),"") and fill the formula down. This removes nonbreaking spaces and attempts to convert each result to a number.

4
Test the total

Enter a SUM formula over the helper column, such as =SUM(B2:B10). Check any blank conversion results for other hidden characters or mismatched decimal separators.

5
Review the source

If the data came from a query or import, review its source and import settings so that future values are recognized as numbers.

Check and convert values stored as text
Conversion depends on number format: VALUE uses the recognized number format for the workbook's locale. Removing nonbreaking spaces will not fix every hidden character or separator mismatch.
Free Microsoft Office alternative

Work with Spreadsheet Formulas in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel workbooks in WPS Spreadsheets to review values and formulas while moving between office suites.

Review SUM formulas and source values in WPS SpreadsheetsOpen and edit compatible Microsoft Excel XLS and XLSX filesUse a familiar interface for a smoother transition from Microsoft OfficeWork with a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why does SUM return zero when cells appear to contain numbers?

The values may be stored as text or contain hidden characters. Enter =ISNUMBER(A2) in an empty cell, replacing A2 with a source cell, to check whether Excel recognizes it as a number.

Will changing the cell format convert text to numbers?

Changing the format alone generally does not convert existing text values. Edit the cell to remove unwanted characters or use VALUE in a helper column to convert the text.

How can I remove a nonbreaking space from an imported value?

In a helper cell, enter =SUBSTITUTE(A2,CHAR(160),"") to remove nonbreaking spaces. Wrap it in VALUE, as in =VALUE(SUBSTITUTE(A2,CHAR(160),"")), to attempt a numeric conversion.

What should I try if the formula works in a different workbook?

Copy the affected cells to a new worksheet using Paste Special > Values, then test SUM again. You can also try another browser, clear the browser cache, or open the file in desktop Excel.