Excel Sum Showing Zero? Fixes That Work

How-to basics · Updated November 22, 2026

If your Excel SUM formula returns 0 when it should show a total, the cause is almost always that the cells contain text instead of numbers, or the cells are formatted as text. To fix it, select the cells, click the warning icon that appears, and choose Convert to Number; if that doesn’t work, use the VALUE function or re-enter the numbers.

Why SUM Returns 0: The Text Number Problem

Excel treats text and numbers differently. A cell that looks like a number (e.g., “123”) but is actually stored as text will be ignored by SUM. This is the #1 reason for a zero total.

How to check: select the cells and look at the Status Bar at the bottom of Excel. If it shows only “Count” and not “Sum,” then at least one cell is text. Also, text-formatted numbers align left by default, while real numbers align right.

How to Fix Text Numbers Quickly

Here are the fastest ways to convert text to numbers:

  • Use the Error Warning: Select the cells, click the yellow diamond icon, and choose Convert to Number.
  • Use Paste Special: Type 1 in any empty cell, copy it, select your data, right-click > Paste Special > Multiply. This forces Excel to treat them as numbers.
  • Use Text to Columns: Select the column, go to Data > Text to Columns, click Finish (no need to change steps).
  • Use the VALUE function: In a new column, enter =VALUE(A1) and drag down.

Check for Hidden Characters or Spaces

Sometimes numbers have invisible spaces or non-breaking spaces (character code 160) imported from web pages. These make the cell text. Use the TRIM function to remove regular spaces, and CLEAN to remove non-printing characters.

With data in A1:A10, in B1 enter: =TRIM(CLEAN(A1)) and copy down. Then sum column B.

Formula Bar and Cell Formatting Misconceptions

Many users think that setting the cell format to “Number” fixes it. It does not. Changing format only changes how the value is displayed; it does not change text to a number. You must actually convert the data.

Also, check that your SUM range is correct. If you accidentally include a header or a cell that contains text, SUM will skip it but still total the numeric cells. If all cells are text, SUM returns 0.

Circular References and Calculation Mode

If your formula refers to itself (directly or indirectly), Excel shows 0 or a warning. Go to Formulas > Error Checking > Circular References to see if any exist.

Also, check Calculation Options: if set to Manual, formulas won’t update. Press F9 to recalculate, or go to Formulas > Calculation Options > Automatic.

When the Cell Contains a Formula That Returns Text

If your numbers are the result of a formula (e.g., =LEFT(A1,3)), that formula might return text. Wrap it in VALUE or multiply by 1. For example: =VALUE(LEFT(A1,3)).

Frequently Asked Questions

Why does my SUM formula show 0 even after converting to number?

If you still see 0, check for leading apostrophes (‘) in the formula bar. An apostrophe forces text. Use Find & Replace: select the range, press Ctrl+H, find ', replace with nothing. Also, check for spaces before the number.

How do I sum cells with numbers stored as text in different columns?

Use the SUMPRODUCT function with VALUE: =SUMPRODUCT(--(A1:A10)) or =SUMPRODUCT(VALUE(A1:A10)) (the latter requires Ctrl+Shift+Enter in older Excel). This forces each cell to be evaluated as a number.

My SUM works on some rows but not others. Why?

That means some cells are real numbers and some are text. Convert the text ones using the methods above. Check the Status Bar for “Sum” to see which rows are being counted.

Does Excel for Mac have the same issue?

Yes, the same text-vs-number issue exists on Mac. The conversion options are similar: select cells, go to the Data tab, choose Text to Columns, or use the error warning (which appears as a small triangle).

Can I prevent this from happening?

Yes. Before entering data, format the cells as Number or General. When importing data, use the Import Wizard and set each column’s data type to “General” or “Number”. Avoid copying numbers from web pages directly.

Final Checks and a Simple Test

To confirm the fix, type a new number in an empty cell and sum it with your range. If the total updates, you’re good. If not, check for a hidden filter that might be hiding rows, or ensure the sheet isn’t protected.

Remember: Excel’s SUM function ignores text, logical values (TRUE/FALSE), and empty cells. So a zero result always points to the data being text, not a broken formula.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.