# How to Perform If Zero Leave Blank Formula in Excel (3 Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.

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

Here, if the logical_test is C5-D5=0, The function will leave a blank cell if the value is true. Otherwise, it will show numbers.
• 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)

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

## Related Articles

Asikul Himel

Hi! I am Asikul Islam Himel. Glad you are here. I am a Team Leader of ExcelDemy, running an excellent team of five efficient Excel & VBA Content Developers. Here at ExcelDemy, we give the best sustainable solutions by posting articles related to MS Excel-related problems. I have completed my under graduation degree from Bangladesh University of Engineering and Technology and my program was Naval Architecture and Marine Engineering. I have found passion in data analysis and research-based fields. I am currently working to grow my leadership quality. I have a great interest in project management and critical thinking. In my free time, I love to travel and read books.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF