Sometimes you will have a data set that has blank cells in it that you need to fill with a 0.
I have often found this to be a case with some databases where when you export the data, the cells that contain zero are often left blank. This could also be the case where data is manually entered, and 0’s are not entered, leaving the cell blank.
While you always have an option to manually select and enter 0 in a blank cell, doing so for a large data set would be quite tedious and inefficient.
In this tutorial, I’m going to show you three simple methods you can use to fill blank cells with 0 (zero) in Excel.
Fill Empty Cells with 0 in Excel
In this section, I will show you three ways to quickly fill all the blank cells with zero in Excel.
Two of these methods (‘Find & Replace’ and ‘Go To Special’) use the in-built Excel functionalities, and the third method uses a simple VBA code.
Using the Find and Replace to Find Blank Cells and Replace it with 0
Let me first show you the best way to fill blank cells with 0 in Excel – using the Find and Replace functionality.
With Find and Replace, you can quickly find all the cells that are blank in your data set, and replace the content of these cells with a 0.
Below I have a data set where I have the sales values for different stores in four quarters. as you would see, there are some cells that are empty, and I want to fill these blank cells with 0.
Here are the steps you can use to fill blank cells with 0 using Find and Replace:
- Select the data set where you have the blank cells that you need to fill with 0
- Click the Home tab
- In the Editing section, click on ‘Find & Select’
- Click on ‘Replace’. This will open the Find and Replace dialog box
- Leave the ‘Find what’ field empty (As we need to find all the cells that are empty/blank)
- In the ‘Replace with’ field, enter 0
- Click on Replace All
As soon as you click on the replace all button in step 7, it will fill all the blank cells with a 0.
It will also show you a prompt to tell you how many cells have been filled with zero.
One important thing to note when using find and replace to fill all the blank cells with zero is to make sure that the blank cells are in fact empty. They should not have any space character or an apostrophe that may make them look empty while they’re not.
Pro Tip: You can use the keyboard shortcut Control + H to open the Find & Replace dialog box. To do this, select the data set, then hold the Control key, and then press the H key
Also read: How to Replace Zero with Dash in Excel?
Using the Go to Special Technique to Select Blank Cells and Enter 0
Another easy way to quickly fill all the blank cells with 0 is by using the go to special dialog box method.
Using the Go To Special functionality, you can quickly select all the cells that are blank. Once these blank cells are selected, you can manually fill these with 0 (with a keyboard shortcut that would fill zero in all the blank cells in one go).
Below again I have the same data set where I have the sales values for four quarters, and there are some blank cells that I need to fill with zeros.
Here are the steps to fill blank cells with 0 using Go To special method:
- Select the entire data set where you have the blank cells that you want to fill with 0
- Press the F5 key to open the Go To dialog box
- Click on the ‘Special’ button, which will open the Go To Special dialog box
- In the Go To Special dialog box that opens, select the ‘Blanks’ option button
- Click OK. You will now see that all the blank cells in the data set have been selected
- Enter 0 using your keyboard. you will notice that the zero is entered in one of the blank cells (which would be the active cell in the selection)
- Hold the Control key and press the Enter key
Doing the above steps would enter 0 in all the blank cells in your selected data set.
In this method, the Go To Special dialog box enables us to select all the blank cells while deselecting all the other cells that are not blank.
When you enter a 0 when all the blank cells are selected, this value is entered only in the active cell, but when you use the keyboard shortcut Control + Enter, it enters the value in the active cell (which is 0), in all the other selected blank cells.
While this may look like a long method as compared to the Find and Replace technique we used earlier, one benefit that this method offers is that it allows you to select all the blank cells in one go.
In our example, once we had all the blank cells selected, we entered 0 in it.
But you can also do other things such as
- Enter any text value such as “Zero” or “NA” or “Not available”
- Highlight these blank cells by giving these a background color
- Delete rows that have the blank cells
Also read: Replace Zero with Dash in Excel
VBA Code to Fill Blank Cells with 0 (zero)
You can also use a simple VBA code to quickly fill all the blank cells with zeros.
Here is the VBA code that would go through each cell in the specified data set, and then check whether the cell is blank or not. and in case the cell is blank, it would enter a zero in that cell.
'Code developed by Steve Scott from https://spreadsheetplanet.com/
Sub Fill_Blank_Cells_with_Dash()
Dim myrng As Range
On Error Resume Next
Set myrng = Application.InputBox("Select the Range to fill blank cells with dash", "Fill Blanks with Dash", , , , , , 8)
For Each Cell In myrng
If Cell.Value = "" Then
Cell.Value = 0
End If
Next
End Sub
In the above VBA code, I have used the Application.InputBox method that would allow you to choose a range of cells that should be considered while running the code.
So when you execute this code, you will see a simple input box as shown below, that allows you to select the data set where you have the blank cells.
Once you select the range and click OK, the VBA code would be executed. it would go through each cell in the selection, and if it finds any blank cell, it would fill it with a 0.
There are multiple ways you can run this VBA code in Excel:
- You can add this code to a module in the VBA editor and then run this code by hitting the F5 key or clicking on the run button in the menu in the VBA editor
- You can assign a keyboard shortcut to this macro, and run this using that keyboard shortcut
- You can save this macro in the Quick Access Toolbar so that you can run it with a single click
Note: If you’re adding this VBA code in a module in your excel file, make sure to save that excel file as a macro-enabled file (with a .XLSM extension). You can do this by clicking on the File tab, and then clicking on Save as. In the save as dialog box, you will have the option to save the file as a macro-enabled file (in the Save as type drop-down).
If filling blank cells with zeros is something you need to do quite often, then I would recommend you add this VBA code to your Personal Macro Workbook.
Once you do that, this code will be available in all the Excel files on your system. and if you assign a keyboard shortcut to it or add this to your Quick Access Toolbar, you will have easy access to run this macro in any Excel file (with a keyboard shortcut or with a click of a button in the quick access toolbar icon).
If you’re not sure what a personal macro workbook is and how it works, here is a great video I found that explains this in detail:
Why Fill Blank Cells with Zeros?
If you’re wondering why it’s a good idea to fill all the empty cells in your dataset with 0’s, here are some scenarios where it would be needed:
- Many Excel formulas (such as SUM or COUNT or AVERAGE) ignore blank cells. So if you’re using these formulas, and your input range has blank cells, these cells would be ignored. This also means that formulas such as COUNT or AVERAGE would not give you the right result. In such cases, filling these blank cells with 0 would solve this issue
- If you’re creating charts with data that has blank cells, some charts (such as line charts) show gaps in places where there are blank cells. and if you fill these blank cells with 0, the same line chart is going to show you a continuous line with no breaks.
- This is my personal use case where I want to fill all the blank cells with 0 (auto dash or any other character) just to make sure that I do not have any blank cells in the column. this helps me to quickly jump to the last filled cell in the column by using the keyboard shortcut CTRL and down arrow key (which wouldn’t work if I have blank cells as the cursor would always go to the last filled cell before the blank.
These are some of the reasons that I have come across where it makes sense to fill all the blank cells with 0 (zero), but I’m sure there would be other reasons as well.
In this tutorial I showed you three easy ways you can use to quickly fill all the blank cells with 0 in Excel.
The fastest way would be to use the Find and Replace technique which finds all the blank cells and replace the content of these cells with 0 (or any value that you specify).
You can also use the Go To Special method, which allows you to select all the blank cells and then manually enter 0 in all the blank cells in one go.
And finally, I showed you the VBA method that allows you to quickly select a range of cells that have blank cells and fill these with zero.
I hope this Excel tutorial helps you in your day-to-day Excel tasks.
Other Excel tutorials you may also find useful:
