Filter single- and multiple-column text - Microsoft Excel for Mac Tutorial

- [Instructor] When you work with large lists, there certainly are times when you're saying, I only want to see some of the list. For example, on this worksheet called Filter, which is in the chapter two file, we might want to view, for the moment, just the full-time people, or maybe just the people with a certain job rating, or maybe just the people who have a certain benefits package, all based on the data that we see in the various columns in this list. Now, before working with Filter, as with Sort, we want to make sure that the list that we're working with has no empty rows or empty columns. If we were to click within this list and start the filtering process, if there's an empty row that maybe we just hadn't checked out, maybe it's in row 25, Excel will only be looking at the data from rows one down to just where that break is in the data. And so, we wouldn't see any information about the data below that, so always check out the data ahead of time. If you work with the list frequently you don't have to do that very often, but a quick reminder, as with sorting, click within the data, press Command + A, that highlights all the contiguous data, then press Control + . five or six times, this will simply move the active cell around the corners of the range, if you're not sure at the bottom, scroll a bit more just to make sure, Control + . Control + . again, you need only to do that occasionally if you're very familiar with the list. Click within the list, alright, we've decided that for the moment we only want to see full-time people in the list, what do we do? On the Data tab in the ribbon, there's the Filter button. You can also, by the way, go to the Home tab, far right button, Sort and Filter, you could go there first then Filter, or a bit faster perhaps, Data, Filter. Excel checks out the data and puts filter arrows in the column headings that we see at the top. And even without any direction whatsoever, most people are quickly able to figure this out. If we want to see full-time people, where do we go, into the Status column, column F, here's the drop arrow. We see that all these are selected, let's unselect them all, and then simply check the box for Full Time, and we see only the full-time people. The lower left corner of the screen tells us there are 393 out of 741 records found. Notice that the row numbers are blue, and they're certainly not contiguous because we're not seeing all the rows. So what does filtering do, one way to describe it, it hides the rows you do not want to see, we are only viewing full-time people in this list. And, of course, as you would expect and hope, we're not in any way deleting that other data, we're simply hiding it. Now, you can filter on more than one column, and do notice that the icon next to status is an actual filter, in other words, it reminds us if we somehow would have forgotten, that this column is being used for filtering purposes. Now, we can also do this with additional columns as well. Let's say that we only want to view the full-time people who have a job rating of five. Out in column K we'll click the drop arrow there, and unselect all of the options, then select option five, so now, we've got filtering taking place in column K as well as in column F, and how many people in this list are Full Time and have a job rating of five, 102 people. Now the objective is not always, of course, to see a smaller list, but we could certainly take this a step further and in different ways say we only want to view people who work in the building called Main. So in column B we could click the drop arrow there and unselect all of them and choose Main. And now we're seeing only 30 records, you can close this dialog box any time you wish. So we've got filtering in three separate columns now, column B, and column F, and column K. And there will be times of course, when we want to bring back all the data, we can either click the filter button twice, it's like turning it off, turning it on again, or simply click the Clear button to the right. All of our data is back, the arrows are still in place, and yet, none of the arrows has the filter icon, therefore, we are seeing all the records. So it's relatively easy and straightforward to work with this feature called Filter. And we'll see in subsequent movies other ways to use this powerful tool.