Find If Cell is Blank in Excel (7 Methods)

In this article, we will discuss if an Excel cell is blank or contains any data. Sometimes, while working with spreadsheets, we might need to find if a cell is empty or not. Besides, we can do several calculations depending on the status (blank or not) of the cell. So, let’s go through the methods.

Download the Practice Workbook

You can download the practice workbook that we have used to prepare this article.

7 Methods to Find If Cell is Blank in Excel

1. Use ISBLANK Function to Find Out If an Excel Cell is Blank

One of the easiest ways to find out the blank status is simply using the ISBLANK function. For example, we have a dataset containing several fruit names. But, some of the cells of the dataset are empty too. So, if we want to check the status of any cell, we have to follow the below steps.

Steps:

• First, type the below formula in Cell C5.

Here, the ISBLANK function checks whether a reference is to an empty cell and returns TRUE or FALSE.

• At last, the following is the output of the above formula. Use the Fill Handle (+) tool to copy the formula to the rest of the cells.

Read more: How to Return Value if Cell is Blank

2. Apply IF Function to Determine Whether a Cell is Empty or Not

Another easy and quick way to find blank cells is to use the IF function. We will put “Blank” or “Not Blank” as the value of the arguments of the IF function.

Steps:

• Initially, type the following formula in Cell C5.

Here, the IF function checks whether a condition is met, and returns one value if TRUE, and another value if FALSE.

• If the formula is entered correctly, it will return “Blank” for empty cells.

3. Highlight If Cell is Blank Using Conditional Formatting

If you have several blank cells in a  spreadsheet, Conditional Formatting is a very useful option to find and highlight those empty cells. Such as, in this method, you will highlight blank cells of a fruit name dataset.

Steps:

• First, select the entire dataset (B5:C13).

• Next, go to Home > Conditional Formatting (Styles group).

• Now, go to Highlight Cell Rules > More Rules.

• Then, the ‘New Formatting Rule’ window will show up. Here, the ‘Format only cells that contain’ option is selected by default. After that, choose the ‘Blanks’ option from the ‘Format only cells with’ drop-down.

• Click on the Format button.

• Go to the Fill tab and choose the highlight color and press OK.

• Again, press OK.
• Finally, all the blank cells are highlighted.

4. Use a Combination of IF and ISBLANK to Find Blank Cell in Excel

Unlike Method 1 and Method 2, now we will use a combination of functions to find empty cells. For instance, we will combine the IF and ISBLANK functions to determine the status of a cell.

Steps:

• First, type the following formula in Cell C5.

Here, the ISBLANK function checks the status of the cell, and the IF function returns value as “Blank” or “Not Blank” depending on the result of the ISBLANK formula.

• In the end, the following is our output.

Similar Readings:

• If Cell is Blank Then Show 0 in Excel (4 Ways)
• How to Delete Empty Cells in Excel (6 Methods)
• Remove Blank Lines in Excel (8 Easy Ways)
• Fill Blank Cells with Value Above in Excel (4 Methods)

5. Combination of NOT, IF, and ISBLANK Functions to Find If Excel Cell is Not Blank

Till now, in this article, we have discussed only how to recognize blank cells. However, depending on the status of the blank cell we can reach a conclusion. For example, we have a dataset containing fruit names, quantity, and delivery dates. Here, fruits that are delivered have the value in the ‘Delivery Date’ column. Now if we want to find out whether a particular fruit is ‘Delivered’ or not, we can use a combination of the NOT, IF, and ISBLANK functions.

Steps:

• Type the below formula in Cell E5 at first.

Here, the ISBLANK function checks the status of Cell D5 for blankness. Then, the NOT function checks the non-blankness of Cell D5 and returns TRUE. Finally, the IF function returns ‘Delivered’ if Cell D5 is not empty.

• After entering the formula, the following is our output.

Read more: How to Calculate in Excel If Cells are Not Blank

6. If Any Cell in Range is Blank(Applying COUNTBLANK and IF Functions)

In this method, we will look for empty cells in a data range, and depending on the count of the blank cells, will give a conclusive decision. For example, we have a dataset containing fruit names with two delivery dates. Now, if both the delivery dates for a particular fruit are mentioned, we can assume that all the orders for that fruit are completed. Similarly, if any one of the ‘Delivery Date’ columns is blank, it is clear that the order is ‘Not Completed’. In this method, we will use the combination of COUNTBANK and IF functions.

Steps:

• Initially, type the following formula in Cell E5.

Here, the COUNTBLANK function counts the number of empty cells in range C5:D5. And, the IF function returns ‘Not Completed’ if the result of the COUNTBLANK formula is greater than zero.

• Lastly, here is our output.

7. If All Cell is Blank in Excel (Using SUMPRODUCT and IF Functions)

Likewise the previous method, now we will use the SUMPRODUCT function along with the IF function to get the number of the blank cells and reach a conclusion. For example, in our current dataset, for any particular fruit, we will count for blank cells in both the delivery dates. And, if both the delivery dates are black we will return ‘Not Started’.

Steps:

• Type the below formula in Cell E5.

Here, the SUMPRODUCT function returns the sum of the products of the range (C5:D5). This time, we count cells that are not empty in the above range. If the result is greater than zero, we can understand that not every cell in the range is blank. If the result is zero the IF function returns ‘Not Started’.

• Finally, the following is our output. Use the Fill Handle (+) tool to copy the formula to the rest of the cells.

Conclusion

In the above article, I have tried to discuss the methods elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.

Further Readings

• How to Delete Blank Cells in Excel and Shift Data Up
• Null vs Blank in Excel
• How to Remove Blank Cells in Excel (10 Easy Ways)