We now discuss ways of filtering data sets—that is, finding records that match particular criteria. Before getting into details, there are two aspects of filtering you should be aware of. First, this section is concerned with the types of filters called AutoFilter in previous versions of Excel (2003 and earlier). The term AutoFilter implied that these were very simple filters, easily learned in a few minutes. If you wanted to do any complex filtering, you had to move beyond AutoFilter to Excel’s Advanced Filter tool. Excel 2007 still has Advanced Filter. However, the term AutoFilter has been changed to Filter to indicate that these “easy” filters are now more powerful than the old AutoFilter. Fortunately, they are just as easy as AutoFilter.
Second, one way to filter is to create an Excel table, as indicated in the previous subsection. This automatically provides the dropdown arrows next to the field names that allow you to filter. Indeed, this is the way we will filter in this section: on an existing table. However, a designated table is not required for filtering. You can filter on any rectangular data set with variable names. There are actually three ways to do so. For each method, the active cell should be a cell inside the data set.
(1) Use the Filter button from the Sort & Filter dropdown list on the Home ribbon. (2) Use the Filter button from the Sort & Filter group on the Data ribbon.
(3) Right-click on any cell in the table and choose the Filter option. You get several
options, the most popular of which is Filter by Selected Cell’s Value. For example, if the selected cell has value 1 and is in the Children column, then only customers with a single child will remain visible. (This behavior should be familiar to Access users.) The point is that Microsoft realizes how important filtering is to Excel users. Therefore, they have made filtering a very prominent and powerful tool in Excel 2007.
Filtering is certainly possible without using Excel tables, but there are definitely advan- tages to filtering with Excel tables.
As far as we can tell, the two main advantages of filtering on a table, as opposed to the three options just listed, are the nice formatting (banded rows, for example) provided by tables, and, more importantly, the totals row. If this totals row is showing, it summarizes
only the visible records; the hidden rows are ignored.
We now continue Example 2.7 to illustrate a number of filtering possibilities. Unlike some “how to” Excel books, we won’t lead you through a lot of descriptions and screen- shots. Once you know the possibilities that are available, you should find them extremely easy to use.
2.7 Excel Tables for Filtering, Sorting, and Summarizing 71
E X A M P L E
E
XAMPLE2.7 H
YT
EX’
SC
USTOMERD
ATA(C
ONTINUED)
T
he HyTex company wants to analyze its customer data by applying one or more filters to the data. It has already designated the data set as an Excel table. What types of filters might be useful?Objective To investigate the types of filters that might be applied to the HyTex data.
Solution
There is almost no limit to the filters you can apply, but here are a few possibilities.
■ Filter on one or more values in a field. Click on the Catalogs dropdown arrow.
You will see five checkboxes, all checked: Select All, 6, 12, 18, and 24. To select one or more values, uncheck Select All and then check any values you want to filter on, such as 6 and 24. In this case, only customers who received 6 or 24 catalogs will remain visible. (In Excel 2003 and earlier, it wasn’t possible to select more than one value this way. Now it’s easy.)
■ Filter on more than one field. With the Catalogs filter still in place, create a filter
on some other field, such as customers with one child. When there are filters on multiple fields, only records that meet all of the criteria are visible, in this case customers with one child who received 6 or 24 catalogs.
■ Filter on a continuous numerical field. The Salary and AmountSpent fields are
basically continuous fields, so it would not make much sense to filter on one or a few particular values. However, it does make sense to filter on ranges of values, such as all salaries greater than $75,000. This is easy. Click on the dropdown arrow next to Salary and select Number Filters. You will see a number of obvious possibilities, including Greater Than.
■ Top 10 and Above/Below Average filters. Continuing the previous bullet, the
Number Filters include Top 10, Above Average, and Below Average options. These are particularly useful if you like to see the highs and the lows. The Above Average and Below Average filters do exactly what their names imply. The Top 10 filter is actually more flexible than its name implies. It can be used to select the top n items (where you can choose n), the bottom n items, the top n percent of items, or the bottom n percent of items. Note that if a Top 10 filter is used on a text field, the ordering is alphabetical. If it is used on a date field, the ordering is chronological.
■ Filter on a text field. If you click on the dropdown arrow for a text field such as
Region, you can choose one or more of its values, such as East and South, to filter on. You can also select the Text Filters item, which provides a number of choices, including Begins With, Ends With, Contains, and others. For example, if there were
The number of ways you can filter with Excel’s newest tools is virtually unlimited. Don’t be afraid to experiment.You can always clear filters to get back to where you started.
an Address field, you could use the Begins With option to find all addresses that begin with P.O. Box.
■ Filter on a date field. Excel 2007 has great built-in intelligence for filtering on
dates. If you click on the FirstPurchase arrow, you will see an item for each year in the data set with plus signs next to them. By clicking on the plus signs, you can drill down to months and then days for as much control as you need. Figure 2.40 shows one possibility, where we have filtered out all dates except the last part of July 2007. In addition, if you click on the Date Filters item, you get a number of possibilities, such as Yesterday, Next Week, Last Month, and many others. There aren’t many possibilities regarding dates that Microsoft hasn’t included.
Figure 2.40 Filtering on a Date Variable
■ Filter on color or icon. Excel 2007 has many ways to color cells or put icons in cells.
Often the purpose is to denote the sizes of the numbers in the cells, such as red for small numbers and green for large numbers. We won’t cover the possibilities in this book, but you can experiment with Conditional Formatting on the Home ribbon. The point is that cells are often colored in certain ways or contain certain icons. Therefore, Excel 2007 allows you to filter on background color, font color, or icon. For example, if certain salaries are colored yellow, you can isolate them by filtering on yellow. We are not sure how often this feature will be used, but it is available and easy to use.
■ Use a custom filter. If nothing else works, you can try a custom filter, available at
the bottom of the Number Filters, Text Filters, and Date Filters lists. Figures 2.41 and 2.42 illustrate two possibilities. The first of these filters out all salaries between $25,000 and $75,000. Without a custom filter, this wouldn’t be possible. The second uses the * wildcard to find regions ending in est (West and Midwest). Admittedly, this is an awkward way to perform this filter, but it indicates how flexible custom filters can be.
2.7 Excel Tables for Filtering, Sorting, and Summarizing 73
Figure 2.41 Custom Filter for Salary
Figure 2.42 Custom Filter for Region
We remind you once again that if you filter on an Excel table and you have summary mea- sures in a total row at the bottom of the table, these summary measures are based only on the filtered data; they ignore the hidden rows.
One final comment about filters is that when you click on the dropdown arrow for any variable, you always get three items at the top for sorting, not filtering (see Figure 2.40, for example). These allow you to perform the obvious sorts, from high to low or vice versa, and they even allow you to sort on color. As with filtering, you do not need to designate an Excel table to perform sorting (the popular A-Z and Z-A buttons work just fine without tables), but sorting is made even easier with tables.
Now that you know the possibilities, here is one particular filter you can try. Suppose HyTex wants information about all middle-aged married customers with at least two children who have above average salaries, own their own home, and live in Indiana or Kentucky. We imagine that you can run this filter in a few seconds. The result, sorted in decreasing order of AmountSpent and shown in Figure 2.43, indicates that the
average salary for these 10 customers is $84,750, and their total amount spent at HyTex is $1,4709. (We summarized Salary by average and AmountSpent by sum in the totals row.) ■
Figure 2.43 Results from a Typical Filter
P R O B L E M S
Level A
33. The file P02_03.xlsxcontains data from a survey of