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

The sample dataset contains the columns Product ID, Quantity, Unit Price, and Cost columns where some of the values for quantity are missing. This leads to some entries in the column cost to be zero. We’ll modify the formula so that those cells lose the values.

### Method 1 – Using the IF Function

Steps

• The cells E7, E9 E12, and E14 are actually empty.
• The numerical value of those cells is equal to 0 and the cell formatting is expressing it as such.

• The formulas in the range of cells F5:F14 are shown below. These formulas force the cells to show zero values with the Currency format.

• Replace the formula in F5 with the following:

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

• Drag the Fill Handle to cell E14.

### Method 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

• The cells E7, E10, and E12 are empty. The formulas in the range of cells F5:F14 are shown below. Despite not having a value, these formulas force the cells to show zero values with Currency format.

• Replace the formula in F5 with the following:

`=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.
• Drag the Fill Handle to cell E14.

### Method 3 – Applying IF and ISNUMBER Functions

Steps:

• The cells E7, E10, and E12 are empty. The formulas in the range of cells F5:F14 are shown below. Despite not having a value, these formulas force the cells to show zero values with Currency format.

• Replace the formula in F5 with the following:

`=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.
• Drag the Fill Handle to cell E14.

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.

### Method 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

• The cells E7, E10, and E12 are empty. The formulas in the range of cells F5:F14 are shown below. Despite not having a value, these formulas force the cells to show zero values with Currency format.

• Select the range of cells D5:F14.
• Right-click on the range.
• From the context menu, click on Format Cells.

• In the Format Cells dialog box, click on Custom from the Number tab.
• Type “\$General;;” in the Type field and then click OK.

• The values without data show up as blank.

Note

• In the custom formatting dialog box, we need to type “;;” after General. We also need to put a \$ sign in front of the General to keep the Currency format.

### Method 5 – Embedding VBA Code

Steps

• Go to the Developer tab, then click Visual Basic.

• Click Insert and select 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``````

• Close the window.
• Go to the View tab and select View Macros.

• Select the macro that was created just now. The name we used is Blank_Cell.
• Click Run.

• The cells without the data now show a Blank cell instead of \$0.

## Related Articles

<< Go Back to Excel Cells | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Rubayed Razib Suprov

Rubayed Razib, holding a BSC degree in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a devoted member of the ExcelDemy project. He has contributed significantly by authoring numerous articles and showcasing proficiency in VBA. Razib efficiently automates Excel challenges using VBA macros and actively participates in the ExcelDemy forum, providing valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel,... Read Full Bio

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.

Good Luck!

Regards,
Sabrina Ayon
Author, ExcelDemy
.

2. at first it appeared complicated but this was easy to follow.

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

Advanced Excel Exercises with Solutions PDF