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.
How to Fill Blank Cells with 0 in Excel: 3 Effective Ways
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.
- First, select the cells in the range.
- Select the Find & Select option in the Editing Ribbon.
- Then, click on the Go to Special
- Click on the Blanks.
- Then, press Enter.
- As a result, all the blank cells will be selected.
- Type 0 (zero) in a blank cell.
- 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.
- How to Fill Blank Cells in Excel with Go To Special
- How to Fill Blank Cells with Formula in Excel
- How to Fill Blank Cells with N/A in Excel
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 instructions below.
- Firstly, select the cells.
- Then, click on the Find & Select.
- Choose the Replace.
- Keep the Find what box blank.
- Next, type 0 in the Replace with box.
- Then click on the Replace All.
- 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: How to Find and Replace Blank Cells in Excel
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.
- First of all, select the cells.
- Press Alt + F11, to open the VBA Macro.
- Then click on the Insert.
- Next, choose the Module.
- Now, 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.
- In the input box, type 0 (zero).
- Finally, press Enter.
- As a result, you will get all the blank cells filled with 0.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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. Please do not hesitate to contact us if you have any queries. Please share your thoughts in the comments box below.
- How to Fill Blank Cells with Value Above in Excel VBA
- How to Fill Blank Cells with Value Below in Excel
- How to Fill Blank Cells with Value Above in Excel
- How to Fill Blank Cells with Color in Excel
- How to Fill Blank Cells with Value from Left in Excel
- How to Fill Empty Cells with Default Value in Excel
- How to Fill Empty Cells with Last Value in Excel
- Fill Blank Cells with Text in Excel
- Fill Blank Cells with Dash in Excel