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

There are several ways to make zero values into blank cells. Here’s a GIF overview of how you can do that.

## 3 Easy Ways to Perform If Zero Leave Blank Using Excel Formula

Consider a situation with 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.

### Method 1 – Insert the IF Function

Steps:

• Find out the current stock of products with the following formula in E5:
`=C5-D5`
• Press Enter to get the result.

• Drag down the formula using the Fill Handle.

• We can see that we have zero values in some cells.
• Replace the function with the following (in E5):
`=IF(C5-D5=0,"",C5-D5)`
• 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.
• Drag down the formula using the Fill Handle.

• The IF function left a blank cell where the value is zero.

### Method 2 – Apply Custom Formatting

Steps:

• Select the E5:E13 range.

• Go to the Home Tab.
• Go to the Number Ribbon.
• Click on the Number Format to open available options. Choose More Number Formats

• Select Custom in the Format Cells

• Use the following format:
`0;-0;;@`
• Click OK to get rid of the zeros.

• Here’s the final result.

### Method 3 – Use Conditional Formatting

Steps:

• Select the dataset.
• Click on Home, go to Conditional Formatting, select Highlight Cell Rules, and choose Equal To.

• Put 0 in the Format Cells That are EQUAL TO field and choose Custom format.

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

• Click OK to confirm the changes.

• Click OK again.

• We have blank cells where the values are zero.

## Use Excel Options to Ensure that Zero Values Leave Blank

Steps:

• Select the Current Stock column.
• Go to File to open Options.

• Click on Options to continue.

• Find the Advanced Options section and click on it.
• Scroll down until you get Display Options for this Worksheet.
• You will see Show a zero in cells that have zero value. Uncheck it and press OK to continue.

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.

## Related Articles

Get FREE Advanced Excel Exercises with Solutions!
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

Advanced Excel Exercises with Solutions PDF