Filtering Data

You may not always want to see all the data in a table. Perhaps you want to see only rows with a specific value in column A, or a higher-than-some-threshold value in column B, or a checkmark in column F. Numbers provides quick filtering for rows through a table’s Column menus, which I describe in Quick Filters. More sophisticated options, such as multiple criteria for filters, are described in Filtering with the Inspector.

Because this chapter is divided between the use of Filter menus and that of the Sort & Filter Inspector for filtering, I’ll mention here two quick items that are common to filtered tables in general:

• You can’t add rows to a filtered table; the Add commands are dimmed.
• Data in hidden rows—those that disappear when a table is filtered—are not included in charts unless you check Hidden Data in the Chart Option section of the Format Inspector’s Chart pane.

Note: Here’s an important concept to keep in mind: you don’t filter out a value, you filter for a value. So, you define filters by what you want to see, not what you want hidden.

Quick Filters

When you want a quick look at a subset of the data in your rows, a “quick filter” is your best friend. You can apply a quick filter instantly by selecting from a Column menu, specifying which values you want to remain visible.

Creating Quick Filters

Try it: Use the 17·Quick Filter sheet in The Hands-on Example Spreadsheet to try the procedures described here.

Make it: Create a three-column table with the numbers shown in the next figure in columns B and C. Include at least one footer row with sums or averages so you can see how hiding rows affects the calculations.

You can do quick filtering whether the table is active or selected:

• Specify a filter value: Click a column label, and choose from the Filter Table submenu, which lists all the unique values in a column. Choose 2 from the column B (Jack) submenu, and two body rows remain displayed, since only two rows have a 2 in that column. The sums and averages remain related to all the cells referenced in their formulas, no matter how many of them are showing (Figure 83).

Tip: To view a sum, average or other common statistic for only the filtered info, drag across the displayed body cells, and then check the tokens in the Quick Calc bar.

Figure 83: Left: The full table. Center: The Filter Table submenu. Right: The filtered table shows only the 2’s in the first column, but the sum and average footer cells don’t change.

• Add a second filter to a column: Select another item from the same submenu. In your example table, choose 3; now there are four rows displayed, because there are two 2’s and two 3’s in Jack’s column.
• Filter a second column: Choose from that column’s Filter Table submenu. In your example table, choose 6 from the column C (Jill) Filter Table submenu. Only one row is displayed now, since it’s the only one that gets through both filters: the value in Jack’s column must be 2 or 3, and Jill’s column must contain a 6 (Figure 84).

  

  Figure 84: Left: With 2 and 3 filtered for Jack, four rows remain. Right: A second filter, for 6 in Jill, leaves only one row.

  It’s easier to predict how column filters will interact if you imagine one column winnowing the available rows, and the other then winnowing that group. This concept isn’t “directional”: Jack isn’t filtered first, and then Jill. It comes out the same if you think of Jill being filtered first and then the choices for Jack narrowing down from there.

Note: If you get unexpected results from a filter operation, you may be working with a table that had filters set up through the Sort & Filter Inspector, covered just ahead. There may be additional filter rules applied, or the default “Show all filters” may have been set to “Show any filter” instead (read All Filters vs. Any Filters).

• Remove a filter value: Choose a selected filter value from a Filter Table submenu to uncheck it.

  In the example file, uncheck 3 in Jack’s submenu. Oh, no! Oh, yes: all your rows disappear because no values can get through the current filter—a 2 in Jack and a 6 in Jill. In the example file, you’ll still see the header and footer rows when the body rows disappear. If you filter all rows from a table with no headers or footers, you’ll get a “No rows match the filter conditions” banner in place of the table. (Otherwise, there would be nothing on the sheet to indicate the filtered table’s existence.)

Tip: When you’ve set up multiple filters through one or more Filter Table submenus, you don’t have to (tediously) select one at a time to turn them all off. Because the menu selections are echoed in the Sort & Filter Inspector’s Filter pane, you can easily delete them there.

Quick Filter Quick Points

Here are some small but important points you should know before we move on to the slightly more complex procedure of creating filter rules:

• The Filter Table submenus don’t include any values from header or footer rows, which always remain visible.
• For a column with checkbox formatting, the Filter Table submenu lists TRUE (for checked boxes) and FALSE (for unchecked boxes).
• For a column formatted for star ratings, the Filter Table submenu shows numeric values from zero to 5.
• Footer rows that contain formulas that refer to column data show the results for all rows, including those hidden by the filter.

Because quick filters interact with settings in the Filter pane of the Sort & Filter Inspector:

• Selecting something in a Filter Table submenu creates a rule in the Inspector.
• If a Filter Table submenu includes the phrase “X other filters applied,” there are filter rules active for that column, applied through the Sort & Filter Inspector.
• If a Filter Table submenu is dimmed, it’s probably because the Filters checkbox in the Filter pane is unchecked.

Important: Because quick filters automatically create rules in the Inspector for the target columns, you can get some confusing clashes if, for instance, you’ve used the Inspector previously and your new Column-menu choices are added to existing filters.

Filtering with the Inspector

While Column menus offer a quick solution for simple filtering, it’s the Sort & Filter Inspector’s Filter pane that does the heavy lifting, letting you create multiple rules to filter a column.

A filter rule is a description like Score is greater than 30 or Name contains “Smith” or even Date is in the next 30 days. This flexibility alone puts filter rules in a different league from quick filters.

A filter is made up of one or more rules. (The fact that a single rule can define a filter muddies the water a bit when it comes to understanding the difference between the two, but you’ll quickly become comfortable with the concepts when you work with them.) You can filter multiple columns, resulting in quite sophisticated data selection for a table. When you want to display all your data again, you can turn your filters off (as a set), preserving the setup for reuse.

Filter Rule Benefits

Rules offer many advantages over quick filters. You can:

• Specify values for filtering that don’t yet exist in a column, since you aren’t limited by a submenu of existing values.
• Use multiple rules to create a complex filter for a column.
• Go beyond the simple “equals” criterion as a filter. Filter rules offer nearly 50 comparison operators that let you look for such things as:
  • Numbers that fall in or outside of a range, or are higher or lower than the average for the column.
  • Text that begins or ends with, or contains or doesn’t contain, certain characters.
  • Dates as they relate to the current date (today, yesterday, tomorrow, last week, next month) or fall before, after, or between specified dates.
• Specify if all filters must be met, or if matching any single one is good enough. So, while one of the multiple-column quick filters described previously results in rows with a 2 or 3 in Jack’s column and a 6 in Jill’s, you can easily set up a more advanced filter that looks for rows that have a 2 or 3 for Jack or a 6 for Jill.
• Easily change a single argument of a complex filter. So, if you’ve filtered for the highest and lowest scores for your Blue team, you need only change the rule that filters for Blue to filter for Red instead, and you’ll get the highest and lowest scores for Red.
• Turn off a filter without losing its definition, so you can turn it back on again to view filtered data without redefining its parameters.

Create a Filter

Try it: This section uses the 18·Filter Rules sheet in The Hands-on Example Spreadsheet.

Make it: To follow the steps in your own table, include, at minimum, two columns with the headers Team and Score. In the Team column, randomly assign the names Red, Blue, and Green; put numbers from 15 to 45 in the Score column, making sure you have several numbers under 20 and some over 35.

Add a Filter

Start with a filter that finds the higher scores among the data:

1. Select or activate the table, click the Sort & Filter button, and click Filter to open the Filter pane.
2. Click Add a Filter and choose a column name from its menu. (If there are no column headers, the menu lists Column A, Column B, and so on.) Choose Score for this example.

   Numbers adds a Filters section to the pane; its checkbox is currently unchecked, since there’s as yet no filter applied to the table. A segment for the column you’re filtering—Score, in this example—is also added, and a popover opens from the filter’s Add a Rule button.

3. Choose a category in the popover and then select a specific rule: click Numbers at the top of the popover and then click Greater Than or Equal To.
4. Enter additional information to complete the rule: type 35 in the field, and press Return to enter it.

Numbers selects the Filters checkbox and applies the filter; the table displays rows with scores of 35 and over (Figure 85).

Figure 85: Left to right: The Add a Filter menu listing all the column headers, the Add a Rule popover, and the rule for the Score filter.

To turn off all filters for a table but keep the rules for quick re-use, uncheck the Filters checkbox (shown in the figure above, top right).

Note: When a table is filtered, you can’t add a new row.

Tip: When you create filters for Checkboxes, use Text Is or Text Is Not with true or false. Treat Star Ratings as numbers, since they represent zero through five.

Add a Second Rule to a Filter

You can create more than one rule to define a column’s filter, so you can filter for more than one thing in a column: grades A, A- or A+, for instance, or, in our example, the outlying scores of 35 and over or 25 and under).

With the table selected and the Filter pane showing, add a second rule:

1. Click the Or button beneath the existing rule.
2. Define the second rule: choose Less Than or Equal To from the popover, type 25 in the field, and press Return.

The rows that are left show only the high and low scores, because, as the Filter pane pretty much spells out in its menus and fields, this filter is: Match any of the following rules: the Score is greater than or equal to 35; the Score is less than or equal to 25.

Note: There’s a not between option that lets you, in effect, define “over 35 or under 25” in a single rule—but I wanted you to see how to add a rule to an existing filter, as well as delete one (next).

Delete and Edit Rules

You can modify a filter by deleting or editing any of its rules in the Sort & Filter Inspector’s Filter pane.

To delete a rule, hover your pointer toward the top of the rule in the Filter pane and then click the trash button that appears. To edit a rule, click any of its criteria and make the change—greater than to greater than or equal to, for instance, or the number that you input for that threshold.

If you worked in your sample table following the instructions starting at Add a Filter, you have two rules that together find outlying scores that are 35 and over or 25 and under. To change the current filter to find just the above-average scores:

1. Delete the second rule (Less Than or Equal To 25) by clicking its trash button (Figure 86).

   

   Figure 86: Deleting a rule.

2. Click the Greater Than or Equal To button in the rule that’s left, and select Above Average from the popover’s Number list to replace it; note its subtitle is “in the column,” just to keep you oriented.

Add a Second Filter

First let me reiterate the terminology: a rule is a description of what you want to remain in the column—numbers higher than 10, or cells that contain the word red, and so on. A filter is one or more rules that define what will show in a specific column. So, there’s only one filter for any column, but the filter may have multiple rules.

You can apply multiple filters to a table by creating them for more than one column. As a result, you can find, for instance, all the members of the Blue team (that’s one column) who had above-average scores (that’s another column).

Try it: Continue using the 18·Filter Rules sheet in The Hands-on Example Spreadsheet. These directions assume that you’ve applied the changes described above, deleting one rule and editing the remaining one for the Score column, but these directions work just as well if you still have the original two rules for the Score filter.

Make it: Continue using the table you’ve worked with so far, initially described under Create a Filter. You can use your table whether or not you made the most recent changes to the Score column filter.

To add a second filter to a table, work in the Filter pane:

1. Click the Add a Filter button, and choose a column from the menu; in your example file, choose Team.

   Numbers adds another rule segment at the bottom of the Filter pane and opens a popover from that rule’s Add a Rule button.

2. Pick a rule from the popover: click the popover’s Text button, and choose is from the list. This puts text is in the Team filter segment.
3. Provide details for the rule: type blue in the field beneath text is (Figure 87), and press Return to enter it.

Figure 87: These two filters work together to find the above-average scores for Blue team members.

Tip: You don’t need to match upper- and lowercase when you specify a text value in a filter—the text is case insensitive.

All Filters vs. Any Filters

Throughout this chapter, we’ve stuck with the Filter Pane’s default setting of All Filters (it’s in the pop-up menu beneath the Show Rows that Match label), but its alternative, Any Filter, is an extremely useful option.

With All Filters chosen, the filters you’ve defined for your columns are all applied. Say you have a list of student grades, with a column each for the midterm and final exams. If you set up a filter for an A in the Midterm column, and another for an A in the Final column, the All Filters setting means you’ll find only those students who earned A’s in both the midterm and the final. Choose Any Filters instead, and you’ll get a list of the students who scored an A in either the midterm or the final. (That would also include, of course, those who earned A’s in both.)