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.

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

When using the Advanced Filter, keep in mind 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.