In this post we will see how to check if all the cells in a range have values in excel.
The range in excel could be of varied dimensions, and we will use formula to check about the existence of the values in a range.
When we say values, it could be anything like Text, Numeric, and Alphanumeric etc.
We will use COUTBLANK formula in excel to determine if all the cells in a range have values.
As you could see above we have arrangements of some data in a range and we want to check this range if all the cells in this are having values.
We will check both the scenario i.e. when the range has some blank values and when the range does not have any blank value.
Step 1
First We will see if all the cells in a range have values.
Enter the formula =NOT(NOT(COUNTBLANK(B4:E9))), in a cell as shown below.
B4:E9 is the range that we want to check.
COUNTBLANK will return us the number of cells that have blank values and NOT function is used just to get the result in TRUE/FALSE format here.
As you can see the cells in the range are completely filled and we do not have any blank values hence the formula has returned FALSE.
Step 2
Now we will delete some values in the range to check the formula.
As you could see the formula has returned TRUE that indicates that we have blank values in the range.
Additionally, if you want to calculate the number of blank values in a range, just remove the two NOT operator from the formula and you will get the count.
Hope this helped.
