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

Get FREE Advanced Excel Exercises with Solutions!

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


If Cell is Blank Then Show 0 in Excel: 4 Ways

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 blank.

excel if blank then 0


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, that the cells of column E are displaying 0 if the cells of column D of the same row are blank.

excel if blank then 0

Read More: How to Return Value If Cell is Blank


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


Download Practice Workbook


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.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Prantick Bala
Prantick Bala

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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo