How to Use Advanced Filtering on an Excel Table
Most of the time, you’ll be able to filter Excel table records in the ways that you need by using the Filter command or that unnamed table menu of filtering options. However, in some cases, you might want to exert more control over the way filtering works. When this is the case, you can use the Excel advanced filters.
Writing Boolean expressions
Before you can begin to use the Excel advanced filters, you need to know how to construct Boolean logic expressions.
For example, if you want to filter the grocery list table so that it shows only those items that cost more than $1 or those items with an extended price of more than $5, you need to know how to write a Boolean logic, or algebraic, expression that describes the condition in which the price exceeds $1 or the extended price exceeds or equals $5.
The range A13:B14 describes two criteria: one in which the price exceeds $1, and one in which the extended price equals or exceeds $5. The way this works, as you may guess, is that you need to use the first row of the range to name the fields that you use in your expression.
After you do this, you use the rows beneath the field names to specify what logical comparison needs to be made using the field.
To construct a Boolean expression, you use a comparison operator and then a value used in the comparison.
|Operator||What It Does|
|<||Is less than|
|<=||Is less than or equal to|
|>||Is greater than|
|>=||Is greater than or equal to|
|<>||Is not equal to|
The Boolean expression in cell A14, checks to see whether a value is greater than 1, and the Boolean expression in cell B14 checks to see whether the value is greater than or equal to 5. Any record that meets both of these tests gets included by the filtering operation.
Here’s an important point: Any record in the table that meets the criteria in any one of the criteria rows gets included in the filtered table. Accordingly, if you want to include records for items that either cost more than $1 apiece or that totaled at least $5 in shopping expense (after multiplying the quantity times the unit price), you use two rows — one for each criterion.
Running an advanced filter operation
After you set up a table for an advanced filter and the criteria range, you’re ready to run the advanced filter operation. To do so, take these steps:
Select the table.
To select the table, drag the mouse from the top-left corner of the list to the lower-right corner. You can also select an Excel table by selecting the cell in the top-left corner, holding down the Shift key, pressing the End key, pressing the right arrow, pressing the End key, and pressing the down arrow. This technique selects the Excel table range using the arrow keys.
Choose Data tab’s Advanced Filter.
Excel displays the Advanced Filter dialog box.
Tell Excel where to place the filtered table.
Use either Action radio button to specify whether you want the table filtered in place or copied to some new location. You can either filter the table in place (meaning Excel just hides the records in the table that don’t meet the filtering criteria), or you can copy the records that meet the filtering criteria to a new location.
Verify the list range.
The worksheet range shown in the List Range text box — $A$1:$E$10 — should correctly identify the list. If your text box doesn’t show the correct worksheet range, however, enter it.
Provide the criteria range.
Make an entry in the Criteria Range text box to identify the worksheet range holding the advanced filter criteria. The criteria range is $A$13:$B$15.
(Optional) If you’re copying the filtering results, provide the destination.
If you tell Excel to copy the filter results to some new location, use the Copy To text box to identify this location.
Excel filters your list. Note that the table now shows only those items that cost more than $1 and on which the extended total equals or exceeds $5.
And that’s that. Not too bad, eh? Advanced filtering is pretty straightforward. All you really do is write some Boolean logic expressions and then tell Excel to filter your table using those expressions.