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.
- 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.
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.
- 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. 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.
- 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.
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.
- 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.
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.
- 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.
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.
- 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. 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’.
- 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.
Download the Practice Workbook
You can download the practice workbook that we have used to prepare this article.
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.
- How to Check If Cell Is Empty in Excel
- How to Find Blank Cells in Excel
- How to Delete Blank Cells and Shift Data Up in Excel
- How to Remove Blank Cells in Excel
- How to Delete Blank Cells and Shift Data Left in Excel
- Null vs Blank in Excel
- How to Highlight Blank Cells in Excel
- How to Find & Count If a Cell Is Not Blank
- How to Return Value if Cell is Blank
- How to Apply Conditional Formatting in Excel If Another Cell Is Blank
- How to Ignore Blank Cells in Range in Excel
- How to Set Cell to Blank in Formula in Excel
- If a Cell Is Blank then Copy Another Cell in Excel
- Formula to Return Blank Cell instead of Zero in Excel
- How to Remove Unused Cells in Excel
- How to Remove Blank Cells from a Range in Excel
- How to Make Empty Cells Blank in Excel
- How to Deal with Blank Cells That Are Not Really Blank in Excel
- Excel VBA: Check If Multiple Cells Are Empty
- How to Find Blank Cells Using VBA in Excel
- Return Non Blank Cells from a Range in Excel
- Excel If Two Cells Are Blank Then Return Value
- Data Clean-Up Techniques: Fill Blank Cells in Excel
- Excel VBA: Find the Next Empty Cell in Range
- Excel VBA: Delete Row If Cell Is Blank