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.
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.
- First, we will find out the current stock of our products. We will use this simple formula to do this.
- Press ENTER to get the result.
- 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
- Where logical_test is C4-D4=0
- The function will leave a blank cell if the value is true.
- Otherwise, it will show numbers.
- 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.
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!
- From your Home Tab, go to the Number Ribbon. Click on the number format to open available options. Then click on More Number Formats
- Select Custom in the Format Cells
- 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.
- So our final result is here.
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.
- From your Home Tab, click on the Conditional Formatting and select Highlight Cell Rules.
- Then choose the Equal To option to continue
- Put 0 in the Format Cells That are EQUAL TO 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.
- Click OK to confirm the changes.
- Finally, OK to get your job done.
- From our worksheet, now we can see that we have blank cells where the values are zero.
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.
- Select the column then go to File to open Options.
- Click on the Options to continue.
- Now find the Advanced Options section and click on it. 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.
- We have got blank cells for the zero values.
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.
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!