M. Shaiq Ansari
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.
Table of Contents
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.
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.
Step 2
Select the area where you want to insert formatting to hide zero values.
Step 3
Now look at the menu bar and open the “Format” option.
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.
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.
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.
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.
Step 8
Now we will change the cell fill color also to “white”.
Step 9
You have completed the formatting now just press the button “Done“.
Step 10
Now you may close the conditional formatting pane.
Step 11
As you may see the result all zero values have been hidden.
Note: By applying the above method zero values are only hidden they are not removed they are still present in their cell.
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.
Step 2
Now select all the data where you want to hide zero values.
Step 3
After selection, find the “Format” tab in the menu bar and open it.
Step 4
As open the format tab a drop-down menu will drag down where you will find “Number“.
Step 5
Go to the “Number“, a second menu will appear where you will find “Custom Formatting” with some scrolling down.
Step 6
When you click the “custom formatting” a new window will appear in front of you like,
Step 7
Here we will put the format (0;-0;;@) in the custom format dialogue box which we created above to hide zero values.
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.
Step 9
As you may see below in the picture all zero values have been hidden.
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.
Step 2
Now we will open the “Edit” tab from the menu bar, where you will find the “Find & Replace” at last.
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.
Step 4
Then to remove them, the Replace box remains empty and checks on “Match entire cell contents”.
Step 5
Now just do a couple of clicks first click on the “Replace all” and then “Done” button.
Step 6
You are done now. As you may see all the zero values have been removed.
Important Notes
- 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.
Frequently Asked Questions
Can I Use the Same Methods to Hide Zero Values in Google Sheets when Finding Absolute Value?
To hide zero values in Google Sheets when calculating absolute value, you can utilize the same methods. By applying conditional formatting rules, you can create a custom formula that targets cells containing zero values and hides them. This ensures that your google sheets absolute value calculations appear cleaner and more organized.
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.
