How to replace 0 with a blank in Microsoft Excel 

Replacing 0 with a blank in Excel means replacing cells containing 0 with empty cells to visually hide zero values. This is useful for better data presentation and excluding zero values from calculations when working with formulas. 

In this tutorial, we will learn how to replace 0 with a blank in Microsoft Excel. In Excel we can replace 0 with a blank in many ways i.e. we can utilize the “Find and Replace” command or it can also be done by applying a VBA code. 

We possess a dataset sample exhibiting the quantities at which various products are sold. Certain products have zero quantities sold, and our objective is to substitute these 0 entries with blanks. 

Method 1: Utilizing the Find and Replace Function 

Step 1 – Press the CTRL+H Keys 

• Press the CTRL+H keys on the keyboard to open the Find and Replace dialog box. 

Step 2 – Enter “0” in the “Find what” Field 

• Enter 0 in the “Find what” field. 

Step 3 – Leave the “Replace with” Field Blank 

• Enter nothing in the “Replace with” field. 

Step 4 – Perform a Click on the “Replace All” Button 

• Perform a click on the “Replace All” button. 

Method 2: Utilizing a VBA Code 

Excel provides a very powerful method i.e., VBA code to automate and perform tasks through coding. If you are that type of person who likes to write down some lines of code and get stuff done then follow along the steps mentioned below to learn how to use VBA code to replace a 0 with a blank. 

Step 1 – Press the ALT+F11 Keys 

• Press the ALT+F11 keys on the keyboard. 

Step 2 – Insert a New Module 

• Insert a new module. 
• For this, right-click on the tab name located in the menu on the left. 
• Choose the Insert option and perform a click on the Module button. 

Step 3 – Enter the VBA Code 

• Enter the following VBA code, remember to modify the range of cells from which you want to remove 0. 

Sub RemoveZeros() 

    Dim rng As Range 

    Dim cell As Range 

    ‘ Set the range where you want to remove 0 entries 

    Set rng = Range(“B2:B21”) ‘ Update with your desired range 

    ‘ Loop through each cell in the range 

    For Each cell In rng 

        If cell.Value = 0 Then 

            cell.ClearContents ‘ Remove the 0 entry by clearing the cell 

        End If 

    Next cell 

End Sub 

Step 4 – Run the Code 

• Run the code. 
• The number from the targeted cell will be removed.