Using the ISBLANK and COUNTBLANK functions to check for and tabulate blank cells

- It's important, at times, to recognize when cells are blank. It's also important, at times, to be able to tabulate how many blank cells there might be. This is a small amount of data we're seeing on this worksheet, Columns A through G, only goes down to Row 16. But let's imagine a simple little test here to see if a cell is blank or not. Now, let's imagine that, maybe, we're going to have some additional bonuses, possibly, additional thoughts related to the benefits package that some people have here. Let's make a simple test to see if an entry in Column F is blank.

We can do this with a function called ISBLANK. Now, sometimes you'll use this with the IF function. You can use it by itself as well. *isblank, left parentheses. I'm clicking Cell F2, and we don't have to put in the right parentheses. I'll press Control+Enter here, and then simply drag it down the column. Now, first of all, that is not blank, therefore, we get the answer FALSE. If it were blank, we'd see TRUE. Drag this down the column for the remainder. And it certainly makes sense, there it's true.

In other words, is it blank? This is blank. Now, possibly, as I suggested, this could be inside of an IF function to test and see if it is blank, then do such and such or not do such and such. We can certainly look at it that way. There are times when it's important to actually count how many blanks there are, and so there's a function called COUNTBLANK. Now, if I click this for Column F that might seem the right approach. However, the answer we get here I think immediately raises a flag that says, "Well, I guess we're looking "through the entire worksheet." We are.

Many times using a Column Reference makes good sense, but in this case, not. Now, with certain kinds of math we could figure out how many entries within this range are blank, or how many of them have data, and do some subtraction, something along those lines, but if we select the actual cells in question, in other words F2 down through F16, we have an answer. The problem with that kind of a function is that, as the list grows, we'd have to keep redefining this limit here. So, a possible workaround is we could turn this data into a table.

But do recognize, that gives us the correct answer, too. In other words, these are truly blank. Let's look at a slightly different situation in Column G. I put in formulas here, I'll make the column wider momentarily. We're going to provide a service bonus for people with so many years of service, so there's an IF function in here. And look what it's doing. It's saying, in effect, that if the years of service are over 20, the bonus is 2000. If not, let's check to see if the years are 10 or more, it's going to be 1000, but if not, we'll simply put in NOTHING.

"" means put in NOTHING, put in a blank. And so we see blanks here. So, let's now use our ISBLANK, but, instead of Column F, let's use it right here on G2. Is that blank? No, it isn't, and that should say FALSE. Let's copy this down the column. I'll simply double-click. In other words, these are not recognized as blanks, even though we put in "", meaning NOTHING. So, the ISBLANK doesn't pick up on that.

Meanwhile, let's take our COUNTBLANK, which counts totals, and instead of it focusing on Column F, let's change those Fs to G. We're looking in Column G. Let's see what happens. And it's counting eight of them, so these two, this one, this one, this one. So on and so on. In other words, it's getting it correct. So, a little bit of discrepancy here about how COUNTBLANK and ISBLANK work when counting cells that have these kinds of entries in them. Now, another thought here is, if the original data had been a table, or if we make it a table now, so I'll get rid of Column H here, the data in Column H.

Let's approach this differently, and we can make Column G part of the table as well, too. So, simply clicking within the data here. If you're not familiar with converting data to a table, it's a feature introduced in Excel 2007. It's primarily visual, but we also get some content benefit out of it, as well. It eases sorting and filtering. It also means when we add adjacent data it's being treated as part of the table immediately. It makes handling large amounts of data more efficient. Also has a special feature, makes it easier to expand charts.

Also works smoothly with Pivot Tables. We can convert data to a table by pressing Control+T or Control+L, or from the HOME tab, we see Format as Table, or from the INSERT tab, so at least four different ways. Turn this into a table. Click OK. Now, when we have data in a table, remember earlier what we tried here, =countblank. If we were to click Column F we get the same answer.

But let's try it now with the table reference. Now, when you refer to tables, they have names. I didn't look to see what the name of the table is, but if I type T, the name will appear in there, Table 1. Had I created others, it might be a Table 2, Table 3, so sometimes there's some guesswork. I'm going to use Table 2, I'm going to use Table 1 here, and you can just tab it in, or type it. Now, you wouldn't know this, but in tables we can use brackets to pick up field names. There we are right there. And I'm concerned with Column F, that's the Benefits column.

I can tab that into place. Right bracket, and then Enter. So, that's counting the blanks within the Benefits column, but it's not looking at all of Column F. So, again, another advantage to using the table concept. Another way to use COUNTBLANK, situation like this. This represents some salespeople. Maybe they sell cars or real estate. In certain weeks they had no sales. Other weeks, better. We want to know how many of these are blank. Function's already here in cell S2.

COUNTBLANK, now this is done in a way that you wouldn't normally use. It's an Array formula, a more standard way, and I'll come back to that momentarily, but a more standard way would be to use COUNTBLANK. In other words, we're looking at all these cells here. How many blanks do we have? 23. The Array formula example here, a bit unusual, and I wouldn't say it's better, in fact, maybe it's more cumbersome, does allow us to use ISBLANK, but it uses it in a way that you might not be too familiar with.

What's great about Array functions, is they can deal with massive amounts of data as if we're treating each of them one by one. In other words, we're saying here, "We're going to check every cell in here, "and every time we find a blank, "we're going to hold on, or keep this value one, "and then we're going to add them all up." Now, Array formulas do things we can't typically do, and what is odd about them is, when you have completed typing them, you don't press Enter, you press Control+Shift+Enter. So, for some people you might not ever see those again.

For others, you might have more advanced knowledge of formulas and functions, but in this example here I think COUNTBLANK is better, and we see what it's doing. So, what COUNTBLANK is telling us here, is saying that throughout this time period here, and this covers a lot of different salespeople, covers a five-week period, so we're actually looking at 95 items of data there. On how many different sales-weeks, you might say, or salesperson weeks, were there no sales? In other words, there were 23 entries here that are blank.

So, in 23 different situations we had a salesperson who had an empty week. So, we've seen different examples of using ISBLANK and COUNTBLANK to tabulate the number of blank cells.