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.

If Zero Leave Blank Using Excel Formula


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.

Dataset for Performing If Zero Leave Blank Using Excel Formula


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.

Finding Out Current Stock in Dataset Using Subtraction Formula

  • Drag down the formula using the Fill Handle.

Dragging Down Formula Using 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.

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.
  • Drag down the formula using the Fill Handle.

Dragging Down Formula Using Fill Handle Technique

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

Result After Leaving Blank Cell If Finding Zero in Cells


Method 2 – Apply Custom Formatting

Steps:

  • Select the E5:E13 range.

Selecting Range for Leaving Zero After Finding Zero

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

Selecting Number Format

  • Select Custom in the Format Cells

Selecting Custom Format to Perform If Zero Leave Blank

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

Typing Formula for Leaving Blank Cell After Finding Zero Cell

  • Here’s the final result.

Result After Applying Custom Formatting to Perform If Zero Leave Blank

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


Method 3 – Use Conditional Formatting

Steps:

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

Selecting Conditional Formatting

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

Defining Condition For Conditional Formatting

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

Selecting Font Color For Zero

  • Click OK to confirm the changes.

Confirming Font Color of Zero

  • Click OK again.

Confirming Formatting of Zero After Finding

  • 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


Use Excel Options to Ensure that Zero Values Leave Blank

Steps:

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

Selecting Range to Apply Excel Options

  • Click on Options to continue.

Choosing Excel Options

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

Formatting Settings in Advanced Excel Options

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 the Practice Workbook


Related Articles

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