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.
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.
- 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.
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.
- 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.
- We have blank cells for the zero values.
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
- How to Find Average with Blank Cells in Excel
- Return Non Blank Cells from a Range in Excel
- How to Make Empty Cells Blank in Excel
- Fill Blank Cells with Dash in Excel
- How to Remove Blank Cells from a Range in Excel
- If a Cell Is Blank then Copy Another Cell in Excel
- How to Skip Blank Rows Using Formula in Excel
