O'Reilly logo

Excel Hacks by Raina Hawley, David Hawley

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required