Excel's autofilter feature, which you access by selecting Data-Filter-AutoFilter, gives you a fast and easy way to view rows that meet certain criteria. It works with a range of cells set up as a database. When AutoFilter is turned on, the row headers display drop-down arrows that let you specify criteria (such as "Age greater than 30"). Rows that don't meet your criteria are hidden ù but are redisplayed when you turn off AutoFilter.

One problem is that Excel doesn't tell you which criteria are in effect. To overcome this deficiency, Stephen Bullen, a UK-based Excel guru, sent me a custom VBA worksheet function that displays a cell's AutoFilter criteria.

Caption: This is the VBA code for the FilterCriteria function. Use the function to display AutoFilter criteria

To create this function in Excel 97 or later, press <Alt>-<F11> to insert a new module for the active workbook. Then enter the code illustrated above (to save time, simply copy and paste the code from the filter.txt file on this month's CD). In Excel 5 or 95, select Insert-Module and enter the code in the new module sheet. Once you've entered the code, you can type in a formula such as =FilterCriteria(A3). The single-cell argument for the FilterCriteria (in this example, A3) can refer to any cell within the column of interest. The formula will return the current AutoFilter criteria (if any) for the column of the cell specified in the FilterCriteria function.

The graphic below shows the FilterCriteria function in action. The cells in row 1 contain formulas that use FilterCriteria. As you can see, the list is currently filtered to display a table in which column A contains the word January, column C contains a code of either A or B, and column D contains a value greater than 125 (column B is not filtered). The rows that don't match these three criteria are hidden.

Caption: FilterCriteria displays the AutoFilter criteria currently in use (see Row 1).