How to fill blank cells with zero or any specific value – Excelchat

There are many easy ways to fill in blanks in a spreadsheet, especially when it makes our reports look clumsy. In this tutorial, we will discuss four easy ways to fill zero or specific values in blank cells without using conditional formatting. We will explore how to fill blanks using:

1. Go to Special
2. Find & Replace
3. Pivot Table Options
4. VBA

Figure 1 – Result of how to fill in blanks

How to fill blank cells with 0 using the Go To Special Command

• We will highlight the cells in our worksheet

Figure 2 – Using Excel fill in blank cells

• Next, we will press Ctrl + G to open the Go To Dialog box and then click Special. Or we can go to Find & Replace and select Go To Special from the drop-down menu

Figure 3 – Fill in empty cells using Go To Special

• In the Go To Special dialog box, we will select the Blanks option and click on the OK Button

Figure 4 – How to replace blanks with 0

• Our Blank cells will be highlighted.

Figure 5 – Excel Replace blank cells with zeros

• Next, we will type 0 or any other value.
• We will press Ctrl + Enter alongside and automatically fill blank cells with zero or our specific value

Figure 6 – Fill blanks with 0

How to fill Empty cells using the Find & Replace Command

• We will go to Find & Replace dialog box by pressing Ctrl + H or select Find & Select from the Home Tab followed by the Replace button

Figure 7 – How to use to replace blank with 0

• We will leave Find what text box “blank” and type 0 (any other unique value) in the Replace with text box

Figure 8 – Find and Replace blanks with Zero

• We will click on Replace All and select OK. Now, we will close the Find and Replace Dialog box.

Figure 9 – How to fill empty cells

How to fill blanks with Zeros in Pivot Table

• Let’s suppose we make a Pivot Table with the original data

Figure 10 – Fill empty cells in Pivot table

• To replace blank cells in the Pivot Table with zeroes, we will right-click any cell in the Pivot Table and select Pivot Table Options

Figure 11 – How to fill blank cells

• In the Pivot Table Options Dialog Window, we will go to the Layout & Format Tab and mark For Empty Cells. Next, we will enter 0 (or any specific value) in the text box next to it.

Figure 12 – Using Excel to replace blanks with zeros

• We will click OK

Figure 13 – How to fill empty cells

Fill Blank Cells with 0 or specific value with VBA code

• We will highlight our range of cells that contain the blank cells
• We will go to the Developer tab and select Visual Basic

Figure 14 – Fill empty cells in excel

• A new Microsoft Visual Basic Applications window will be displayed. We will click on Insert and select Module and copy the VBA code below.

Sub FillEmptyBlankCellWithValue()

Dim cell As Range

Dim InputValue As String

On Error Resume Next

InputValue = InputBox("Enter value that will fill empty cells in selection", _

"Fill Empty Cells")

For Each cell In Selection

If IsEmpty(cell) Then

cell.Value = InputValue

End If

Next

End Sub

Figure 15 – Excel replace blanks with zeros

• When we run the code, we will see a dialog box like this:

Figure 16 – How to fill empty cells in excel

• Here we can insert the value we wish to see. In this case, we will type null

Figure 17 – how to fill blank cells with the value above

• We will press OK.

Figure 18 – Using Excel to replace blank with null

Instant Connection to an Excel Expert

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.