During the many calculations phase, we encounter Blank cells, which leads to an output of zero. But in 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.
How to Leave Cell Blank If There Is No Data: 5 Easy Ways
We are going to use the below dataset, for demonstration purposes. We have Product ID, 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: How to Ignore Blank Cells in Range in Excel
2. Combining IF and ISBLANK Functions
Utilizing the combination of IF and ISBLANK functions, we can find if the cell in Excel is Blank and then Leave it Blank if there is no data available for display.
Steps
- If you look 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 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.
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 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 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 form 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.
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 this, they are not in a Blank state. They still show 0 values.
- To resolve this issue and put Blank cells in the cells that have no 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 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.
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.
Download Practice Workbook
Download this practice workbook below.
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 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 appreciated.
Related Articles
- How to Delete Blank Cells and Shift Data Up in Excel
- How to Remove Blank Cells in Excel
- How to Remove Blank Lines in Excel
- How to Delete Blank Cells and Shift Data Left in Excel
- How to Remove Unused Cells in Excel
- How to Remove Blank Cells from a Range in Excel
<< Go Back to Blank Cells | Excel Cells | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
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.
at first it appeared complicated but this was easy to follow.
Dear Alex,
Thanks for your feedback.
Regards
Exceldemy
Thanks for a great article!
How do you leave a blank cell, as apposed to “$N/A”, when you are using index and match function.
My cell currently has the formula: =INDEX(‘Round 1′!$B$3:$AJ$25,MATCH(A5,’Round 1’!$B$3:$B$25, 0),34).
Not all of the “matches” will be found on each worksheet (it is a scoresheet and not all individuals compete each round), and therefore return the $N/A.
Thanks!
Hello Lory,
You can use the following formula: =IFERROR(INDEX(‘Round 1′!$B$3:$AJ$25,MATCH(A5,’Round 1’!$B$3:$B$25, 0),34),””)
It will leave a cell blank if no matches are found.
Regards
ExcelDemy