While working with Excel or Google Sheets, zeros may appear in empty dataset cells. This may make your sheet contains many unwanted values. In this article, we shall discuss some of the workarounds that can replace zeros with a blank in Excel and Google Sheets.
Replacing 0 with blank in Google Sheets
Table of Contents
We shall discuss three methods of replacing 0 with blank in Google sheets:
Find and replace tool
Using the Custom formatting tool
Using the Conditional formatting tool
Find and replace tool
Follow these steps while using this method:
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.
Using the Conditional formatting tool
Below are the steps to follow:
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.
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.
Replacing 0 with blank in Excel
We shall discuss two methods that can be used in Excel to replace 0 with blank in Excel:
Disabling zeros in the worksheet
Using the Custom formatting tool
Disabling zeros feature
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.
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.