Google Sheets IF Zero: How to Handle Zero Values in Formulas

To test whether a cell equals zero in Google Sheets, use an IF formula such as =IF(A2=0,"Zero","Not zero"). This assumes the value to check is in A2; if A2 contains the number 0, the formula returns “Zero,” and otherwise it returns “Not zero.”

What “IF zero” means in Google Sheets

“Google Sheets IF zero” usually means using the IF function to check whether a value is 0 and then choose what the spreadsheet should show or calculate. The test can drive a label, a blank-looking result, a calculation, or a warning. The cell containing the formula can be somewhere else in the sheet; the reference A2 is only an example.

IF has three parts: a condition, the result when that condition is true, and the result when it is false. In the example above, the condition is A2=0. Sheets evaluates it as true or false, then returns the matching text. The same idea works with other outputs, including numbers and formulas.

For instance, with a score in A2, you might want a status that distinguishes a zero score from a positive score. With an inventory quantity in A2, you could show “Out of stock” when the quantity is zero. The IF function does not change the original value in A2; it produces a separate result in the cell where you enter the formula.

Build a basic zero test

Enter the formula in an empty cell and replace A2 with the cell you want to examine. If A2 contains 0, =IF(A2=0,"Zero","Not zero") returns “Zero.” If A2 contains 12, it returns “Not zero.” That makes the formula useful for status columns, checks, and simple decision rules.

You can also return a number rather than a label. With a numeric value in A2, =IF(A2=0,0,1) returns 0 when A2 is zero and 1 for any other value. This can be useful when another formula expects a numeric indicator. For example, a later SUM can add the indicators to count how many tested cells are nonzero, provided each row uses the same logic.

To return the value from A2 when it is not zero, but show a blank-looking result when it is zero, place the following in another cell. With the value in A2, =IF(A2=0,"",A2) returns an empty string when A2 is 0 and returns A2’s value otherwise. This changes only the displayed result of the formula cell; A2 itself remains unchanged.

Google Sheets uses commas between formula arguments in many language and region settings. Some locales use semicolons instead, so a formula copied from another spreadsheet may need its separators adjusted. The IF function and the zero comparison are the same; the separator depends on the spreadsheet’s locale.

Distinguish zero from a blank cell

A blank cell, a formula that returns an empty string, the number 0, and text that looks like “0” are not necessarily interchangeable. That distinction matters when a report should say “Missing” for an empty entry but “Zero” for an actual recorded value. A simple comparison with zero is not the best way to label all four cases.

With the value being checked in A2, use =IF(A2="","Blank",IF(AND(ISNUMBER(A2),A2=0),"Numeric zero","Other")). A genuinely empty A2 returns “Blank”; the number 0 returns “Numeric zero”; and a number such as 8 or text such as “0” returns “Other.” The ISNUMBER check makes the test specifically about a numeric zero. A2=”” also treats a formula result of an empty string as blank for this purpose.

This distinction is common in imported data. A source system may store a zero as text, or it may leave a field empty when no value was supplied. If a number stored as text needs to participate in arithmetic, check the source data and convert it deliberately rather than assuming it is already numeric. Applying Format > Number changes how values are displayed; it does not reliably convert every text entry into a number.

Think about the meaning of a blank before treating it as zero. For a sales report, zero may mean a confirmed day with no sales, while blank may mean the data has not arrived. Replacing both with the same label can make a report look complete when information is actually missing.

Use IF when zero affects another calculation

A common reason to test for zero is to avoid an unwanted calculation, especially division by zero. Suppose A2 contains an amount and B2 contains the number of units. Put this formula in another cell: =IF(B2=0,"",A2/B2). If A2 is 12 and B2 is 0, the result is blank; if A2 is 12 and B2 is 3, the result is 4.

This explicit test is useful when zero has a specific meaning and you want the formula to handle that case differently. If you want to catch errors from any cause, including errors unrelated to division by zero, IFERROR is broader. With the numerator in A2 and denominator in B2, =IFERROR(A2/B2,"") returns a blank-looking result when the division produces an error; with 12 and 0, respectively, it returns blank, and with 12 and 3 it returns 4.

Choose between IF and IFERROR based on what should happen. IF makes the zero rule visible and leaves unrelated errors available to investigate. IFERROR can conceal other problems as well, such as a nonnumeric entry that causes the calculation to fail. During setup, showing a clear message such as “Check input” may be more useful than hiding every error.

Hide zero values without changing the data

Sometimes the goal is not to change a formula’s result, but simply to make zeros less prominent in a table. Google Sheets lets you apply a custom number format. Select the cells, then choose Format > Number > Custom number format. For whole numbers, a format such as 0;-0;;@ displays positive values normally, negative values with a minus sign, hides numeric zero, and leaves text governed by the text section.

For values displayed to two decimal places, 0.00;-0.00;;@ uses the same four-section pattern with two decimal places. Custom number formats affect appearance, not the stored value: a hidden zero is still zero for sums, comparisons, and other formulas. A reader can also see the value in the formula bar when selecting the cell.

Be cautious with decimal formats. A small nonzero amount can appear as 0.00 when rounded for display, even though it is not actually zero. If the underlying value should be treated as zero only after rounding, make that rule explicit in a formula. With a measurement in B2, =IF(ROUND(B2,2)=0,"Treat as zero","Keep amount") returns “Treat as zero” when B2 is 0.004, because rounding to two decimal places produces 0.00. Use that approach only when the rounding rule matches the meaning of your data.

Formatting is a better choice than an IF formula when the values need to remain available for calculations but the table should look cleaner. An IF formula is better when zero should lead to a different result, such as a message or an alternate calculation.

Common problems and how to check them

The formula returns the wrong label: Confirm that the reference points to the intended cell and that the value is numeric. A displayed “0” may be text, or a formula may be producing an empty string.
A tiny value appears to be zero: Increase the number of displayed decimal places or inspect the underlying value. Formatting can round the display without changing the value.
The formula is rejected: Check the commas and quotation marks, and account for the spreadsheet’s locale. Straight quotation marks are safest when typing formula text.
Zero disappears but calculations still include it: That is expected with a custom number format. Formatting hides a value visually; it does not remove or replace it.
A blank is reported as zero: Decide whether empty cells and formula-generated empty strings should count as missing, then use a separate blank check before testing for numeric zero.

When diagnosing a result, temporarily remove display formatting or inspect the source cell directly. This helps separate a calculation issue from a display issue. If the formula uses data copied from another system, checking a few representative cells—one blank, one zero, one ordinary number, and one value that looks like zero—can reveal inconsistent data types before they affect a whole report.

Choose between an IF formula and formatting

Use IF when the zero test needs to affect the value or message produced in a cell. Use a custom number format when the real value should remain intact but zero should not appear in the selected cells. Use conditional formatting when zero should remain visible but stand out with a color or other style: select the range, choose Format > Conditional formatting, and create a rule for cells equal to 0.

These options solve different problems. Conditional formatting changes appearance based on a rule, just as number formatting does, but it highlights rather than suppresses the value. An IF formula creates a result in a cell and can be used by later calculations. If a report needs both clean presentation and accurate underlying data, keep the raw values in one range and use formulas or formatting in a separate report area.

The basic IF formula is also valid in Excel, but the menu paths and some display settings differ between products. In Google Sheets in a web browser, custom number formats are reached through Format > Number > Custom number format. If you share a workbook with Excel users, confirm that the formatting and formula results look as intended in both applications.

Frequently Asked Questions

How do I make Google Sheets show blank if a cell is zero?

If the value is in A2, enter =IF(A2=0,"",A2) in another cell. It returns an empty string for zero and otherwise passes through A2’s value. If blanks in A2 must be treated differently from numeric zero, add a blank check rather than relying on this simple test.

How do I check whether a cell is exactly zero?

For a numeric value in A2, use =IF(A2=0,"Zero","Not zero"). To make sure the cell contains a number rather than text, use the ISNUMBER-and-equality test described above. This is helpful when data has been imported or copied from another source.

Can I hide all zeros in Google Sheets?

You can hide zeros in a selected range with a custom number format, but that changes only how those cells display. Select the range and use Format > Number > Custom number format; choose a format with an empty zero section. The zeros remain in the sheet and continue to affect calculations.

Why does a cell look like zero when it is not exactly zero?

The number format may be rounding a small value for display. Increase the displayed decimal places or check the actual value before making a zero-based decision. If your rule is intentionally based on rounded values, use ROUND in the formula and specify the number of decimal places.

Should I use IF or IFERROR for division by zero?

Use IF when you want to test the denominator specifically and decide what a zero denominator means. Use IFERROR when you want a fallback for any error from the calculation. Because IFERROR can hide errors other than division by zero, use it only when that broader behavior is appropriate.

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.