While working in Google Sheets, it is quite common that we encounter Zeros (0). However, in some instances, users might want to get rid of 0 values while others want to preserve 0 values in the dataset. Thus if you are a user who wants to get rid of 0 values in a dataset, then this page is for you. This article will explain everything about how to remove zero values from a Google Spreadsheet with the help of Google Sheet tips provided on this page. Read on to find more.
|
Table of Content |
Find and Remove Zero Values in Google Sheets
Let’s say, you may have the data having the values including zero values, to find and remove only zero values in google sheets.
The following are the steps for locating and removing all cells with zero values,
- Step-1: Select the whole collection of data
- Step-2: Select Edit from the menu options.
- Step-3: Select the Find and Replace option from the drop-down menu. You may also use the keyboard shortcut Control + H to activate the Find and Replace dialogue box.
- Step-4: Fill in 0 in the ‘Find’ field and leave the ‘Replace with’ field blank in the Find and Replace dialogue box.
Make sure that the option ‘Match whole cell contents’ is selected.
- Step-5: Select Replace All.
- Step-6: Click the Done button.
Now the results will be published as below image.
- How to Compare Two Columns in Google Sheets (for matches & differences)
- How to Color Alternate Rows in Google Sheets: Alternating Colors Every 2 Rows, 3 Rows
- How To Insert Indents in Google Sheets? – Know How to Tab Down in Google Sheets
Hide Zero Values Using Conditional Formatting
There are multiple methods with the help of which we can remove zero values. The steps to remove 0 values in Google Spreadsheet using conditional formatting are outlined below:
- Step-1: Select the entire cell having the numbers
- Step-2: Select the Format option from the menu bar
- Step-3: Select the Conditional Formatting option
- Step-4: In the format rules, set the rule by selecting is Equal To and type 0
- Step-5: In the formatting style, make sure you enable the text color to white and also fill with no color, this will effectively hide all your zero values.
Now that, we have set the conditional format rules, the result will be shown as below,
Remember that the cells appear to be blank, but they are not. The zero values in these cells have not been deleted; we have just altered the colour to make the cells seem blank.
Hide Zero Values Using Custom Formatting
When both the cell colour and the cell font colour are the same, the conditional formatting method mentioned above works perfectly.
However, it simply creates the idea that zero values are hidden. Follow the procedure described in this section if you actually wish to hide these values.
- Step-1: Select the entire cell having the numbers
- Step-2: Select the format option from the menu bar
- Step-3: Go to Number –> More Formats –> Custom number format in the option that shows
- Step-4: In the custom number format dialog box, type “0;-0;;@”
- Step-5: Click on Apply, to view the results
Now the results will be published as below image.
In the above example, we used the following format:
0;-0;;@
The above format is broken into four parts in google sheets:
Positive Numbers; Negative Numbers; Zeros; Text
- Positive Numbers: These should appear as they are and are specified by using the 0 characters
- Negative Numbers: Any number beginning with a negative sign
- Zeros: These should be hidden since the format has been left blank (no format has been given)
- Text: Display the text exactly as it is, as provided by the @ symbol
You can use the following custom format to make all of the cells blank:
;;; (three semi-colons without any format)