If you are familiar with Excel's AutoFilter tool, you also are familiar with its limitations. If you require extensive data manipulation, using Excel's Advanced Filter tool is the way to go.
Although limited, AutoFilters are a useful way to display only the data that meets particular criteria. Sometimes, however, you cannot glean the information you need using the standard options available in AutoFilters. Excel's versatile Advanced Filter tool enables you to further manipulate your data.
When you use Excel's Advanced Filter tool, your table must be set up in a classic table format as described at the start of Chapter 1.
When using Excel's
Advanced Filter tool, you will need a copy of your
table's column headings somewhere above your
data. You should always leave at
least three blank rows above your table of data. To ensure that your headings are exactly the
same and will remain so regardless of whether you change your column
headings, always reference the column headings with a simple
reference formula such as
A4 contains a column heading. Copy this across for as many column headings
as you have in your table. This will
ensure that the criteria headings for the Advanced Filter are
dynamic. Directly below these copied
headings, place the criteria for the Advanced Filter to use. For more details on this process, see the
Excel Help under Advanced Filters Criteria.
One point to keep in mind when using the Advanced ...