Prantick Bala
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.
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.
➤ 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.
Read More: How to Perform If Zero Leave Blank Formula in Excel (3 Methods)
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, the cells of column E are displaying 0 if the cells of column D of the same row are blanks.
Read More: How to Fill Blank Cells with 0 in Excel (3 Methods)
Similar Readings
- How to Ignore Blank Cells in Excel Sum (6 Ways)
- VBA to Count Blank Cells in Range in Excel (3 Methods)
- How to Autofill Blank Cells in Excel with Value Above (5 Easy Ways)
- Excel VBA: Find the Next Empty Cell in Range (4 Examples)
- How to Skip Blank Rows Using Formula in Excel (8 Methods)
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.
Read More: How to Fill Blank Cells in Excel with Go To Special (With 3 Examples)
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.
Read More: How to Deal with Blank Cells That Are Not Really Blank in Excel (4 Ways)
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 Find and Replace Blank Cells in Excel (4 Methods)
- Fill Blank Cells with Text in Excel (3 Effective Ways)
- How to Fill Blank Cells with N/A in Excel (3 Easy Methods)
- Fill Blank Cells with Dash in Excel (3 Easy Ways)
- How to Fill Blank Cells with Value from Left in Excel (4 Suitable Ways)
- Fill Blank Cells with Color in Excel (5 Methods)
