If Cell is Blank Then Show 0 in Excel (4 Ways)

In Excel, if there is no data in any cell, it normally remains blank. But you can display 0 in the blank cells by following some techniques. In this article, you will find 4 ways to show 0 if the cell is blank in Excel.

Suppose, we have a dataset where production information of different factories of a company is given. A unit is considered ready to sell when packaging is done. Now, in the Unit Ready to sell column (column E) we want to show 0 if any cell in the Unit packaged column (column D) of the same row is blank.

dataset


Download Practice Workbook


4 Ways to Display 0 in Excel If Cell is Blank

1. IF Function to Show 0 in Blank Cell

We can use the IF function to show 0 in a blank cell based on the data of another cell.

To show 0 in the cells of column E if any cell of column D is blank,

➤ Type the following formula in cell E6,

=IF(D6="",0,D6)

The formula will show 0 in E6, if D6 is empty. Otherwise, it will show the value of D6 in E6.

IF

➤ Now, press ENTER and drag the cell E6 to apply the same formula in all other cells of column E.

As a result, you will see, the cells of column E are showing 0 if the cells of column D of the same row are blanks.

excel if blank then 0

Read more: How to Return Value if Cell is Blank


2. ISBLANK Function to Display 0

We can also use the ISBLANK function to display 0 if another cell is blank.

To show 0 in the cells of column E if any cell of column D is blank,

➤ Type the following formula in cell E6,

=IF(ISBLANK(D6),0,D6)

Here the ISBLANK function will determine if cell D6 is blank or not and if D6 is blank, the formula will display 0 in the cell E6.

ISBLANK

➤ Now press ENTER and drag the cell E6 to apply the same formula in all other cells of column E.

As a result, you will see, the cells of column E are displaying 0 if the cells of column D of the same row are blanks.

excel if blank then 0


Similar Readings:


3. Replacing Blank Cell with 0 Using Go to Special

We can replace all the blank cells with 0 by using Go to Special features.

➤ First, select your dataset and go to Editing > Find & Select > Go To Special.

EDITING

After that, the Go To Special window will appear.

➤ Select Blanks and click on OK.

GO TO SPECIAL

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

SELECT BLANK CELLS

➤ Now type 0 and press CTRL+ENTER.

As a result, all the blank cells will be replaced with 0.

excel if blank then 0


4. Display 0 in Blank Cells from Display Options

If you have cells with 0 but they are showing blank, you can fix this from the display options. Suppose some of the cells of our dataset have a value 0 but it is showing blank. To fix this,

➤ Go to the Home tab and select Options.

OPTION

➤ After that, select Advanced and check on the box Show a zero in cells that have zero value.

At last, click on OK.

excel if blank then 0

Now you will see the cells with 0 Value are showing 0 instead of being blanks.

excel if blank then 0


Conclusion

If you follow any of the above described ways based on your requirement, Excel will show 0 if the cell is blank. Please leave a comment if you have any queries.


Further Readings

Prantick

Prantick

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo