How to Determine and Count If Cell Is Not Blank in Excel

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.

Determine If the Cell is not Blank


Approach

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.

Applying ISBLANK Function to Determine If the Cell is not Blank

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

Applying ISBLANK Function to Determine If the Cell is not Blank

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

Applying ISBLANK Function to Determine If the Cell is not Blank

  • Drag this sign at the end of your column, and all your cells will be auto-filled.

Applying ISBLANK Function to Determine If the Cell is not Blank

📕 Read More: 4 Easy Ways to Fill Blank Cells in Excel with Formula


Approach

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

Combining IF and ISBLANK Functions to to Determine If the Cell is not Blank

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

Combining IF and ISBLANK Functions

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

 Determine If the Cell is not Blank Combining IF and ISBLANK Functions


Approach

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

 Determine If the Cell is not Blank Inserting IF Function

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

 Determine If the Cell is not Blank Inserting IF Function

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


Approach

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

Utilizing the Combination of IF, NOT, and ISBLANK Functions

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

Utilizing the Combination of IF, NOT, and ISBLANK Functions to Determine If the Cell is not Blank

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


Approach

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)

Integrating IF and LEN Functions

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

Integrating IF and LEN Functions to Determine If the Cell is not 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.

 Count If a Cell Is Not Blank in Excel

Approach

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.

Applying COUNTIF Function to Count If a Cell Is Not Blank in Excel

  • After that, Enter the formula, and you will be able to see the number of non-blank cells.

Applying COUNTIF Function


Approach

 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.

Count If a Cell Is Not Blank in Excel Applying COUNTA Function


Approach

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.

Utilizing COUNTIFS Function to Count If a Cell Is Not Blank in Excel


📝 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

(Visited 137 times, 1 visits today)
Lamisa Musharrat

Lamisa Musharrat

Hey there! I am Lamisa Musharrat, a Marine Engineer and a Content Developer. I love to help people by writing articles for them. Hope you will enjoy my blog.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo