How to Leave Cell Blank If There Is No Data in Excel (5 Ways)

During the many calculations phase, we encounter Blank cells, which leads to an output of zero. But many cases, the Blank cell compared to the zero value as output is preferred. As it can provide more clarity in the understanding of the calculations. If you are curious to know how you can Leave a cell Blank if there is no data in it, then this article may come in handy for you. In this article, we discuss how you can Leave a cell Blank if there is no data in Excel with an elaborate explanation.

Download Practice Workbook

Download this practice workbook below.

5 Easy Ways to Leave Cell Blank If There Is No Data

We are going to use the below dataset, for demonstration purposes. We have Product id, their Quantity, Unit Price, and Cost. etc. there are some entries in the column Quantity which does not include any value. So that leads to some entries in the column Cost to be Zero. But we want to Leave them to be full Blank, instead of showing zero. We deploy 5 separate ways by following which you resolve this issue.

1. Using IF Function

Using the IF function, we can Leave the cell Blank in Excel if there is no data to display in the cell.

Steps

• If you look closely, then you will notice that the cells E7, E9 E12, and E14 are actually empty.
• The numerical value of those cells is equal to 0. But still, those cells are occupied with a $0 value.
• We need to completely remove all contents in those cells. In other words, we want to put those cells in the Blank state.

• The reason behind those cells is not Blank despite having no data, is because of the formulas that are being used here.
• The formulas in the range of cells F5:F14 are shown here. These formulas force the cells to show zero values with Currency format.

• In order to Leave a cell Blank where no data is present, we will enter the following formula:

• Then drag the Fill Handle to cell E14.
• Doing this will execute the same formula as before, but this time the zero values are shown and left as Blank cells.

• This is how we can Leave a cell Blank if there is no data in the cell.

Read More: Excel IFERROR Function to Return Blank Instead of 0

2. Combining IF and ISBLANK Functions

Utilizing the combination of IF and ISBLANK functions, we can check whether is the cell in Excel is Blank and then Leave it Blank if there is no data available for display.

Steps

• If you notice closely, then you will notice that the cells E7, E10, and E12 are empty.
• The numerical value of those cells is equal to 0. But still, those cells are occupied with a $0 value.
• The primary these cells are showing zero values not a Blank cell is because of their formula and formatting.
• The formulas in the range of cells F5:F14 are shown here. These formulas force the cells to show zero values with Currency format.

• To bypass this problem, we can enter the following formula:

• Then drag the Fill Handle to cell E14.
• Doing this will execute the same formula as before, but this time the zero values are not showing and are left as Blank cells.

Read More: How to Use VLOOKUP to Return Blank Instead of 0 (7 Ways)

Similar Readings

• How to Remove Zeros in Front of a Number in Excel (6 Easy Ways)
• Hide Rows with Zero Values in Excel Using Macro (3 Ways)
• How to Hide Chart Series with No Data in Excel (4 Easy Methods)
• Hide Zero Values in Excel Pivot Table (3 Easy Methods)

3. Applying IF and ISNUMBER Functions

Deploying the combination of IF and ISNUMBER functions, we can check whether the cell is Blank and then Leave it as Blank if there is no data available for display.

Steps

• If you look closely, then you will notice that the cells E7, E9 E12, and E14 are actually empty.
• The numerical value of those cells is equal to 0. But still, those cells are occupied with a $0 value.
• We need to entirely remove all contents in those cells. In other words, we want to put those cells in the Blank state.
• The reason behind those cells is not Blank despite having no data, is because of the formulas that are being used here.
• The formulas in the range of cells F5:F14 are shown here. These formulas force the cells to show zero values with Currency format.

• To bypass the problem, we enter the following formula:

• Then drag the Fill Handle to cell E14.
• Doing this will execute the same formula as before, but this time the cells which do not contain any data will be left, Blank.

Read More: How to Apply VLOOKUP to Return Blank Instead of 0 or NA

4. Using Custom Formatting

Custom formatting will help us to select individual cells and then format them Leave only the Blank cells if there is no other data available for display.

Steps

• In the dataset shown below, we can observe that the cells E7, E9, E12, and E14 now have zero data in those cells, But in spite of it, they are not in a Blank state. They still show 0 values.

• To resolve this issue and put Blank cell in the cells that have now data, we can re-format the dataset. Which can show the Blank cell if there is no data in it.
• To do this, select the range of cells D5:F14.
• And then right-click on it.
• From the context menu, click on the Format Cells.

• In the format cells dialog box, click on the Custom from the Number tab.
• Then type “$General;;” in the Type field and then click OK.

• After clicking OK, you will notice that the values are now showing as Blank if there is not any data.

Read More: How to Use XLOOKUP to Return Blank Instead of 0

5. Embedding VBA Code

Using a simple VBA Macro can drastically reduce the time to detect and Leave cells Blank if there is no data.

Steps

• First, go to the Developer tab, then click Visual Basic.

• Then click Insert > Module.

• In the Module window, enter the following code.

Sub Blank_Cell()
Dim x As Range
For Each x In Range("B5:F14")
If x.Value = 0 Then x.Value = " "
Next x
End Sub

• Then close the window.
• After that, go to the View tab > Macros > View Macros.

• After clicking View Macros, select the macros that you created just now. The name here is Blank_Cell. Then click Run.

• After clicking  Run, you will notice the cells without the data now show Blank cell instead of $0. We also managed to keep the Currency format for the rest of the cell intact.

Conclusion

To sum it up, the issue of leaving a cell Blank if there is no data is resolved by providing 5 separate solutions. those methods involved the use of IF, ISBLANK, and ISNUMBER functions. We also used the VBA macro. The VBA macro method requires prior VBA-related knowledge to understand from the scratch.

For this problem, a macro-enabled workbook is attached where you can practice these methods.

Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the Exceldemy community will be highly appreciable.

Related Articles

• Exclude Zero Values with Formula in Excel (3 Easy Ways)
• Hide Zero Data Labels in Excel Chart (4 Easy Ways)
• How to Hide Zero Values in Excel Pie Chart (3 Simple Methods)
• Ignore Blank Cells in Excel Bar Chart (4 Easy Methods)
• How to Ignore Blank Series in Legend of Excel Chart