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

Sometimes, you will have a dataset that has zero values in it. While some times these are actual values that you want to keep as is, in some cases you may want to hide these zero values in Google Sheets.

In this tutorial, I will show you a few methods that you can use to hide zero values in Google Sheets. I will also cover a method you can use to not just hide the zero values but instead remove these completely.

Hide Zero Values using Conditional Formatting

Conditional formatting is the way you can format a cell based on the value in it.

And in our example, we will check all the cells that have zero value in it and then use conditional formatting to hide these 0s (by changing the font color of these 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 cell color and the cell font color.

But as I mentioned in the section above, it doesn’t hide the zero values, it only gives an impression that these are hidden.

In case you truly want to hide these values, use the method covered in this section.

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 hide the zero values using custom formatting in Google Sheets:

  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 open the ‘Custom number formats’ dialog box.Open the Custom Number formatting option in Google Sheets
  4. In the Custom number formats dialog box, enter the following format: 0;-0;;@Custom Number format to hide zero value cells in Google Sheets
  5. Click on the Apply button.

The above steps would hide all the zero values, while all the other cells remain unaffected.

Explanation of the custom format:

In the steps above, we have used the following format:

0;-0;;@

The custom format 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
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.

Find and Remove Zero Values in 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.

In case you want to remove the zero values (so that the cells are actually empty), use the steps covered in this section.

Suppose you have a dataset as shown below and you 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.

You may also like the following Google Sheets tutorials:

Most Popular Posts

Sumit

Sumit

Sumit is a Google Sheets and Microsoft Excel Expert. He provides spreadsheet training to corporates and has been awarded the prestigious Excel MVP award by Microsoft for his contributions in sharing his Excel knowledge and helping people.
Related Posts

Disclosure: Spreadsheet Point is supported by its audience. When you purchase through links on our site, we may earn an affiliate commission.