Microsoft Office Tutorials and References
In Depth Information
The Advanced Filter—Setting Your Data Aside
And finally, if you want to turn the AutoFilter off altogether, just click the Filter button on the Sort &
Filter button group—and the handles disappear (but note : AutoFilter handles won’t print, even if you
leave them on screen).
The Advanced Filter—Setting Your Data Aside
The AutoFilter is a great and speedy way for sifting your data, but it suffers from a few shortcomings that
could impair your analysis of the information, depending on what you need to do. For one thing, the
AutoFilter is temporary; that is, because it works by hiding any rows that don’t meet the criterion you’ve
specified, you’re going to want to restore those rows to view sooner or later—and then you’re back
where you started; the filtered results are gone. In addition, those hidden rows may contain information
elsewhere in other columns, far from the data with which you’re working—but they’ll be hidden too.
Finally, the AutoFilter presents the results for all the database fields, even if you don’t need all of that
information. For example: I need to know the names of those students whose test averages equal or
exceed 85, and don’t need to see the data in their individual exam fields. That is, I may want the filter
result to display data for the Student and Average fields only . But AutoFilter isn’t selective—it shows
results for all the database fields, whether I want to see them or not. And here’s where the Advanced
Filter comes in: it lets you copy your filtered results to a separate area of the worksheet, giving you a new
set of results to work with, while leaving your original table data untouched and unhidden.
So let’s say you want to carry out the above task—you want to see which students have compiled a
test average of 85 or better, and you want to see those results displayed compactly, in the Student and
Average fields alone. Now we need to do a bit of preparation in order to get the Advanced Filter going.
First, we need to designate a small area on the worksheet (it could be anywhere, except inside the
database) in which we’ll enter the criterion, or criteria, we need to define which data we want to filter.
We’ll select cells N9:N10, and enter (Figure 6–26):
Figure 6–27. A criterion range for an advanced filter. “Average” in N9, >=85 in N10.
What is this pair of cells trying to do? It’s identifying
1. The name of the field bearing the criterion we need—Average-—in N9, and
2. The criterion itself—the 85-or-greater grade threshold—in N10.
Remember—we want to filter all students who’ve achieved a test average of 85 or better. The
averages are recorded in the Class Averages field, and so we’ve entered that name in N9. The >=85 in N10
is the standard way in which to express equal to, or greater than, a specified value—in this case, 85.
If, by way of a different example, we had wanted to filter all the students who had scored less than 65
in exam 4 , we could have entered this in N9:N10 instead (Figure 6–27):
 
Search JabSto ::




Custom Search