More Than Two Conditions for Custom AutoFiltering

Excel's AutoFilter is a great tool for viewing portions of a data table. If you turn on the AutoFilter (Data | Filtering | AutoFilter), drop-down arrows appear at the top of each column of your data table. Using the choices available through each drop-down, you can pick what data you want displayed from the data table. If you choose the Custom option from a drop-down list, Excel displays the Custom AutoFilter dialog box. (Click here to see a related figure.)

This dialog box allows up to two conditions to be defined--conditions which will affect what is displayed. What if you want to specify more than two conditions, however? Unfortunately, there is no direct way to do this. There is an "indirect" way, however: You could create a new column in your data table that will contain a formula that indicates whether a record should be displayed or not. The formula could contain any number of IF, AND, OR, and NOT conditions to render either a True or False result. You can then use AutoFilter to display only those rows that return True as a result, or only those that return False.

You always have the option, of course, to use the advanced filtering capabilities of Excel. As described in other ExcelTips, advanced filtering allows you to create complex criteria that control what is displayed from a data table.