How to Filter Blank Cells in MS Excel

Next Video:
How to Count the Occurrences of a Number or Text in a Range in Excel....5

Filtering blank cells in MS Excel is a great way to make the best use of the space you have available to you. Filter blank cells in MS Excel with help from a mechanical engineer with 32 years of experience in a large aerospace company in this free video clip.

Part of the Video Series: Using Excel
Promoted By Zergnet


Video Transcript

Hi, I'm Ted. Today I'm going to show you how to filter blank cells in Excel. This is something that's useful to know how to do for instance if you have a spreadsheet with, where some of the entries are blank, and maybe you want to, you want to actually just look at the ones that are blank, or you just want to look at the ones that aren't blank. So I have a table set up here, and it's a series of names and for each name there's a hometown and a state. I just have the first names, if you're a baseball fan, you might recognize some of these names and places. I just, it actually is a real list. So what we're gonna do, the first thing we're gonna do is we're gonna, we're gonna go into the filter option, which depending on whether you have a Mac or a Windows, I think in both Mac and Windows it's under the data menu. And there's a couple of different ways to do it. It, depending on exactly how the spreadsheet is set up, but I find the most reliable way is to select all of the headers, all of the column headers, and then go up to the data menu, and click on filter, and that will enable, you notice that the, the column headers now have a little triangle, and that triangle is the filter that allows you to only select certain rows depending on which of the items you have selected. So let's just, let's find all of the entries where there is no hometown listed. So we're gonna go onto the filter button, over on the right of the hometown, and you'll notice that you have a list here, and it has all, it's gone through, Excel has gone through the whole list that has all of the different hometowns listed. If there are duplicates, it's only going to appear up here once here, and let's, the first thing we want to do is oops, I accidentally selected that. We're going to unclick select all, and that means everything is now unselected, so that if we were gonna filter, we would see nothing. Somewhere on this list there is always something called blanks. And so we're gonna on this little scroll bar on the right we're gonna scroll all the way to the bottom, and sure enough, there's something called blanks, and all that means is that it knows that there are some blank entries in the list, and we're gonna click on that, and then just move this box over to the right, and sure enough, only the entries that have a blank hometown are listed. Then scroll down and we can see a whole bunch more. Now for some of those, the state is, oops I'm scrolling around here. For some of those, the state is still shown, because the state is known, but the hometown isn't shown. So we're gonna do one more thing, we're gonna go in and we're going to, we're going to, we're going to go back into the hometown, select everything, so we're back and selecting everything, and we're going to select all of the states except for the ones that we're going to only select everything except the blanks. So we're gonna select all, and we're gonna scroll down here, and we're going to unclick the blanks. Then we're going to go into the hometown menu, and we're going to select just the blanks. So we're gonna go down here, and we'll come down here, and we're gonna select just the blanks, and what we have here is a list of all of the names on the list where the state is known but the hometown isn't. So, you may have a more complex data set. You can see that there's all kinds of things you can do, but basically the point is you can select just the blanks or everything that's not a blank. And of course you can use the filtering option to do all kinds of other things too for the values that aren't blank. So I hope this has been helpful. I'm Ted, and today I showed you how to filter the blank cells in Excel. Thank you for watching.


Related Searches

Is DIY in your DNA? Become part of our maker community.
Submit Your Work!