Two Ways to Remove 0 Values in Google Sheets [How-To]

Do you know how to remove 0 (zero) values in Google Sheets? If not, this tutorial is for you.

If you’re familiar with Excel, you might already know how to do this in Excel spreadsheets. Excel offers a default menu option: navigate to File > Options > Advanced and uncheck the box next to “Show a zero in cells that have zero values.” This will hide all zero values in the spreadsheet.

However, if you want to remove zero values from the selected cells, the above option isn’t suitable. In that case, there are two alternative methods: using conditional formatting and custom number format. These two methods can also be used in Google Sheets.

Here are the two ways to remove 0 values in Google Sheets. Let’s start with the Custom Number Format.

Hide or Remove Zero Values Using Number Formatting

  1. Select the cells containing the zero values. You can select individual cells, a range, or entire rows and columns.
  2. Go to Format > Number > Custom number format in the menu.
  3. Enter the following custom number format in the provided field and click “Apply.” This will instantly hide the zeros in the selected range: #,##0.00_);"("#,##0.00")";""_)

If you want to remove zero values and add a currency symbol to other values, use this format: $#,##0.00_);"("#,##0.00")";""_). Replace the currency symbol with the one you want.

Number Format to Hide Zero Values

If you’d like to hide zeros and display a hyphen instead, you can use the following number format: #,##0.00_);"("#,##0.00")";"-"_). This will replace zero values with hyphens. You can also apply the currency prefix as shown earlier. Please note that the actual content in the cell will still be zero.

I understand it might be difficult to remember this format, so you may want to bookmark this page or take a photo of the code using your mobile device for quick reference.

Now, let’s move on to the second method.

Hide or Remove Zero Values in Google Sheets Using Conditional Formatting

This method is also effective but has some limitations. Here’s how to hide zero values using conditional formatting in Google Sheets:

  1. Select the column or data range where you want to apply the rule.
  2. Go to Format > Conditional formatting in the menu.
  3. Under “Format cells if,” select “Is equal to.”
  4. Enter 0 in the field below that.
  5. Ignore the “Apply to range” reference (e.g., F4:F10); ensure it matches your selected range.
  6. Set the font color to white and leave the fill color as none. Then, click “Done.” This will hide the zeros in the selected range.
Conditional Formatting to Hide Zero Values

The drawback of this method is that you won’t be able to change the font color or highlight the cells in the selected range afterward without revealing the zeros.

Another limitation is that applying this rule to a very large range may slow down the performance of your sheet.

Additional Tip

The above two methods apply to both manually entered zeros and zeros returned by formulas.

I recommend using the LET function if you want to remove zero values by adding a condition directly within the formula. This function allows you to name your formula and reference it within the formula expression to handle zeros.

Assume the formula is something like this:

=B2*C2

This formula would return zero if one of the values is zero. In that case, you can use LET as follows:

=LET(formula, B2*C2, IF(formula=0, ,formula))

It’s as simple as that, and it works similarly with array formulas. You just need to replace B2*C2 with your array or non-array formula.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Highlight Upcoming Birthdays in Google Sheets

When highlighting upcoming birthdays in Google Sheets, one important aspect is ignoring the year...

Compare Two Tables for Differences in Excel

To compare two tables with similar data for differences, you can use the XLOOKUP...

Calculate Weighted Average in Pivot Table in Google Sheets

You can calculate a weighted average in a Pivot Table using a custom formula...

Summarize Data and Keep the Last Record in Google Sheets

In Google Sheets, we can summarize data (like sum, average, min, max, count) using...

More like this

Highlight Upcoming Birthdays in Google Sheets

When highlighting upcoming birthdays in Google Sheets, one important aspect is ignoring the year...

Calculate Weighted Average in Pivot Table in Google Sheets

You can calculate a weighted average in a Pivot Table using a custom formula...

Summarize Data and Keep the Last Record in Google Sheets

In Google Sheets, we can summarize data (like sum, average, min, max, count) using...

5 COMMENTS

  1. Great tip! The choice of methods is head & shoulders better than other answers on the interwebs, and Bob L’s addition (if the cell is a formula) is the cherry on top. Bookmarking your site.

  2. I’m not a fan of using custom or conditional formatting to create blank cells. My preference is formulas.

    If the cell you’re dealing with is not a manually entered cell but has a formula you can do this:

    Original Formula: SUM(A1:A10)

    =IF(SUM(A1:A10)=0,"-",SUM(A1:A10))

    I prefer blank cells, so I just use "" rather than "-"

  3. Thanks for this. Very helpful. However, I think you’re missing the $ from the negative number format. Shouldn’t it be $#,##0.00_);”(“$#,##0.00″)”;””_)? You can see in your screenshot, you’re positive shows up as $1,234.56 while your negative number will show up as(1,234.56).

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.