Excel Hacks Ebook [Electronic resources] نسخه متنی

Hack 31 Manipulate Data with the Advanced Filter

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, where
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 Filter is that two or more criteria placed directly
underneath the applicable heading use an OR
statement. If you want to use an
AND statement, the column headings and their
criteria must appear twice, side by side. Figure 2-14 shows how to use the
OR operator to filter your data, and Figure 2-15 shows how to use the AND
operator.

Figure 2-14. Using Advanced Filter with OR to show only those people who have a pay rate greater than $16.00 OR less than $15.00

Figure 2-15. Using Advanced Filter with AND to show only those people who have a name starting with B AND ending with L

Both of the preceding examples show fairly simple uses of the
Advanced Filter tool and can be accomplished via AutoFilter if
needed. Next we'll
provide some examples of the Advanced Filter in which the use of
AutoFilter would not be possible.

It's important to note that whenever you use a formula for your criteria, you must not use above the criteria a heading that is identical to the one within the table. For example, if you have a list of numeric data in column A and the list begins in cell A5 (with A4 being the heading), and you need to extract all the numbers in that list that are greater than the average, you would use criteria such as these: =A5>AVERAGE($A$5:$A$500) If the criteria were placed in cell A2, the criteria range would be $A$1:$A$2, but $A$1 could not contain the same heading as the one the list uses. It must be either blank or a different heading altogether. It also is important to note that any formula you use should return either TRUE or FALSE. The range for the average function is made absolute by the addition of dollar signs, while the reference to cell A5 is a relative reference. This is needed because when you apply the Advanced Filter, Excel will see that A5 is a relative reference and will move down the list one entry at a time and return either TRUE or FALSE. If it returns TRUE, it knows it needs to be extracted. If it returns FALSE, it does not meet the criteria; therefore, it will not be shown.

Also assume that many of the names are
repeated in the range $A$5:A$500, with A4 being the headings. Also
assume that many of the headings are repeated numerous
times. You have been given the task
of extracting from the list all the names that appear more than
once. To do this you need to use the
Advanced Filter and the following formula as your criteria:

=COUNTIF($A$5:$A$500,A5)>1

Once you apply the Advanced Filter to this and use the Copy to
Another Location: option, the newly created list will contain all the
names that appeared more than once in the original list (see Figure 2-16). Many of
these names will be repeated numerous times, but you can easily
filter this new list again with the Advanced Filter, this time
selecting Unique Records Only (see Figure 2-17). This will
give you a list of names that appear in the list more than
once.

Figure 2-16. Using Advanced Filter to extract names from a list that appear more than once

Figure 2-17. Using Advanced Filter on the extracted list of names to filter down to show each name only once (Unique Records Only)

Advanced Filter users commonly ask how they can force Excel to filter their data by the exact criteria they have provided. If your criterion is Dave and you perform an Advanced Filter on a long list of names, Excel would show not only the name Dave, but also names such as Davey, Dave J, Dave K, etc. In other words, any name that begins with the letters Dave, in that order, will be considered a match for the criteria. To force Excel to find exact matchese.g., in this case find only the name Daveenter your criteria as ="=Dave".