How to Hide Zero Values in Google Sheets [3 Methods]

How to Hide Zero Values in Google Sheets 7

In this article, we will learn multiple ways to hide zero values in Google Sheets. Also, we are going to include a method that you can use to delete zero values forever from your dataset. Keep on reading to discover these methods in Google Sheets and get ready to apply these techniques in your spreadsheet as well.

Hiding zero values by formatting them as blanks is a commonly used technique in Google Sheets. It will help you make your Google Spreadsheet easy to read and interpret.

While working with Google Sheets we may have a dataset that contains zero values in it. Sometimes we want to delete those values or sometimes just want to hide zero values without removing them permanently from our dataset.

If you have worked with Excel, you may know there is a default option to hide zero values in Excel Spreadsheets. But this function is not present here to hide zero values in Google Sheets directly. So, we have different procedures to hide them in Google Sheets.

Importance of Hiding Zero Values in Google Sheets

Google Sheets users often need to use the technique of hiding zero values present in the dataset. Some advantages of hiding zero values in Google Sheets indicate its significance and importance.

Just imagine that you have extensive and complex data so it would be difficult for you to quickly determine the cells that contain zero values in them. To make them prominent in your data set you can add a visual cue by formatting zero values as blanks. In this way, you can quickly identify the empty cells or blanks which are expressing zero values in your dataset. Also, it will not affect formulation in your spreadsheet if we just hide zero values instead of deleting them. Therefore, it would be better to hide them for better visual interpretation.

Sometimes zero values in your dataset indicate that there is no data available. So, hiding zero values and showing blank or empty cells would be more suitable. Because if you are anything like me, you want a neat and clean sheet and want to hide something that may look wrong. So, if you have rows of data, but don’t have data in some rows yet and want them to be ready for when they do contain data. Therefore, instead of showing zero for every field with no data, you can replace zero values with empty cells.

See also  How to Use Absolute and Relative Cell References in Google Sheets

How to Hide Zero Values in Google Sheets

There are a couple of logical ways to hide zero values in google sheets. One by conditional formatting and the second by customs formatting. But by these two methods, we can only hide the zero values; it does not remove the value. So, we will also study the method of removing zero values in Google sheets

This tutorial, let you find the step-by-step procedures of each method of hiding zero values in Google Sheets. So, let’s get started!

Hide zero values in Google Sheets – Using Conditional Formatting

Conditional formatting is the method by which you may format the cells as you want. Just as you need to understand the logic according to your requirement as you need to hide zero values here, you may apply the condition that all the cells that contain zero values may be filled by white color. In such a manner, you may hide all the zero values from your data. Let’s see how it works.

Step 1

Let’s have some sample data that have several zero values.

How to Hide Zero Values in Google Sheets 1

Step 2

Select the area where you want to insert formatting to hide zero values.

How to Hide Zero Values in Google Sheets 2

Step 3

Now look at the menu bar and open the “Format” option.

How to Hide Zero Values in Google Sheets 3

Step 4

As click on the format tab, a drop-down menu will open where you will find the “Conditional formatting” option. Click to open it.
How to Hide Zero Values in Google Sheets 4

Step 5

Here you will need to change “Format cell if…” to “Is equal to”. you may have to scroll a bit to see this option in the list.

How to Hide Zero Values in Google Sheets 5

Step 6

After selecting the “format cell if..” an additional value box will appear where you will put the “0” because we are looking for zero values.

How to Hide Zero Values in Google Sheets 6

Step 7

When you give the condition, all zero values will be selected automatically. Now we will apply formatting to hide them. First, change the text color to “white” from here.

How to Hide Zero Values in Google Sheets 7

Step 8

Now we will change the cell fill color also to “white”.

How to Hide Zero Values in Google Sheets 8

Step 9

You have completed the formatting now just press the button “Done“.

How to Hide Zero Values in Google Sheets 9

Step 10

Now you may close the conditional formatting pane.

How to Hide Zero Values in Google Sheets 10

Step 11

As you may see the result all zero values have been hidden.

How to Hide Zero Values in Google Sheets 11

Note: By applying the above method zero values are only hidden they are not removed they are still present in their cell.

See also  How to Change Cell Size in Google Sheets [3 Methods]

Hide zero values in Google Sheets – Using Custom Formatting

The above method works great but it does not hide the zero values it just gives the impression that they are hidden. To completely hide the zero values, we will use custom formatting. In Google sheets, custom formatting is based on a text pattern by default present. The pattern is Positive Numbers, Negative numbers, zero values, and text. According to this pattern, we will use the following format to hide the zero values. For each of these formats, you can specify how you want the cell to display the value as I define here,

0;-0;;@

Positive Number: They will remain the same as positive numbers by using 0.

Negative numbers: They will remain the same as negative numbers by using -0

Zero values: We will remain space empty instead of zero values so that they may hide.

Text: We will show the text as is and is specified by using the @ symbol.

Now let’s see how it can be practical.

Step 1

As usual, First, we will have some sample data.

How to Hide Zero Values in Google Sheets 12

Step 2

Now select all the data where you want to hide zero values.

How to Hide Zero Values in Google Sheets 13

Step 3

After selection, find the “Format” tab in the menu bar and open it.

How to Hide Zero Values in Google Sheets 14

Step 4

As open the format tab a drop-down menu will drag down where you will find “Number“.

How to Hide Zero Values in Google Sheets 15

Step 5

Go to the “Number“, a second menu will appear where you will find “Custom Formatting” with some scrolling down.

How to Hide Zero Values in Google Sheets 16

Step 6

When you click the “custom formatting” a new window will appear in front of you like,

How to Hide Zero Values in Google Sheets 17

Step 7

Here we will put the format (0;-0;;@) in the custom format dialogue box which we created above to hide zero values.

How to Hide Zero Values in Google Sheets 18

You may also verify your format by seeing the format of all values just next to the format dialogue box as shown in the above image, there is a space for zero values which means they will be hidden.

Step 8

Now you are just one click behind to complete your process. Click on the “Apply” button.

How to Hide Zero Values in Google Sheets 19

Step 9

As you may see below in the picture all zero values have been hidden.

How to Hide Zero Values in Google Sheets 20

This method also just hides the zero value. It does not remove them

How to hide zero values in Google sheets – Using Find & Replace

As we study two methods yet but both of them just hide the zero values in google sheets. So, If you want to remove the zero values then you may take help with Find & Replace feature of the google sheet. It will first find all the zero values and then remove them.  Let me show you how we may remove the zero values by find & replace with the help of some examples.

Step 1

First, we will select the data where we want to remove the zero values.

How to Hide Zero Values in Google Sheets 21

Step 2

Now we will open the “Edit” tab from the menu bar, where you will find the “Find & Replace” at last.

How to Hide Zero Values in Google Sheets 22

You may also use a shortcut key “Ctrl + H” to open the find and replace dialogue box.

Step 3

When clicking on Find & Replace option a dialogue box opens to find and replace the values. So here we are looking for zero values so we will put the “zero” in the find box.

How to Hide Zero Values in Google Sheets 23

Step 4

Then to remove them, the Replace box remains empty and checks on “Match entire cell contents”.

How to Hide Zero Values in Google Sheets 24

Step 5

Now just do a couple of clicks first click on the “Replace all” and then “Done” button.

How to Hide Zero Values in Google Sheets 25

Step 6

You are done now. As you may see all the zero values have been removed.

How to Hide Zero Values in Google Sheets 26

See also  How to Use REGEXMATCH in Google Sheets [Complete Guide]

Important Notes on Hiding Zero Values in Google Sheets

  • First thing is to keep in mind that here we are working on these methods to hide zero values. So be remembered that you must have zero values in your sample data otherwise you will find nothing.
  • As we learned two methods to hide zero values one is conditional formatting and the second is customs formatting, in both of these methods zero values are just hidden, they are not removed, they are still present in the cells, even if it can affect your formulas and conditions as well if applied.
  • Conditional formatting and custom formatting both can be removed as well if needed.
  • Remove zero values by Find and replace completely remove the values and can give a change in your formula results if you have applied.

See also  How to Change Text Case in Google Sheets [Complete Guide]

Conclusion

Your priority is to make your documents, presentations, tables, and reports errorless, accurate, and attractive. Zero values somewhere are useful but they may also affect our applied conditions and can give an impression of unattractive sheets. Therefore, sometimes there is a need to hide zero values in google sheets. As we have studied several easy methods to hide zero values in google sheets by which you will not get any problems while applying.

I have told you how to remove zero values as well. Hope these lessons are found helpful to you. For more informational guidance don’t forget to Subscribe to Office Demy.

Content Protection by DMCA.com

M. Shaiq Ansari

Hi, I am Shaiq. A young and self-motivated content writer having years of experience expertise in MS Office suite, Google docs Editor Suite. I have a technical education background that empowers me to stand out in today's digital world. I am currently a freelance content creator and a part of a local digital marketing agency in Karachi Pakistan.

OfficeDemy.com
Logo
Enable registration in settings - general