Lamisa Musharrat
Counting blank cells manually can be easy when dealing with a small amount of data, but what about when you have a massive amount of data to handle? In that case, you can use the built-in functions of excel to determine whether a cell is blank and count the empty cells. So, this article will discuss how to determine and count if a cell is not blank in Excel.
📁 Download Excel File
Download the Excel file we used to create this article so you can practice.
Learn to Determine If Cell Is Not Blank with These 5 Suitable Approaches
Here you will find five suitable approaches to determine if the cell is not blank. The dataset we have used to write this article is about the bookstore stock; you can see the number and quantity of books given, and we are going to see which book has stock and which doesn’t.
1. Applying ISBLANK Function
The syntax of the ISBLANK function of Excel is ISBLANK(value); here, in the value portion, you can input any value of the cell and see if that cell is blank. Let’s see how to do this with some simple steps. Suppose we want to see if a book is out of stock.
⬇️⬇️ STEPS ⬇️⬇️
- First, select the cell where you want to see your results. In this case, we have set cell D6.
- After that, enter the function. Here, as we have to know if the product is in stock depending on its quantity, we chose the amount for the value part. For the first book, the value is stored in cell C6.
Here is the formula that we have used here.
=ISBLANK(C6)
- Then, after entering the above formula, it will return TRUE if the cell is blank or FALSE based if it is not.
- But your job is not done yet! You must use the Fill Handle icon if you want to apply this to an entire column. Just put your cursor on the right side of the box, and you will see a (+)sign known as the Fill Handle icon.
- Drag this sign at the end of your column, and all your cells will be auto-filled.
📕 Read More: 4 Easy Ways to Fill Blank Cells in Excel with Formula
2. Combining IF and ISBLANK Functions
You can also use the IF function and ISBLANK function together and insert the language you want to display if the cell is blank. We want to see the output in terms of stockout or In-stock. Check the steps below.
⬇️⬇️ STEPS ⬇️⬇️
- First, select the cell where you want to see the result. In this case, we have set cell D6.
- Afterward, enter the formula. The formula is given below.
=IF(ISBLANK(C6),”Stockout”,”In Stock”)
🔨 Formula Breakdown
IF(ISBLANK(C6),”Stockout”,”In Stock”)
👉 You can see that, like the ISBLANK function in the previous procedure, we used the C6 cell to know if it is blank.
👉 Additionally, this function includes the message Stockout or In Stock.
So, if the ISBLANK function returns the value TRUE, it will show you Stockout; or False, it will show In stock.
- Again, you have done this only for one cell. Now you have to fill the other cells in the same column using the Fill Handle icon.
- Then, drag the Fill Handle icon at the end of your column, and all your cells will be auto-filled.
3. Using IF Function
You can also try using the IF function only. Follow the steps below.
⬇️⬇️ STEPS ⬇️⬇️
- First, select the cell where you want to see the result. In this case, we have set cell D6.
- After that, enter the formula using the IF. The formula that we have used is given below.
=IF(C6<>””,”In stock”,”Stockout”)
🔨 Formula Breakdown
IF(C6<>””,”In stock”,”Stockout”)
👉 Here, C6 is the cell to see if it is blank. The operator <>, known as not equal to, is used here, so if the C6 cell is not equal to “” the IF function will return the value TRUE, and in the cell, it is displayed as In stock.
👉 Otherwise, if this C6 cell is equal to “”, which indicates a blank cell, it will display the value Stockout.
- Then, after pressing the Enter key, you can see if the cell is blank.
- But if you want to run this formula for the entire column, use the Fill Handle icon.
- Finally, drag the Fill Handle icon at the end of your column, and all your cells will be auto-filled.
4. Utilizing Combination of IF, NOT, and ISBLANK Functions
A combination of IF, NOT, and ISBLANK functions can also determine if a cell is not blank in Excel. Follow these simple steps.
⬇️⬇️ STEPS ⬇️⬇️
- First, select the cell where you want to see the result. In this case, we have set cell D6.
- Then, write the following formula using IF, NOT, and ISBLANK functions.
=IF(NOT(ISBLANK(C6)),”In stock”,”Stockout”)
🔨 Formula Breakdown
IF(NOT(ISBLANK(C6)),”In stock”,”Stockout”)
👉 ISBLANK(C6) checks if cell C6 is blank.If it is blank, then it returns the value TRUE. Then this function becomes IF(NOT(TRUE), “In stock”,”Stockout”).
👉 If this is NOT TRUE, that means the cell is not blank, then the IF function displays the value In Stock; otherwise, it will show Stockout.
- Then, as you have done this only for one cell, now you have to fill the other cells in the same column using the Fill Handle icon.
- Drag the Fill Handle icon at the end of your column, and all your cells will be auto-filled.
5. Integrating IF and LEN Functions
The LEN function gets the number of characters in a string. Combining it with the IF function can determine a blank cell. Follow the simple steps below to learn how to do this.
⬇️⬇️ STEPS ⬇️⬇️
- First, you must select the cell where you want to see the result. In this case, we have set cell D6.
- Then insert the formula below.
=IF(LEN(C6)>0,TRUE)
🔨 Formula Breakdown
IF(LEN(C6)>0,TRUE)
👉 In this formula, LEN(C6) counts the characters in cell C6. IF(LEN(C6)>0, TRUE) means if there is even only one character in the cell C6, LEN(C6)>0 is TRUE. It will return the value TRUE.
- Then, press the Enter key to see if the cell is blank.
- After that, apply this formula to all the cells in that column. Use the Fill Handle; drag this Fill Handle icon to the end of the column.
- As a result, you will see this formula benign applied to all the cells automatically.
Learn to Count If Cell Is Not Blank with These 3 Suitable Approaches
In the previous section, we discussed the approaches to determine if a cell is blank. However, this section will demonstrate how to count if the cell is complete with three examples. We have used the same dataset here, except this dataset doesn’t contain stock status.
1. Using COUNTIF Function
The COUNTIF function is used to count cells. Here we have used this function to count the number of non-blank cells of the dataset. Go through the steps below to learn more.
⬇️⬇️ STEPS ⬇️⬇️
- First, you must select the cell where you want to see the count. In this case, we have set cell C13.
- Then, insert the following formula of the COUNTIF.
=COUNTIF(C6:C11,”<>”)
🔨 Formula Breakdown
COUNTIF(C6:C11,”<>”)
👉 In this formula, C6:C11 is the range of columns that we want to count for.
“<>”, which means no equal to blank, indicates that this function will count only those not blank cells.
- After that, Enter the formula, and you will be able to see the number of non-blank cells.
2. Applying COUNTA Function
The COUNTA function count cell. It counts all the cells containing error values or information but doesn’t count empty cells. Follow the steps below to learn how to employ this function in this case.
⬇️⬇️ STEPS ⬇️⬇️
- First, you must select the cell where you want to see the count of the non-blank cells. In this case, we have set cell C13.
- Afterward, type a formula in your selected cell.
The formula we have used is the following.
=COUNTA(C6:C11)
🔨 Formula Breakdown
COUNTA(C6:C11)
👉 As you can see, this COUNTA function counts within a range, and we have selected the range C6:C11. As we have mentioned earlier, this function doesn’t count the empty cells; it will only show the count of non-blank cells.
- After that, Enter the formula, and you will be able to see the number of non-blank cells.
3. Utilizing COUNTIFS Function
The COUNTIFS function counts cells for multiple ranges and multiple criteria.
Let’s see how we can use this.
⬇️⬇️ STEPS ⬇️⬇️
- First, you must select the cell where you want to see the count of the non-blank cells. In this case, we have set cell C13.
- Afterward, type a formula in your selected cell.
So, the formula we have used is the following.
=COUNTIFS(C6:C11,”>0″,C6:C11,”<>”)
🔨 Formula Breakdown
COUNTIFS(C6:C11,”>0″,C6:C11,”<>”)
👉 Here, the COUNTIFS function first accounts for the range of the column to count, and the criteria are set by “>0”.
👉 So, C6:C11,”>0″ counts all cells with a value greater than 0.
👉 Again, for the second range of cells C6:C11, same as the first, count for the empty, not empty cells by “<>.” Then finally, it shows the result in your selected cell.
- After that, Enter the formula, and you will be able to see the number of non-blank cells.
📝 Takeaway from This Article
📌 We have learned how to count the number of non-empty cells using built-in functions.
📌 We understand how to determine which cells are blank.
📌 Use of various functions for the same task may now be clear.
Conclusion
To conclude, don’t make it tedious to count data from a vast dataset. Instead, use these formulas and do it more time-efficiently. Please leave a comment if you have any suggestions or questions. Also, don’t forget to visit our Excelden page to enhance your Excel-related knowledge.
Related Articles
- 3 Approaches to Make Empty Cells Blank in Excel
- How to Get Blank Cell Instead of Zero Using Formula in Excel
- 4 Quick Tricks to Fill Blank Cells in Excel with N/A
- How to Fill Blank Cells in Excel with Go To Special (4 Easy Ways)
- 3 Ways to Fill Blank Cells with Text in Excel
- 4 Quick Ways to Insert 0 If Cell Is Blank in Excel
