In this article, we will discuss how to perform if zero leave blank formula in excel. Sometimes you are dealing with a worksheet that requires you to leave a blank cell if zero value appears. On the other hand, you may have a personal preference to show blank cells if the value of the cell is zero. There are several ways to do that kind of task. We will discuss them in this guide.
Download Practice Workbook
Download this practice book to exercise the task while you are reading this article.
3 Easy Ways to Perform If Zero Leave Blank Using Excel Formula
Consider a situation where you are given a dataset containing the Item name, their Stock amount, and Sold numbers. We have to find the Current Stock number. If the current stock number shows zero, we have to leave a blank cell there. In this section, we will demonstrate 3 different methods to do this task.
1. Insert IF Function
Using the IF function, we can easily leave a blank instead of zero in cells. Let’s follow these steps to learn this method.
STEPS:
- Firstly, we will find out the current stock of our products.
- To do so, we will use this simple formula.
=C5-D5- Press ENTER to get the result.
- After that, drag down the formula using the Fill Handle option.
- From the result, we can see that we have zero values in some cells.
- So, we need to leave blanks in those cells.
- To do this, we will use the IF function, the required formula for this method is
=IF(C5-D5=0,"",C5-D5)- Then, press Enter to get the result.
- Now, drag down the formula using the Fill Handle option.
- From the result, we can see that the if function left a blank cell where the value is zero.
Read More: How to Fill Blank Cells with Formula in Excel (2 Easy Methods)
2. Apply Custom Formatting
We can use the custom formatting option in excel to leave a blank cell if the value is zero. Follow these steps below to learn!
STEPS:
- Firstly, select the E5:E13 range.
- After selection, go to the Home Tab.
- Then, go to the Number Ribbon.
- Now, click on the Number Format to open available options. Then, click on More Number Formats
- In the following step, select Custom in the Format Cells
- After that, type the required format to leave blank if the cell value is zero.
- In the type box, type
0;-0;;@- Then, click OK to get rid of the zeros.
- As a result, our final outcome is here.
Read More: How to Fill Blank Cells with Value from Left in Excel (4 Suitable Ways)
Similar Readings
- How to Calculate in Excel If Cells are Not Blank: 7 Exemplary Formulas
- Find If Cell is Blank in Excel (7 Methods)
- How to Find & Count If a Cell Is Not Blank (with Examples)
- Null vs Blank in Excel
- How to Delete Empty Cells in Excel (8 Easy Methods)
3. Use Conditional Formatting
Application of Conditional Formatting command is another way to leave blank if the cell value is zero in excel. This method is discussed in the following steps.
STEPS:
- Firstly, select the dataset.
- Then, click on Home >>Â Conditional Formatting >> Highlight Cell Rules >> Equal To.
- Now, put 0 in the Format Cells That are EQUAL TO field and Choose the Custom format to go.
- As we want to leave a blank cell instead of zero value, we will choose white as the font color.
- After that, click OK to confirm the changes.
- Finally, OK to get your job done.
- As a result, now we can see that we have blank cells where the values are zero.
Read More: How to Fill Blank Cells with Color in Excel (5 Methods)
Utilize Excel Options to Perform If Zero Leave Blank
You can change Excel Options to perform our task. In this way, the result will apply to the entire worksheet.
STEPS:
- Firstly, select the Current Stock column.
- After selection, go to File to open Options.
- Now, click on Options to continue.
- Now, find the Advanced Options section and click on it.
- After that, scroll down until you get Display Options for this Worksheet.
- In this section, we will see Show a zero in cells that have zero value Uncheck it to get our job done. OK to continue.
- As a result, we have got blank cells for the zero values.
Read More: If Cell is Blank Then Show 0 in Excel (4 Ways)
Things to Remember
- You can insert colored cells instead of a blank cell by using Conditional Formatting.
- You can apply blank space for zero values to the entire worksheet using the last method.
Conclusion
Today we have learned four effective and easy ways to leave blank if the value is zero. If you have any suggestions or queries regarding this article, please leave a comment. We will be happy to help you. To read similar articles, check out the ExcelDemy website. Keep learning!
Related Articles
- How to Find Average with Blank Cells in Excel (4 Easy Ways)
- Return Non Blank Cells from a Range in Excel (3 Suitable Ways)
- How to Make Empty Cells Blank in Excel (3 Methods)
- Fill Blank Cells with Dash in Excel (3 Easy Ways)
- How to Remove Blank Cells from a Range in Excel (9 Methods)
- If a Cell Is Blank then Copy Another Cell in Excel (3 Methods)
- How to Skip Blank Rows Using Formula in Excel (8 Methods)
