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.
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.
➤ 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.
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.
➤ 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.
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.
After that, the Go To Special window will appear.
➤ Select Blanks and click on OK.
As a result, all of the blank cells will be selected.
➤ Now type 0 and press CTRL+ENTER.
As a result, all the blank cells will be replaced with 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.
➤ After that, select Advanced and check on the box Show a zero in cells that have zero value.
➤ At last, click on OK.
Now you will see the cells with 0 Value are showing 0 instead of being blanks.
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
- How to Calculate in Excel If Cells are Not Blank
- How to Apply Conditional Formatting in Excel If Another Cell Is Blank
- If a Cell Is Blank then Copy Another Cell in Excel
- Excel If Two Cells Are Blank Then Return Value
- How to Check If Cell Is Empty in Excel
- How to Check If Cell Is Empty Using Excel VBA
- Excel VBA: Check If Multiple Cells Are Empty
- How to Find If Cell is Blank in Excel
- How to Find & Count If a Cell Is Not Blank
<< Go Back to If Cell is Blank Then | Blank Cells | Excel Cells | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
