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

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.


4 Easy Ways to Perform If Zero Leave Blank Formula in Excel

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 four different methods to do this task.

overview


1. Insert the IF Function to Perform If Zero Leave Blank

Using the IF function, we can easily leave a blank instead of zero in cells. Let’s follow these steps to learn this method.

Step 1:

  • First, we will find out the current stock of our products. We will use this simple formula to do this.
=C4-D4

insert the IF Function to Perform If Zero Leave Blank

  • Press ENTER to get the result.

Insert the IF Function to Perform If Zero Leave Blank

Step 2:

  • From the result of our calculation, we can see that we have zero values in some cells. 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(C4-D4=0,"",C4-D4)
  • Where logical_test is C4-D4=0
  • The function will leave a blank cell if the value is true.
  • Otherwise, it will show numbers.

Insert the IF Function to Perform If Zero Leave Blank

  • Get the result by pressing ENTER. Move your cursor to the bottom right corner of the cell until it shows this sign (+). Then double-click on the sign to apply the same function to the rest of the cells. From the result, we can see that the if function left a blank cell where the value is zero.

Insert the IF Function to Perform If Zero Leave Blank


2. Apply Custom Formatting to Perform If Zero Leave Blank

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!

Step 1:

  • From your Home Tab, go to the Number Ribbon. Click on the number format to open available options. Then click on More Number Formats

Apply Custom Formatting to Perform If Zero Leave Blank

Step 2:

  • Select Custom in the Format Cells

Apply Custom Formatting to Perform If Zero Leave Blank

  • Type the required format to leave blank if the cell value is zero. In the type box, type 0;-0;;@. Click OK to continue. And that will get you rid of the zeros.

Apply Custom Formatting to Perform If Zero Leave Blank

  • So our final result is here.

Apply Custom Formatting to Perform If Zero Leave Blank


3. Use the Conditional Formatting to Perform If Zero Leave Blank

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.

Step 1:

  • From your Home Tab, click on the Conditional Formatting and select Highlight Cell Rules.

Use the Conditional Formatting to Perform If Zero Leave Blank

  • Then choose the Equal To option to continue

Use the Conditional Formatting

Step 2:

  • Put 0 in the Format Cells That are EQUAL TO Choose the Custom format to go.

Use the Conditional Formatting

  • As we want to leave a blank cell instead of zero value, we will choose white as the font color.

Use the Conditional Formatting to Perform If Zero Leave Blank

  • Click OK to confirm the changes.

Use the Conditional Formatting to Perform If Zero Leave Blank

  • Finally, OK to get your job done.

Use the Conditional Formatting to Perform If Zero Leave Blank

  • From our worksheet, now we can see that we have blank cells where the values are zero.

Use the Conditional Formatting to Perform If Zero Leave Blank


4. Change 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.

Step 1:

  • Select the column then go to File to open Options.

Change Excel Options to Perform If Zero Leave Blank

  • Click on the Options to continue.

Change Excel Options

Step 2:

  • Now find the Advanced Options section and click on it. Scroll down until you get Display Options for this Worksheet.

Change Excel Options

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

Change Excel Options

  • We have got blank cells for the zero values.

Change Excel Options


Things to Remember

👉 You can insert colored cell instead of the blank cell by using Conditional Formatting

👉 You can apply blank space for zero values to the entire worksheet using Method 4.


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. Keep learning!

Tags:

Asikul Himel
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo