Excel Hacks Ebook [Electronic resources]

Raina Hawley, David Hawley

نسخه متنی -صفحه : 136/ 53
نمايش فراداده

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".