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:
=IF(C5="","",C5*D5)
- 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:
=IF(ISBLANK(C5), "", C5*D5)
Formula Breakdown
- ISBLANK(C5): This function will check C5 cell whether it is Blank or not. If it is Blank, then it will return a Boolean True. Otherwise, it will return a Boolean False.
- IF(ISBLANK(C5), “”, C5*D5): Depending on the return from the ISBLANK function, the IF function will return “”, if the return from the ISBLANK function is True. Otherwise, if the return from the ISBLANK function is False, the IF function will return the value of C5*D5.
- 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:
=IF(ISNUMBER(C5),C5*D5,"")
Formula Breakdown
- ISNUMBER(C5): This function will check C5 cell whether it is a number or not. If it is a number, then it will return a boolean True. Otherwise, it will return a Boolean False.
- IF(ISNUMBER(C5),C5*D5,””): Depending on the return from the ISNUMBER function, the IF function will return “”, if the return from the ISBLANK function is False. Otherwise, if the return from the ISNUMBER function is True IF function will return the value of C5*D5.
- 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.
💬 Note
- The ISNUMBER will return True only f the entry is Number. For any forms of non-numerical value, like Blank, space, etc., the ISNUMBER will return False.
- So, the formula here will make the cell Blank whether the cell content is Blank or other non-numerical characters. Users need to be aware of this.
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.
💬 Note
- In the custom formatting dialog box, we need to type “;;” after General. At the same time, we need to put a $ sign in front of the General, because of keeping the Currency format. Otherwise, this will strip off the Currency format from the numbers.
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.
💬 Note
- You need to edit the code for your dataset, for selecting your intended range of cells.
- Try to avoid adding any non-numerical column or row in the range. Add a range of cells that are absolutely necessary only.
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.
My preference was to use the formatting method, less messing around, nice output – I can fill the calculation in for the entire column, and any zero figures will disappear. However, unfortunately it also hides negative numbers which isn’t ideal in some circumstances!
Hello, OZTIMS!
Thanks for sharing your problem with us!
Instead of using this format ($General;;), you can use the ($0;-0;;@). This will keep the negative values. To use this follow method-4.
1. When the Format Cells dialog box will appear, go to Number > Custom.
2. Type $0;-0;;@ in the Type field.
3. Finally, click OK.
4. Now, if you see the result, this will also show a negative number. The cell will only be blank if the cell has no data.
Hope this will help you.
Good Luck!
Regards,
Sabrina Ayon
Author, ExcelDemy.