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

Get FREE Advanced Excel Exercises with Solutions!

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.

Leave Cell Blank If There Is No Data


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.

Using IF Function to Leave Cell Blank If There Is No Data in Excel

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

Using IF Function to Leave Cell Blank If There Is No Data in Excel

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

=IF(C5="","",C5*D5)

Using IF Function to Leave Cell Blank If There Is No Data in Excel

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

Combining IF and ISBLANK Functions to Leave Cell Blank If There Is No Data in Excel

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

Combining IF and ISBLANK Functions to Leave Cell Blank If There Is No Data in Excel

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


Similar Readings


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.

Applying IF and ISNUMBER Functions to Leave Cell Blank If There Is No Data in Excel

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

Using Custom Formatting to Leave Cell Blank If There Is No Data in Excel

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

Embedding VBA Code to Leave Cell Blank If There Is No Data in Excel

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

Embedding VBA Code to Leave Cell Blank If There Is No Data in Excel

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


Related Articles

Rubayed Razib Suprov

Rubayed Razib Suprov

Hi, I am Rubayed Razib Suprov. Graduated from the Bangladesh University of Engineering Technology in Naval Architecture and Marine Engineering department. I joined Exceldemy on March 2022. I have a keen interest in both fluid dynamics and data analysis. I try to teach people what I learned so far about the Excel tool to date, and hope I can make some positive impacts.

2 Comments
  1. 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
      .

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo