Sometimes, the Excel or Google Sheets dataset may contain zeros. The zeros make the dataset too wordy, reducing the actual datasets’ visibility. This article will discuss ways of hiding zeros in Excel and Google Sheets.
Hiding Zero in Google Sheets
Table of Contents
We shall discuss three methods of hiding or removing zeros in Google sheets:
Using Find and replace tool
Using the Custom formatting tool
Using the Conditional formatting tool
1. Using Find and replace tool
Below are the steps to follow when using Find and replace the tool to remove zeros in Google sheets:
1. Visit the Google account and log in using your email detail (That is, https://www.google.com/account). From the Google Apps, click on the Sheets icon and then select the existing sheet that you want to remove zeros.
2. Highlight the dataset, and then click on the Edit tab on the menu.
3. Click the Find and replace option to open the Find and Replace dialogue box.
4. In the box, enter ‘0’ in the Find section and leave the Replace section blank.
5. Next, check the Match entire cell contents checkbox. Finally, hit the Done button.
2. Using the Custom formatting tool
Below are the steps to follow when using the Custom formatting tool to remove zeros in Google sheets:
1. Open an existing document where you wish to remove the zeros from your dataset.
2. Highlight the dataset, then click on the Format tab on the menu.
3. Locate the Numbers button and hover the cursor over it.
4. From the drop-down, choose the Custom Number Format.
5. In the custom number dialogue box, type this format 0;-0;;@ and then hit the Apply button.
3. Using the Conditional formatting tool
Below are the steps to follow when using this method to hide zeros:
1. Open an existing document where you wish to remove the zeros from your dataset.
2. Highlight the dataset, then click on the Format tab on the menu.
3. From the menu, choose the Conditional formatting option. This will open the formatting pane on the right side of the screen.
4. In the Format rules section, choose the ‘Is equal to’ option.
5. In the field below the rule section, type 0.
6. Then, change the font color to white in the Formatting style section. Also, change the fill color to white.
7. Finally, Hit the Done button.
Hiding Zero in Excel
We shall discuss two methods that can be used in Excel to remove or hide zeros in Excel:
Disabling zeros in the worksheet
Using the Custom formatting tool
1. Disabling zeros in the worksheet
Steps to follow:
1. Open the Excel application and click on the File tab.
2. Click on the options button found on the File screen.
3. In the options dialogue box, click on the Advanced button.
4. In the Display options section, uncheck the Show a zero in cells with a zero value checkbox.
2. Using the Custom formatting tool
Steps to follow:
1. Open the Excel application.
2. Highlight the data, then right-click to open the side-view menu.
3. From the dialogue box, choose the custom option.
4. In the type section, enter his format 0;-0;;@ and then hit the OK button.