How to Find If Cell is Blank in Excel (7 Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.

1. Using the ISBLANK Function to Find If Cell is Blank in Excel

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.
=ISBLANK(B5)

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

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.


2. Applying 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.
=IF(B5="","Blank","Not Blank")

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

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. Using Excel Conditional Formatting to Highlight If Cell is Blank 

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

Highlight If Cell is Blank Using Conditional Formatting

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

Highlight If Cell is Blank Using Conditional Formatting

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

Highlight If Cell is Blank Using Conditional Formatting

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

Highlight If Cell is Blank Using Conditional Formatting

  • Click on the Format button.

Highlight If Cell is Blank Using Conditional Formatting

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

Highlight If Cell is Blank Using Conditional Formatting

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

Read More: How to Highlight Blank Cells in Excel


4. Combining IF and ISBLANK to Find If Cell is Blank 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.
=IF(ISBLANK(B5),"Blank","Not Blank")

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

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


5. Applying the 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.
=IF(NOT(ISBLANK(D5)),"Delivered","")

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

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. Checking If Any Cell in a Range is Blank with 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.
=IF(COUNTBLANK(C5:D5)>0,"Not Completed","")

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

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. Using SUMPRODUCT and IF Functions to Check If All Cell is Blank in Excel

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.
=IF(SUMPRODUCT(--(C5:D5<>""))>0,"","Not Started")

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

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.


Download the Practice Workbook

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


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

Hosne Ara
Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo