Since Excel basically has a limitless number of formulas that can be used, there is always a way to figure out the solution for every problem.
In our work, sometimes we do not want to show empty values. We want to replace these with certain figures, and we will explain how to do that in the example below.
Displaying Zero for Empty Cells in Excel with Formula
For our example, we will have the random numbers in the first three columns, valued from 100,000 to 300,000, and some of the cells will be empty:
To populate the empty cells in our range, we can either do it manually, semi-automatically, or automatically with VBA.
Manually, we would go in and insert the zeroes in the empty cells. Semi-automatically, we can create another range, but using the IF formula. We will create the range starting in cell E2, and will insert the following formula:
|
1 |
=IF(ISBLANK(A2),0,A2) |
We will drag the formula in order to replicate the original table, and the new range will be E2:G8:
Displaying Zero for Empty Cells in Excel with VBA
Of course, this formula above only works if we replicate the data. To achieve this in the original range, we need to use VBA. We will open the VBA by clicking the ALT + F11 combination. On the window that appears, we will go to the Insert tab, and then choose Module:
In the window that appears, we will type in our formula:
|
1 2 3 4 5 6 7 8 9 10 |
Sub ZeroForEmtpyCells() Dim r As Range Dim c As Range Set r = Range("A1:C8") For Each c In r If c.Value = "" Then c.Value = 0 End If Next c End Sub |
This will be our formula in the module:
This formula declares two variables- r and c as range, then it sets the r cell to be equal to our range (A1:C8). For the final part, we use For Next Loop to inspect every cell in our range. If any of the cells are empty, we change the value of that cell to 0.
When we execute the code by clicking on the F5 on the keyboard while in the module, we will have empty cells populated with zeroes:
