Google Sheets If Zero Then Blank: Formulas That Work

Yes. To show a blank instead of a zero in Google Sheets, use an IF formula such as =IF(A1=0,"",A1) when the value is in A1. If you want to keep the value as a number and only hide its appearance, apply a custom number format instead.

How the IF formula works

IF checks a condition and returns one result when it is true and another when it is false. In this example, the condition is whether A1 equals zero. If it does, the formula returns "", a zero-length text string that looks blank; otherwise, it returns A1’s value. Put the formula in a different cell from A1, because a formula cannot replace its own input.

For a list, enter the formula in the first result cell and fill it down. For example, if the source values are in A2:A20, put this in B2 and copy or drag it down through B20: =IF(A2=0,"",A2). Each row checks its corresponding A cell. You can use the fill handle at the lower-right corner of the selected cell to copy the formula to adjacent rows.

This technique changes the formula’s result, not the original input. Keep the source column intact if you may need to audit or reuse its values, and put the display formula in a separate column. A blank-looking result can be more readable in a report, but the distinction between an actual empty cell and a formula returning an empty string matters in other formulas.

Keep empty inputs blank too

A common surprise is that a genuinely empty cell can behave like zero in a numeric comparison. As a result, the simple IF formula may return a blank for both an empty input and a numeric zero. That can be exactly right, but if you want to handle the empty input explicitly, test for it first.

With a source value in A1, use this formula in another cell to leave an empty input blank and also suppress a numeric zero: =IF(A1="","",IF(A1=0,"",A1)). The first test catches the empty input; the second catches zero; any other value is passed through. This is useful when a column is filled gradually and you do not want unfinished rows to look like completed zero results.

For a calculated result, make sure the inputs are present before calculating. If quantity is in B2 and unit price is in C2, use this formula in D2: =IF(AND(B2<>"",C2<>""),IF(B2*C2=0,"",B2*C2),""). It leaves D2 blank if either input is missing, hides a product of zero, and otherwise shows the product. For instance, 3 and 12 return 36; 3 and 0 return a blank. This approach also avoids treating a not-yet-entered quantity or price as a meaningful zero.

Hide zero results from calculations

If a formula already calculates a value, wrap its calculation in IF to replace a zero result with an empty string. With quantity in B2 and unit price in C2, this formula in D2 shows their product unless it is zero: =IF(B2*C2=0,"",B2*C2). If B2 is 3 and C2 is 12, the result is 36; if either is zero, the displayed result is blank.

Use this approach when the output itself should behave like blank text in a report. One limitation is that the expression is written twice: once to check for zero and again to return the result. For a simple multiplication that is easy to maintain. For a longer calculation, consider putting the calculation in a helper column, then use an IF formula on that helper result. A helper column can make a workbook easier to inspect and troubleshoot.

Be deliberate about which zero you are suppressing. A zero balance, zero quantity, or zero difference may be meaningful information. If a zero indicates a real outcome rather than missing information, hiding it could make a reader assume the calculation has not been completed. A label, note, or separate status column can clarify that distinction.

Hide zeros with a custom number format

If you want zeros to remain numeric but not appear on the sheet, use a number format rather than changing the formula result. Select the cells, then choose Format > Number > Custom number format. Enter #,##0.##;-#,##0.##;;@ and select Apply. The format sections represent positive numbers, negative numbers, zero, and text, in that order. The empty third section means zero values are not displayed.

This pattern displays numbers with a thousands separator and up to two decimal places; adjust the positive and negative sections to suit your data. It changes only how the selected cells look. A cell containing numeric zero is still zero, so other formulas can use it as a number and tests such as comparisons still see the underlying value. This is often the better choice for calculation ranges, tables where zeros must remain usable, or a report that should look cleaner without changing its data.

Formatting is not deletion. A hidden zero can still affect a sum, comparison, chart, or later formula, and selecting the cell will show its value in the formula bar. If a reader needs to know that zeros have been hidden, add a short note near the table. Also remember that the format applies only to the range you selected; it is not a universal setting that automatically changes every cell in the spreadsheet.

Choose between a formula and formatting

The right method depends on whether you want to change the result or only its appearance. Use an IF formula when a zero should become a blank-looking output for a particular calculation or report. Use a custom number format when the underlying numeric zero should remain available but should not be printed on screen.

Choose IF when you want to set a rule for a particular result, such as leaving an incomplete row blank or suppressing a zero total.
Choose number formatting when values must stay numeric for calculations, sorting, or further analysis, and only their display should change.
Keep zero visible when it communicates a real result, such as no sales, no balance, or no change.

These approaches can also be combined, but doing so is rarely necessary. A formula that returns an empty string already looks blank, while a custom format can hide numeric zeros in other cells. Before applying both, check whether the extra rule makes the sheet harder for someone else to understand.

What blank-looking cells mean for other formulas

A formula that returns "" does not create a truly empty cell. For example, ISBLANK will generally report FALSE for a cell containing an IF formula, even when that formula displays nothing. COUNTA also counts formula cells that return an empty string, so it may include them in a count of non-empty cells. COUNT, by contrast, counts numeric values and does not count the empty text result as a number.

SUM normally ignores text values in a referenced range, so a blank-string result can be useful in a column of calculated amounts. Still, check the exact function and range used in your workbook rather than assuming every function treats it like a physically empty cell. If a later formula needs to tell the difference between a true empty input and a formula-produced blank, use a test designed for that distinction or refer to the original input column.

Charts and filters can also behave differently depending on whether a cell is truly empty, contains an empty string, or contains a numeric zero. If the display matters in a chart, inspect the chart after changing the formula and review its setup for empty cells. For data exports or processes that require genuinely empty cells, a formula-generated blank may not be equivalent to deleting the cell’s contents.

Common problems and fixes

The zero still appears: Check that the formula is in the output cell and that it references the intended source cell. A custom format affects only selected cells, so confirm the range and the zero section of the format.
All rows look blank: Check for a wrong or fixed reference, or for source cells that are empty. When filling a formula down, a relative reference such as A2 should change to A3, A4, and so on.
A blank input is mistaken for zero: Add an empty-input test before the zero test, as in the earlier nested IF example.
The formula shows an error: Confirm that the source contains the expected type of value and that the calculation itself is valid. IF does not automatically hide errors; handle an error separately only if hiding it is appropriate.
The formula is rejected: Google Sheets uses the spreadsheet’s locale to determine formula separators. If commas are not accepted, check the locale settings; some locales use semicolons between arguments.

Do not use IFERROR merely to make unwanted zeros disappear. IFERROR is for returning an alternative when a formula produces an error, while zero is a valid numeric result. Hiding errors can conceal problems such as a missing denominator or an invalid reference, so use it only when the error condition is expected and the alternative is clear.

How this differs from Excel

The basic IF pattern is familiar in both Google Sheets and Excel, but this guidance is for Sheets in a web browser. Google Sheets provides the custom-format path Format > Number > Custom number format. Excel has its own number-format controls, so do not assume its menu path is identical. In either spreadsheet, the key distinction is the same: an IF formula changes the returned value to blank-looking text, while a number format changes only the display of a numeric zero.

Frequently Asked Questions

What formula makes zero blank in Google Sheets?
With the value in A1 and the formula in another cell, use =IF(A1=0,"",A1). It returns an empty string for zero and otherwise returns A1.

How do I hide zeros without changing the value?
Select the cells, go to Format > Number > Custom number format, and use a format with an empty zero section, such as #,##0.##;-#,##0.##;;@. The zero remains numeric.

Why does my formula make empty cells look blank?
In a numeric comparison, an empty cell can behave like zero. Test for an empty input first if you need to handle it separately, for example with a nested IF formula.

Does an IF formula returning an empty string make a truly blank cell?
No. The cell still contains a formula, even though it looks empty. Functions such as ISBLANK and COUNTA can distinguish it from a cell with no contents.

Can I hide zeros in just one column?
Yes. Select that column or the specific range, then apply the custom number format. For a formula-based rule, place the IF formula in the output column and fill it down only as far as needed.

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.