How to Fill Blank Cells with 0 in Excel (3 Methods)

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.

Sample Data

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.

Ways to Fill Blank Cells with 0 in Excel

Step 2:

  • Select the Find & Select option in the Editing Ribbon.
  • Then, click on the Go to Special

Ways to Fill Blank Cells with 0 in Excel

Step 3:

  • Click on the Blanks.
  • Then, press Enter.

Ways to Fill Blank Cells with 0 in Excel

  • As a result, all the blank cells will be selected.

Ways to Fill Blank Cells with 0 in Excel

Step 4:

  • Type 0 (zero) in a blank cell.

Ways to Fill Blank Cells with 0 in Excel

Step 5:

  • Finally, press  Ctrl + Enter to apply it to all the cells.

Ways to Fill Blank Cells with 0 in Excel

 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.

Ways to Fill Blank Cells with 0 in Excel

Step 2:

  • Then, click on the Find & Select.
  • Choose the Replace.

Ways to Fill Blank Cells with 0 in Excel

Step 3:

  • Keep the Find what box blank.
  • Type 0 in the Replace with box.
  • Click on the Replace All.

Ways to Fill Blank Cells with 0 in Excel

Step 4:

  • Finally, Click OK.

Ways to Fill Blank Cells with 0 in Excel

  • Therefore, you will see that each blank cell is filled up with 0.

Ways to Fill Blank Cells with 0 in Excel

 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


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.

Ways to Fill Blank Cells with 0 in Excel

Step 2:

  • Press  Alt F11, to open the VBA Macro.
  • Click on the Insert.
  • Choose the Module.

Sample Data

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

Sample Data

  • Save the program and press  F5  to run it.

Step 4:

  • In the input box, type 0 (zero).

Sample Data

  • Finally, press Enter.
  • As a result, you will get all the blank cells filled with 0.

Sample Data

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.


Related Articles

Bhubon Costa

Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo