Microsoft Office Tutorials and References
In Depth Information
Click the Copy to another location option, because that’s exactly what we want to
do—copy the filter results somewhere away from the original database. Clicking
Copy to another location activates the Copy to: field, below. Remaining with the
Filter the list, in-place option you see in Figure 6–30 would have the effect of
executing a standard AutoFilter— because that’s what AutoFilter does —filter data
in their own place, by hiding rows.
In criteria range, type or drag this range: N9:N10, the cells in which we entered our
In Copy to: type or drag this range: K9:L9. Even though we’re only naming the
headings in the copy-to location, Excel will still copy all the records that meet our
criterion below those headings.
Click OK. You should see: (Figure 6–31):
Figure 6–32. Our results. Note Mary’s average: Exactly 85
Thus we see that only Gordon and Mary averaged at least 85 across all 5 tests; and these results can
be analyzed on their own, completely independent of the original database. Again, Advanced Filters will
work just as well with 10,000 students as it does with 10, and again, the original database in cells C9:I20 is
There’s still another step you can take to add value to your analysis of a database—you can define it
as a table . 1 A table is really a slightly more souped-up database, which affords you a couple of additional
It automatically turns on AutoFilter.
It supplies you with a large inventory of formats you can easily apply to the
database, including some additional customizing options.
Even after the table is established onscreen you can type additional field names to
the right of the last field, as well as new records (that is, new rows), and these will
be incorporated automatically into the table. The new fields will receive their own
AutoFilter handles, and all new fields and records will inherit the same formatting
as the rest of the table.
As you scroll down a table with many records, its headings remain visible
onscreen (Figure 6–32):
1 (Note: the term Table here is not to be confused with tables as they’re understood in Word.)