Learn what what an Excel filter is. Also, learn how to apply a basic filter, and clear it.
- [Instructor] So I'm willing to bet you've heard the term filter a lot, but just what is a filter in Excel? A filter is a quick way to display only the data you care about at a particular moment. So for example in this data, at this very moment, I only care about Jan Kodis's sales. So I just need to click anywhere in the data and then to apply a filter for Jan, I'm going to go up here to the Data tab and I'm going to click Filter. And you'll see these filter buttons appear here. Now to apply that filter for Jan, I'm going to click the filter button for Sales Rep.
I'm going to deselect Select All, because I don't want to see everything and I'm just going to click the check box for Jan and then when I click OK or on the Mac when I click Jan's checkbox, I then only see Jan's rows. And that's a filter that's been applied. Let's take a look at some important signals here in Excel that appear when we filter things. First of all, when we have a filter applied to a column, you'll see a filter icon in that filter button, like this one here.
And then on the left in the row headings, you'll see that we don't have a full list of all the rows, we only have a partial set. That's just to signify that we only have a subset of the data, not everything. And then most importantly, in the lower left corner here, you'll see 56 of 390 records found. That's just giving us that total of rows for Jan. There's 56 rows for Jan in this dataset. Now let's apply another filter or Jan's sales, but this time we want to filter out everything except for the sales for Company H.
So we're going to go ahead and we're going to deselect Select All, and then we're going to click Company H and then OK. And now we have two filter applied, one for sales rep only showing Jan Kodis's and then in addition to Jan we've got her Company H sales filtered here. Now that we got what we wanted out of this little investigation into Jan's sales, we're now going to clear the filters to see all the data again. So we'll go up to the filter button and we'll click the Clear option here or on the Mac you'll click the Clear Filter button in the lower right.
We'll do that again, and there we've got all of the data back. So that's what a filter is and how to apply one. Again, filters are a way to temporarily see only the data you care about at a particular moment, because in another moment, you might care about someone else's sales, or a certain month's sales, and so forth and so on.
- Identify the purpose of a filter.
- Recognize situations in which a custom filter would be most effectively utilized.
- Summarize the method Excel uses to read data when an Advanced Filter is applied.
- Recall the approach to take when creating a specific item for a picklist.
- Explain the function of a dynamic array formula.
- Name the keyboard shortcut used to make the filter menus appear in Excel.