Md Shamse Tabrej
In this article, I’ll show you three excellent methods to insert 0 if a cell is blank in Excel. Furthermore, these strategies will allow you to solve the problem that brought you here. However, you will learn some important Excel features and functions in this post that will come in handy for any Excel job.
📁 Download Excel File
The sample worksheet that was used during the discussion is available to download for free right here.
Learn to Insert 0 If Any Cell Is Blank in Excel with These 4 Ways
Below we demonstrated three different approaches to insert 0 if a cell is blank in Excel. We considered production data as our dataset that contains four different columns representing Factories, unit Production, unit delivered, and units sold.
1. Apply IF Function in Order to Display 0 in Blank Cell
We’ll utilize the IF function to insert 0 if a cell is blank in Excel based on the data in another cell. We want to achieve If any cell in column E is empty, show a 0 in column F. To accomplish that we will try these steps.
⬇️⬇️ STEPS ⬇️⬇️
- Write the following formula in blank cell F6:
=IF(E6=””,0,E6)
- Now press Enter key and drag the fill handle to see the output.
Thus, you can get 0 for column E for the empty value of column F.
📕 Read More: 3 Ways to Fill Blank Cells with Text in Excel
2. Apply ISBLANK Function for Displaying 0 in Blank Cell
By using the ISBLANK function, we may display a 0 if the adjacent cell is blank. What we’re aiming for is to display a 0 in column F if any cell in E is blank. The steps are shown below:
⬇️⬇️ STEPS ⬇️⬇️
- Insert the following formula in empty cell F6.
=IF(ISBLANK(E6),0,E6)
- Now hit Enter button and drag the fill handle to see the output.
Thus, if column E is blank, column F will also be 0.
🔨 Formula Breakdown 👉 Criteria 1 = ISBLANK(E6): This criteria will check whether the cell E6 is empty or not and . 👉 IF(ISBLANK(E6),0,E6): Here, If the cell E6 is blank,will return the value of E6.
📕 Read More: 4 Ways to Find and Replace Blank Cells in Excel
3. Substituting Blank Cell with 0 Using Go To Special Feature
Use the “Go to Special features” menu item to change all zeros in blank cells. The steps are shown below:
⬇️⬇️ STEPS ⬇️⬇️
- Before doing anything else, pick your dataset, then head to Edit > Find & Select > Go To Special.
- The Go To Special menu will then show up.
- Choose Blanks and confirm with OK.
- Thus, all the empty cells will be chosen.
- Proceed by inserting 0 and hitting Ctrl+Enter.
Consequently, all of the empty cells will be filled in with 0.
4. Show 0 in the Blank Cell from Option Display Tab
Cells containing 0 that are incorrectly displayed as blank can be corrected in the display settings. Let’s assume some of the blank cells in our dataset actually contain the value 0. To fix this we try these steps:
⬇️⬇️ STEPS ⬇️⬇️
- Before choosing Options from the Home menu.
- Then, click on Advanced and mark the box Show a zero in cells that have zero value.
- Lastly, finish by selecting OK.
- Then you can see all the blank cells have a zero in your worksheet.
Thus Instead of being blank, the cells that previously displayed a value of 0 now display the number 0.
If Cell Is Blank Then Skip to Next Cell in Excel
We will try to solve the particular issue if a cell is blank we will skip to the next cell. To achieve that we will consider a data set containing that Id and Factory name. We will try to find out which Factory doesn’t have an ID. We will use the IF function to find that.
- To do that we will apply the following formula:
=IF(B6=””,C6,””)
- We will press Enter and drag the fill handle to see the outcome.
- Thus we can see the Factory that doesn’t have an Id.
📕 Read More: 10 Quick Tricks to Remove Blank Lines in Excel
If Cell Is Blank Then Return a Specific Value or Text in Excel
In this part, we will try to return a specific value if any cell is blank. So we will take a data set consisting of an ID, and Factory. We will take a column named Comments to return “Found” or “NOT Found” based on if they have an ID or not. Again we will use the IF function to accomplish that.
- Insert the following formula below:
=IF(B6=””,”Not Found”,”Found”)
- Press Enter and drag the fill handle to see the result.
- By this, we can return a specific value if a cell is blank.
📕 Read More: 4 Easy Ways to Fill Blank Cells in Excel with Formula
If Cell Is Blank Then Leave That Blank in Excel
In this part, we will try to leave a cell blank if a cell is blank. We will take a dataset containing Product, Quantity, Price, and Total. To calculate total l we often convey the operation quantity times the unit price. In that case, those which contain blank cells in the quantity column return 0 in the column total.
- To avoid that we will use the IF function to leave those cell blank. Insert the following formula:
=IF(C6=””,””,C6*D6)
- Press Enter and drag the fill handle to see the result.
- Thus we can leave a column blank that is supposed to be blank.
📕 Read More: 4 Ways to Leave Blank If Cell Contains Zero with Excel Formula
📄 Important Notes
🖊️ Verify that you’ve employed Absolute Reference when it was required. To get an absolute cell reference, press F4.
🖊️ Always look for ways that are easy to use in different situations.
🖊️ Every time you try to use shortcuts on the keyboard.
📝 Takeaways from This Article
📌 You can fix a cell with zero value if a cell is blank.
📌 Moreover, You can leave a cell and move to the next if a cell is blank.
📌 In addition, You can return a particular value if a cell is blank.
📌 Finally, you can leave a cell blank it is blank.
Conclusion
First and foremost, I hope you were able to apply the principles I taught in this Excel lesson on how to insert 0 if a cell is blank in Excel. As you can see, there are numerous ways to accomplish this. However, choose the strategy that is best suited to your situation with care. Most essential, if you become confused in any of the processes, I recommend repeating them. After reviewing the Excel file in the practice workbook that I’ve shared above, practice on your own since practice makes perfect. Above all, I sincerely hope you can put it to good use. Please share your thoughts in the comments section about how you felt throughout the article and what we should change or add to make things better for you. However, if you have any Excel-related issues, please leave them in the comments section. The Excelden team is available to answer your inquiries at any time. Finally, keep reading to educate yourself. Please visit our website Excelden.com for further Excel-related articles.
Related Articles
- 3 Approaches to Make Empty Cells Blank in Excel
- 4 Quick Tricks to Fill Blank Cells in Excel with N/A
- 9 Quick Tricks to Delete Row If Cell is Blank in Excel
- How to Determine and Count If Cell Is Not Blank in Excel
- How to Get Blank Cell Instead of Zero Using Formula in Excel
