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.

If Zero Leave Blank Using Excel Formula


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.

Dataset for Performing If Zero Leave Blank Using Excel Formula


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.

Finding Out Current Stock in Dataset Using Subtraction Formula

  • After that, drag down the formula using the Fill Handle option.

Dragging Down Formula Using Fill Handle

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

Inserting Formula for Leaving Blank Cell After Finding Zero Cell

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.

Dragging Down Formula Using Fill Handle Technique

  • From the result, we can see that the if function left a blank cell where the value is zero.

Result After Leaving Blank Cell If Finding Zero in Cells

Read More: How to Fill Blank Cells with Formula in Excel


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.

Selecting Range for Leaving Zero After Finding Zero

  • 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

Selecting Number Format

  • In the following step, select Custom in the Format Cells

Selecting Custom Format to Perform If Zero Leave Blank

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

Typing Formula for Leaving Blank Cell After Finding Zero Cell

  • As a result, our final outcome is here.

Result After Applying Custom Formatting to Perform If Zero Leave Blank

Read More: How to Fill Blank Cells with Value from Left in Excel


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.

Selecting Conditional Formatting

  • Now, put 0 in the Format Cells That are EQUAL TO field and Choose the Custom format to go.

Defining Condition For Conditional Formatting

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

Selecting Font Color For Zero

  • After that, click OK to confirm the changes.

Confirming Font Color of Zero

  • Finally, OK to get your job done.

Confirming Formatting of Zero After Finding

  • As a result, now we can see that we have blank cells where the values are zero.

Result After Leaving Blank After Finding Zero

Read More: How to Fill Blank Cells with Color in Excel


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.

Selecting Range to Apply Excel Options

  • Now, click on Options to continue.

Choosing Excel Options

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

Formatting Settings in Advanced Excel Options

  • As a result, we have got blank cells for the zero values.

Result After Performing If Zero Leave Blank Utilizing Excel Options

Read More: If Cell is Blank Then Show 0 in Excel


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.

Download Practice Workbook

Download this practice book to exercise the task while you are reading this article.


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!


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Asikul Himel
Asikul Himel

Asikul Islam Himel, holding a BSc in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology, has contributed over two years to the ExcelDemy project. Starting as an Excel & VBA Content Developer, now he manages projects at You Have Got This Math Project. He wrote 60+ articles for ExcelDemy, reviewed 500+, and focused on quality maintenance. Currently, his responsibilities include project management and team leadership. Himel's interests encompass data analysis, leadership, WordPress applications, and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo