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.
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
Step 4 – Run the Code
- Run the code.
- The number from the targeted cell will be removed.