Bhubon Costa
Assume that you have a big spreadsheet with thousands of empty cells, and you want to replace the empty cells with a certain value. For example, we will fill our blank cells with zero (0). So, in this article, we will explain to you how to fill blank cells with 0 in Excel.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
3 Effective Ways to Fill Blank Cells with 0 in Excel
In the section that follows, we will demonstrate three practical methods for filling blank cells with zeros (0). First, we’ll use the Go to Special and Replace commands from the Find & Select option in the Editing menu from the Ribbon. Later on, we will use the VBA code to do the same action. A sample data set is provided to apply the methods.
1. Apply the Go to Special Command to Fill Blank Cells with 0 in Excel
To apply the Go to Special command from the Find & Select option, follow the steps below.
Step 1:
- Firstly, select the cells in the range.
Step 2:
- Select the Find & Select option in the Editing Ribbon.
- Then, click on the Go to Special
Step 3:
- Click on the Blanks.
- Then, press Enter.
- As a result, all the blank cells will be selected.
Step 4:
- Type 0 (zero) in a blank cell.
Step 5:
- Finally, press Ctrl + Enter to apply it to all the cells.
Notes. You can use a keyboard shortcut Ctrl + G to find the Go to Special command.
Read More: If Cell is Blank Then Show 0 in Excel (4 Ways)
2. Use the Replace Command to Fill Blank Cells with 0 in Excel
You can do the same task by applying the Replace command from the same Find & Select option. To do so, follow the instruction below.
Step 1:
- Firstly, select the cells.
Step 2:
- Then, click on the Find & Select.
- Choose the Replace.
Step 3:
- Keep the Find what box blank.
- Type 0 in the Replace with box.
- Click on the Replace All.
Step 4:
- Finally, Click OK.
- Therefore, you will see that each blank cell is filled up with 0.
Notes. Keyboard shortcut to Replace command: Ctrl + H .
Read More: Formula to Return Blank Cell instead of Zero in Excel (With 5 Alternatives)
Similar Readings
- How to Remove Blanks from List Using Formula in Excel (4 Methods)
- Ignore Blank Cells in Range in Excel (8 Ways)
- How to Set Cell to Blank in Formula in Excel (6 Ways)
- Calculate in Excel If Cells are Not Blank: 7 Exemplary Formulas
- Highlight Blank Cells in Excel (4 Fruitful Ways)
3. Run a VBA Code to Fill Blank Cells with 0 in Excel
Additionally, you can fill blank cells with anything you want by applying the VBA code. Follow the outlined steps below to accomplish the task.
Step 1:
- First of all, select the cells.
Step 2:
- Press Alt + F11, to open the VBA Macro.
- Click on the Insert.
- Choose the Module.
Step 3:
- Paste the following VBA.
Sub Fill_Blank_Cells_with_0_in_Excel()
'Declare the variables
Dim Selected_area As Range
Dim Enter_Zero As String
On Error Resume Next
'Apply an input box to enter the value zero (0)
Enter_Zero = InputBox("Type a value(O) that will fill blank cells", _
"Fill Empty Cells")
'Apply a For loop
For Each Selected_area In Selection
'Use an If statement to meet the condition
If IsEmpty(Selected_area) Then
Selected_area.Value = Enter_Zero
End If
Next
End Sub
- Save the program and press F5 to run it.
Step 4:
- In the input box, type 0 (zero).
- Finally, press Enter.
- As a result, you will get all the blank cells filled with 0.
Related Content: VBA to Count Blank Cells in Range in Excel (3 Methods)
Conclusion
To summarize, I hope you now understand how to replace empty cells in Excel with 0. To teach and practice with your data, you should employ all of these strategies. Examine the practice book and apply what you’ve learned. We are encouraged to continue delivering seminars like this because of your crucial support.
Please do not hesitate to contact us if you have any queries. Please share your thoughts in the comments box below.
The Exceldemy team will get back to you as soon as possible.
Stay with us and continue to learn.
