Return a Blank Instead of a Zero

Excel & Google Sheets

How it works

If the value is zero, the formula returns "" — empty text that displays as nothing — otherwise it passes the value through untouched. But that blank is cosmetic, not a real empty cell, and it changes downstream math: AVERAGE skips text, so an average over the display column ignores the zero months entirely and reads higher than the truth, and arithmetic like C2*1.1 on a "" cell returns #VALUE!. Same philosophy as guarding #DIV/0!: hide a value only where people read it, never in a column other formulas calculate from. If you just want zeros invisible, a custom number format (0;-0;;@) hides them with the real numbers intact underneath.

A2=0

The test: is this value exactly zero?

""

Empty text — displays as a blank cell, but it's text, not a true empty.

A2

Everything non-zero passes through unchanged.

When to use it

Use it on presentation copies of reports — sales by rep, spend by category, units by branch — where zeros are noise. Keep the raw numbers in their own column for any math.

Common mistakes

• ✕Averaging the column after blanking zeros.

  ✓AVERAGE ignores "" — with values 0, 250, 350 the true average is 200, but the blanked column averages 300. Point AVERAGE at the raw column, or keep the zeros.

• ✕Doing math on the blanked column.

  ✓C2*1.1 on a cell holding "" returns #VALUE! — "" is text, not a number. Calculate from the original values, and use the blanked column only for display.

• ✕Reaching for the formula when a number format would do.

  ✓The custom format 0;-0;;@ makes zeros invisible while keeping the real number in the cell — sums and averages stay correct. Prefer it when you don't need the value gone, just hidden.