Eshrak Kader
Certainly, Microsoft Excel is a popular tool for arranging and manipulating data. Now, what if you need to check for blank cells? With this question in mind, this article shows 5 ways of how to use ISBLANK function to check if cell is blank in Excel. In addition, we’ll also explore how to sum values if a cell is not blank.
How to Use ISBLANK Function If Cell Is Blank in Excel: 5 Examples
First and foremost, let’s consider the Car Information dataset in the B4:D15 cells containing the “Name”, “Maker”, and “Horsepower” columns respectively. Here, we want to use the ISBLANK function to check if a cell is blank in Excel. Therefore, let’s glance at each method in detail and with the necessary illustrations.
Here, we have used the Microsoft Excel 365 version; you may use any other version at your convenience.
1. Checking If Cell Is Blank
First of all, let’s begin with a simple example of using the ISBLANK function, which returns a TRUE or FALSE value depending on whether the cell is blank or not.
📌 Steps:
- Initially, go to the E5 cell >> enter the formula given below >> press the ENTER button.
=ISBLANK(D5)
Here, the D5 cell refers to the “Horsepower” value of “315”.
Voila! The resulting image shows the function returning TRUE for the corresponding blank cells.
Read More: How to Use SUMIF and ISBLANK to Sum for Blank Cells in Excel
2. Utilizing IF and ISBLANK Functions
Besides, we can combine the popular IF and ISBLANK functions to return a text message or leave them blank depending on whether the reference cell is blank. So, let’s see them in action.
2.1 Returning Text If Cell Is Blank
For instance, we‘ll use the IF and ISBLANK functions to check if the cell is blank; if true, then produce the string “Blank”, otherwise generate “Not Blank”.
📌 Steps:
- Initially, move to the E5 cell >> type the following expression >> click the ENTER key.
=IF(ISBLANK(D5), "Blank", "Not Blank")
- IF(ISBLANK(D5), “Blank”, “Not Blank”) → checks whether a condition is met and returns one value if TRUE and another value if FALSE. Here, ISBLANK(D5) is the logical_test argument that returns FALSE which triggers the function to return Not Blank (value_if_false argument) otherwise it returns Blank (value_if_true argument).
- Output → Not Blank
Lastly, the picture below depicts the cells with the “Blank” text.
2.2 Leaving Blank If Cell Is Blank
By the same token, we can leave a cell empty instead of returning a text string.
📌 Steps:
- To begin with, jump to the D5 cell >> copy and paste the equation below >> hit ENTER.
=IF(ISBLANK(C5), "", "Complete")
In this case, the C5 cell represents the “Date” “2-Jan”, since there is a date present, so the function outputs “Complete”, else it shows an empty cell.
Finally, the cells containing the blanks are marked in the screenshot below.
3. Combining IF, ISBLANK, and VLOOKUP Functions
Alternatively, we can apply Excel’s IF, ISBLANK, and VLOOKUP functions to look up the blanks present in the dataset.
📌 Steps:
- At the very beginning, proceed to the C18 cell >> enter the expression into the Formula Bar >> press the ENTER key.
=IF(ISBLANK(VLOOKUP(B18,$B$5:$D$15,3,FALSE)),"Enter Horsepower",VLOOKUP(B18,$B$5:$D$15,3,FALSE))
- VLOOKUP(B18,$B$5:$D$15,3,FALSE) → looks for a value in the left-most column of a table, and then returns a value in the same row from a column you specify. Here, B18 ( lookup_value argument) is mapped from the B5:D15 (table_array argument) array. Next, 3 (col_index_num argument) represents the column number of the lookup value. Lastly, FALSE (range_lookup argument) refers to the Exact match of the lookup value.
- Output → 315
- ISBLANK(VLOOKUP(B18,$B$5:$D$15,3,FALSE)) → checks whether a reference is to an empty cell, and returns TRUE or FALSE.
- Output → FALSE
- IF(ISBLANK(VLOOKUP(B18,$B$5:$D$15,3,FALSE)),”Enter Horsepower”,VLOOKUP(B18,$B$5:$D$15,3,FALSE)) → becomes
- IF(FALSE,”Enter Horsepower”,315) → Here, FALSE is the logical_test argument because of which the IF function returns the value of 315 which is the value_if_false argument. Otherwise, it would return “Enter Horsepower” which is the value_if_true argument.
- Output → 315
📃 Note: Please make sure to use Absolute Cell Reference by pressing the F4 key on your keyboard.
Eventually, the cells which don’t have the “Horsepower” value are shown in the figure below.
4. Returning 0 If Cell Is Blank
Moreover, we can choose to return a 0 instead of a blank using the IF function; It’s simple and easy, so just follow along.
📌 Steps:
- To start with, choose the D5 cell >> type the following formula >> click ENTER.
=IF(ISBLANK(C5),0,C5)
In this situation, the C5 cell indicates the “Inventory” value of “107”.
Subsequently, the cells containing the value 0 are marked as shown below.
5. Applying Conditional Formatting with ISBLANK Function
For one thing, Excel’s Conditional Formatting feature can highlight blank cells within the selected range.
📌 Steps:
- First, select the D5:D15 cells >> navigate to the Conditional Formatting drop-down >> select New Rule.
In an instant, the New Formatting Rule wizard appears.
- Second, choose the Use a formula to determine which cells to format option.
- Then, in the Rule Description enter the following formula.
=ISBLANK(D5:D15)
- Now, click on the Format box to specify the cell color.
At this time, this opens the Format Cells wizard.
- Third, click the Fill tab >> choose a color of your liking, for example, we’ve chosen “Orange Accent 2” fill color >> hit the OK button.
- Consequently, the results should appear in the picture below.
Read More: How to Use ISBLANK Function for Conditional Formatting in Excel
How to Sum Values If Cell Is Not Blank in Excel
Last but not least, we can also employ the SUMIF function to get the sum of the values based on the not-blank condition.
📌 Steps:
- At the start, copy and paste the following equation into the C14 cell >> hit the ENTER key.
=SUMIF(D5:D12, "<>", D5:D12)
- SUMIF(D5:D12, “<>”, D5:D12) → adds the cells specified by a given criteria or condition. Here, D5:D12 is the range argument that refers to the “Sales” column. Then, “<>” represents the criteria argument that represents the not-blank cells in the given range. Lastly, D5:D12 is the optional sum_range argument which indicates the values to sum within the range.
- Output → $23,447
📃 Note: You can open the Format Cells dialog box by pressing CTRL + 1 and change the cell formatting to currency.
Practice Section
We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.
Download Practice Workbook
Conclusion
To sum up, we hope this tutorial has provided you with helpful knowledge on how to use ISBLANK function to check if cell is blank in Excel. Now, we recommend you apply all this know-how in the practice dataset by downloading the practice workbook. In addition, feel free to comment and provide your valuable feedback.
