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.

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.

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

• How to Exclude Zeros from MIN Function Results in Google Sheets
• How to Fill Zero in Blank Cells in Google Sheets
• How to Add Leading Zeros in Google Sheets
• Highlight Min Excluding Zero and Blank Cell in Google Sheets
• How to Remove Trailing Zeros from Numbers in Google Sheets
• How to Return Blank Instead of 30-12-1899 in Google Sheets Formulas
• Replace Blank Cells with 0 in Query Pivot in Google Sheets
• How to Fill Empty Cells with 0 in Pivot Table in Google Sheets