Search
Close this search box.

How to Hide Zero Values in Google Sheets (2 Easy Methods)

Do you need to hide zero values in Google Sheets? You’re in the right place. Sometimes, your data set looks cluttered with a bunch of zero values. That might be fine. But, you might prefer to hide zero values in Google Sheets.

That’s where I come in. In this tutorial, I’ll show you two methods you can use to hide zero values in Google Sheets. I will also cover a method you can use remove them completely. Read on for my step-by-step guide.

Google Sheets: Hide Zero Values

The two easiest way to hide zero values in Google Sheets are with conditional formatting and with custom formatting. I’ll show you how to do each of these. The first just changes the color of the zeros so you don’t see them. The second completely hides them from view. After that, we’ll talk about removing zero values entirely.

Hiding Zero Values with Conditional Formatting

You don’t need to delete every zero value to hide it in Google Sheets. Instead, use conditional formatting. Below, I’ll show you how to automatically check whether a cell has a zero value and then apply conditional formatting to those cells. That hides the 0s by changing the font color of the selected cells.

Suppose you have a dataset as shown below and you want to hide all the zero values in this dataset.

HIde Zero Values in this dataset

Below are the steps to use conditional formatting to change the font color and hide these zero values:

  1. Select the entire dataset (A1:E13 in this example)
  2. Click the Format option in the menu
  3. Click on Conditional Formatting. This will also open the Conditional Formatting pane in the right side of the worksheetClick on Conditional formatting option in Google Sheets
  4. In the ‘Conditional Formatting Rules’ pane, click on ‘Format cells if’ drop-down.
  5. Click on ‘Is equal to’ option (you may have to scroll a bit to see this option in the list)Click on is equal to option in Conditional formatting pane
  6. In the field below the ‘Is equal to’ selection, enter 0Enter 0 in the Is Equal to Value
  7. In the Formatting style options, change the font color and cell fill color to white.Change color to white to hide the zero value cells
  8. Click on Done

The above steps would hide all the zero values and the cells would appear blank.

Remember, the cells just look blank, but these are not blank. These cells still have the zero values (as we have not removed the zero values, only changed the color to make the cells look blank).

Hide Zero Values using Custom Formatting

The above conditional formatting method works great when you have the same color for both the cell color and the cell font color.

But as I mentioned in the section above, it doesn’t exactly hide the zero values, it only gives an impression that these are hidden. To truly hide zero values, use the custom formatting method.

Let’s use the dataset in the screenshot below. Here, we want to hide zero values in the spreadsheet. To do this, we’ll use a custom formatting instruction in Google Sheets.

HIde Zero Values in this dataset

Follow along to hide those pesky zero results in your worksheet.

  1. Select the entire dataset (A1:E13 in this example)
  2. Click the Format option in the menuClick on the Format option in the menu in Google Sheets
  3. In the options that appear, go to Number –> More Formats –> Custom number format. This will opens the Custom Number Formats dialogue box.Open the Custom Number formatting option in Google Sheets
  4. In the Custom number formats dialogue box, enter the following format: 0;-0;;@Custom Number format to hide zero value cells in Google Sheets
  5. Click on the Apply button.

As soon as you click Apply, the zero values are hidden. All other cells remain unchanged. Let’s talk about why that happens.

Custom Formatting Explained

In that dialogue box, you copied and pasted my 0;-0;;@ format. It might look like a confusing jumble, but it helps to break out the syntax to understand what it means. Note that this also helps you with formatting positive and negative numbers, if you’re interested.

The custom format syntax in Google Sheets is divided into four parts:

Positive Numbers; Negative Numbers; Zeros; Text

For each of these formats, you can specify how you want the cell to display the value. In this example, I have specified the following:

  • Positive Numbers: These should look as is and is specified by using the 0
  • Negative Numbers: These should be displayed with a negative sign at the beginning
  • Zeroes: These should be hidden as we have left the format empty (haven’t specified any format)
  • Text: Show the text as is and is specified by using the @ symbol

Look familiar? In the above syntax, you’ll notice that we have a 0 digit in the positive number location, another 0 in the negative number location, and we’ve left the zeroes location blank. That means that it’s entirely hidden in our worksheet.

Pro Tip: In case you want all the cells to be blank, you can use the following custom format: ;;; (three semi-colons without any format)

Important Note: When you hide a zero value in a cell using the methods shown in this tutorial, it only hides the value and not remove it. This means that while the cell may look as if it’s empty, the 0 value is still in the cell, and in case you use these cells for any calculation, these zero values will be used as well.

Looking for even more ways to change the look of your spreadsheet? Consider using the IF CONTAINS function as an alternative to hiding zeros in Google Sheets. Used with conditional formatting, that would also allow you to make a specific change for any cell where you find a zero.

Find and Remove Zero Values using Google Sheets

The above two methods will hide the zero values in Google Sheets, but the value would still be in the cells. It would just appear that the cells are blank. That’s helpful when you just want the data to look cleaner during review. But what if you want to remove zero values entirely?

There’s a quick solution to that too. In case you want to remove the zero values (so that the cells are empty), use the steps covered below.

First, suppose you have a dataset like the one in my screenshot below and want to remove all the zero values in this dataset.

HIde Zero Values in this dataset

Below are the steps that will find all the cells with the zero values and then remove these:

  1. Select the entire dataset (A1:E13 in this example)
  2. Click the Edit option in the menuClick on Edit Option in the Menu
  3. Click on Find and Replace option. This will open the Find and Replace dialog box (you can also use the keyboard shortcut Control + H)Click on Find and Replace option
  4. In the Find and Replace dialog box, enter 0 in the ‘Find’ field and leave the ‘Replace with’ field empty.Find and Replace with in Google Sheets
  5. Check the option, ‘Match entire cell contents’.Click on the Match Case option in Find and Replace
  6. Click on Replace All.
  7. Click on Done

The above steps would replace all the cells that have zero values with blanks. If you’re looking for a deeper dive, I cover it in much more detail in my article on the Find and Replace function in Google Sheets. That’s the best way to completely remove null values from your spreadsheet.

Related Reading: How to Install ChatGPT in Google Sheets.

Frequently Asked Questions

Sometimes, readers ask for my expertise with follow-up questions on my guides. That’s why I made this Q&A. Here are some of the most common questions I hear about hiding values in Google Sheets. If you have any other questions, please let me know in the comments!

How do I highlight all the zeros in my spreadsheet?

If you want to highlight duplicates in your spreadsheet, you’ll use conditional formatting. It’s the same process I outlined above, but you’ll choose to highlight instead of hide the zeros.

How do you hide zero values in Excel?

Microsoft Excel has a different option especially for hiding zero values. In the left pane of that program, you’ll click Options > Advanced > Display Options for this Worksheet. Then, uncheck the box that says “Show a zero in cells that have a zero value”. That hides the zero values in your Excel sheet.

Is it better to hide or remove zero values?

It’s usually better to hide zero values than to remove them entirely. Generally, you want to protect as much of your data as possible if you’re just making changes for viewability.

Can I show a zero as a dash in Google Sheets?

Yes, some people prefer to use dashes for zero values. You can do that with the custom format method I discussed above. And you can use the same method for all special characters. You could show an asterisk in place of a zero, or you could have it display the term “null” instead.

Can I hide zeros in a specific range or array?

Yes, you can use any of the methods above to hide zero values in a specific range. Just make sure that range is selected when you apply your formatting. If you have a whole column of zero values, just hide the whole column. In Excel, you can also hide zeros in a specific worksheet section.

Do I need to use a text function to hide zeros?

You don’t need to use a text function to hide zeros in Google Sheets. You can just modify the formatting to hide their appearance. Otherwise, as I mentioned above, you can use custom formatting to make zeros into dashes.

Conclusion

I hope this helped you hide zero values in your Google spreadsheet. As always, I’m here to help if you need me! Like my commentary on spreadsheets and formulas? Sign up for my newsletter below.

You may also like the following Google Sheets tutorials:

This article was checked by Jim Markus.

Most Popular Posts

Related Posts

Thanks for visiting! We’re happy to answer your spreadsheet questions. We specialize in formulas for Google Sheets, our own spreadsheet templates, and time-saving Excel tips.

Note that we’re supported by our audience. When you purchase through links on our site, we may earn commission at no extra cost to you.

Like what we do? Share this article!