Sometimes, the Excel dataset may contain zeros. The zeros make the dataset too wordy, reducing the actual datasets' visibility. However, Excel allows users to hide the zero values and replace them with other values or with a blank space. This post will discuss two easy ways of hiding zero values and replacing them with a blank.
Using Custom Number Format to hide zero values
A custom number format is a tool to alter the document's number formats. In addition, this tool can also be used to find and replace values in the selected region. Custom Number Format tool plays magic in hiding zeros and replacing them with blank cells. Let us discuss steps to follow while using this method to hide zero values.
1. Open the Excel application. To open the Excel application, double-click on the Excel Icon. Alternatively, right-click on the Excel application and select the Open option.
2. Open the dataset with the zeros you wish to replace with blank spaces.
3. Highlight all the cells with the values in your to hide zero values.
4. Right-click on the selected region to open the side-view menu. From the menu, choose the Format Cells option.
5. From the Format Cells dialogue box, click on the Number tab. Locate the Category section, and click on the Custom button.
6. In the Type section, edit the existing values and type these commands 0;-0;;@ in the type box.
7. Next, click the OK button. All the zero values will be replaced with blank spaces.
Using keyboard shortcuts
You can also follow these steps while using Custom Number Format:
1. Open the dataset.
2. Highlight all the cells with the values in your to hide zero values.
3. Press CTRL + 1 keys on your keyboard.
4. From the Format Cells dialogue box, click on the Number tab. Locate the Category section, and click on the Custom button.
5. In the Type section, edit the existing values and type these commands 0;-0;;@ in the type box. Next, click the OK button.
Using Find and Replace Tools to hide zero values
Find and Replace is another tool that can be used to replace zero values with blank spaces. Here are the steps to follow while using this method:
1. Open the Excel application.
2. Open the dataset with the zeros you wish to replace with blank spaces.
3. Highlight all the cells with the values in your to hide zero values.
4. Click on the Home tab on the ribbon, and locate the Editing section.
5. In the Editing section, click the Find & Select drop-down button and select the Replace option.
6. Click on the Replace tab in the Find and Replace dialogue box.
7. Type the Zero digit (0) in the Find What section. Then, leave the Replace With section blank.
8. Finally, click on the Replace All button. Blank spaces will replace all the zeros in the selected region.