Excel Formula Returns 0? Causes and Fixes

When an Excel formula returns 0, it usually isn't broken. It ran, found nothing it could use, and reported zero. The cause is almost always one of seven things: text that looks like numbers, an empty lookup cell, criteria that don't match, a circular reference, Manual calculation, a wrong range, or a number format. This guide shows how to spot each one in under a minute and fix it.

Why does an Excel formula return 0?#

A formula returns 0 when the cells it reads contain no usable numbers or no matches. Excel doesn't show an error in these cases, which is why a 0 is harder to debug than #N/A. Work through this checklist from top to bottom:

Numbers stored as text: the most common cause#

SUM, SUMIF and AVERAGE skip any cell that holds text, even if the text looks like a number. If every cell in the range is text, the total is 0. This often happens with data copied from a website, a PDF or another system.

Signs that numbers are text:

• They are left-aligned while real numbers sit on the right.
• A small green triangle appears in the top-left corner of the cell.
• When you select them, the status bar shows a Count but no Sum.

To fix them, select the cells, click the warning icon and choose Convert to Number. Or leave the data alone and convert inside the formula:

=SUMPRODUCT(--B2:B6)

The double minus turns each text number into a real one. Our guide to numbers stored as text covers every conversion method, and why SUM shows 0 walks through the SUM-only cases. The SUM function guide is a quick reference for the syntax.

Lookup formulas that return 0 instead of blank#

VLOOKUP, XLOOKUP and INDEX return 0 when they find the right row but the cell they bring back is empty. Excel treats an empty cell as zero in a formula result. So =VLOOKUP("P-104",A2:C8,3,FALSE) shows 0 if the price for P-104 hasn't been entered yet.

That 0 can mislead people, because it looks like a real price. Return a blank instead:

=IF(VLOOKUP(F2,A2:C8,3,FALSE)="","",VLOOKUP(F2,A2:C8,3,FALSE))

A shorter trick is to add &"" to the end of the lookup. It returns an empty string for blanks, but it also turns real numbers into text, so only use it for text columns. The article on returning a blank instead of 0 in VLOOKUP compares both methods.

SUMIF, COUNTIF and SUMIFS returning 0#

A conditional formula returns 0 when no row matches the criteria exactly. The most common reasons are small and invisible:

1. Extra spaces. "Paid " with a trailing space does not match "Paid". Clean the data with TRIM, or use a wildcard: "Paid*".
2. Typos or different words. "Complete" and "Completed" are different values.
3. Numbers stored as text in the column you check or the column you add.
4. A cell reference inside quotes. ">=G2" compares against the letters G and 2. Write ">="&G2.
5. Ranges on different rows. If the sum range starts one row lower than the criteria range, the matches add the wrong cells.

Here is a quick test for hidden spaces. If B2 shows Paid, type =LEN(B2) in a spare cell. A result of 5 instead of 4 means there is an extra character.

Circular references and formulas that show 0#

A circular reference happens when a formula refers to its own cell, directly or through other formulas. For example, =SUM(B2:B10) typed in B10 includes itself. Excel shows a warning, and the cell usually displays 0.

To find it, go to Formulas > Error Checking > Circular References. Excel lists the cell. Change the range so it stops one row above the formula, such as =SUM(B2:B9). The guide to finding and fixing a circular reference explains the less obvious cases, where the loop passes through other sheets.

When the formula shows 0 because it isn't recalculating#

If the result stays at 0 even after you type new numbers, Excel may be set to Manual calculation. The formula still holds the value it had when the cells were empty.

Fix it in Formulas > Calculation Options > Automatic. To recalculate once without changing the setting, press F9 on Windows (on a Mac, F9 or ⌘=). Manual mode is often switched on by a workbook you opened earlier, because Excel takes the setting from the first file opened. See why a formula is not updating for the other causes, such as formulas saved as text.

Wrong ranges, empty cells and number formats#

The last group of causes is about what the formula points at and how the answer is displayed.

• Empty input cells. =B2*C2 returns 0 when C2 is blank. Check that the input cells are filled in.
• A range that points at the wrong place. After rows are inserted or data moves, a formula may now read empty cells. Click the cell and press F2 to see the coloured range borders. On a Mac, use Control+U if F2 doesn't work.
• MIN or MAX with no numbers. Both return 0 when the range holds only text or blanks.
• A number format that hides decimals. A cell that holds 0.4 and is formatted with no decimal places shows 0. Click it and read the formula bar, or use Increase Decimal on the Home tab.

Practise it#

Finding the reason behind a 0 is a skill you build by fixing real sheets. Practise debugging SUM in a live spreadsheet: you'll find a range that misses a row, numbers stored as text and a total that counts itself, and the sheet checks each fix instantly. Then try fixing lookup errors to handle missing and empty lookup results. Start with the exercise on this page: the wildcard turns a 0 into the real paid total.