- Creating Lists
- Sorting Lists
- Working with Names
- Filtering Data with the AutoFilter
- Using the Advanced Filter
- Looking Ahead
This chapter is from the book
This chapter is from the book
This chapter is from the book
Filtering Data with the AutoFilter
When you're working with a large amount of data, you sometimes want to hide certain records so that you can focus on others. This is called filtering: You filter out the records that you want to ignore.
Excel's worksheets offer two approaches to filtering data: the AutoFilter and the Advanced Filter. (There's nothing really complicated about the Advanced Filter; it just takes an extra step to set up.) Both filters require that you arrange data in list form: different records in different rows, different variables in different columns, and variable names in the first row of each column.
NOTE
If you don't put variable names in the list's first row, Excel's filters ask you whether you want to treat the first row as names. Excel calls these column names. To avoid confusion with the A, B, C, D, ... at the top of worksheet columns, we call them variable names.
Fast Filtering: Using the AutoFilter's Dropdowns
The idea behind AutoFilter is to make it easy to focus on a subset of records in your list. AutoFilter does this by temporarily hiding the records that don't belong to the subset you're interested in (see Figure 3.15).
)
Figure
3.15 Notice that the records in the unfiltered list are not sorted, nor
need they be.
In Figure 3.15, the list in the range A1:C9 is unfiltered. That data is repeated in A15:C23, where the user has applied AutoFilter on the Branch variable so as to display only the Northwest branch's records. Notice that rows 17, 18, 20, and 21 are hidden: AutoFilter does this by setting their height to zero. And, although you can't tell in a black-and-white figure, the drop-down triangle in a field that has been used as a filter turns from black to blue.
TIP
You can easily activate a cell even if it's in a hidden row. Click in the Name box, type the cell's address, and press Enter. You'll see the cell's contents, if any, in the Formula Bar.
Suppose that you have a worksheet with the data shown in the range A1:C9 of Figure 3.15. To prepare to use AutoFilter, just do this:
Select any cell in the list.
Choose Data, Filter, AutoFilter.
This puts a series of dropdowns into the first row of your list. Each dropdown, when clicked, displays the unique values found in its column (see Figure 3.16).
)
Figure
3.16 Each unique value in a dropdown appears once only.
By clicking the Northwest value in column A's dropdown, you can filter the records in the entire list so that only the Northwest branch's records appear.
If your list has more than one column, you can use AutoFilter to select two or more values simultaneously, one from each column's dropdown (see Figure 3.17).
In Figure 3.17, the user has focused on both the Northwest branch and the Desktop product line. The two dropdowns act as though they were connected by an and: "If Branch is Northwest and Product is Desktop ...".
Bear in mind that the effect of AutoFilter is to hide the rows that don't meet the filtering criteria that you set by means of the dropdowns. In Figure 3.17, for example, you can no longer see rows 16 through 18 and 20 through 22. But the values in the hidden records are still there. Formulas that depend on values in the hidden records are unchanged by AutoFilter.
)
Figure
3.17 Only the sales of Desktops at the Northwest branch are shown.
You could get a similar effect by sorting the list on the Branch and Product columns. But you might find that approach to be less convenient if you then have to scroll down the worksheet to find the combination that you're interested in.
To reveal hidden records, but leave the dropdowns in place, choose Data, Filter, Show All. To remove the dropdowns, choose Data, Filter. You'll see a check mark by the AutoFilter menu item. Click AutoFilter again to remove the check mark from the menu and the dropdowns from the worksheet.
Using the AutoFilter with Other Criteria
Figure 3.16 shows that the AutoFilter has a Custom item in its dropdowns. Clicking it displays the window shown in Figure 3.18.
)
Figure
3.18 The AutoFilter enables you to specify two custom criteria for each
column in your list.
The Custom AutoFilter window has two dropdowns with operators on the left and two value dropdowns on the right. You choose an operator and a value for it to operate on. For example, if you decided to view records from the Northwest branch only, you could select equals from the left dropdown, and Northwest from the right dropdown.
There are 12 operators available:
Equals
Does not equal
Is greater than
Is greater than or equal to
Is less than
Is less than or equal to
Begins with
Does not begin with
Ends with
Does not end with
Contains
Does not contain
NOTE
The Custom AutoFilter also supports wildcards. As usual, a question mark represents any single character and an asterisk represents any string of characters.
Using the Custom item makes it easy for you to arrange more complex analyses. Suppose that you wanted to filter records by a region that you just made up: North, for example, or East. In the Custom AutoFilter window, you could choose equals as the operator, and in the value dropdown, you could enter North*. Using the asterisk after North matches both Northwest and Northeast. You can see the result in Figure 3.19.
Keep in mind that the one or two custom criteria you set using the Custom AutoFilter apply to one column only. If you establish a custom criterion for Branch, and another custom criterion for Product, they act just as if you had selected simple criteria from the AutoFilter dropdowns; that is, they select records as if they were joined by an and.
)
Figure
3.19 You could create an East region instead, by entering *east in the value
dropdown.