AutoFilter

The autofilter, autofilterrange, and autofiltervalue attributes of HTML TD and TH elements are used to store information about automatic filtering.

autofilter attribute
autofilterrange attribute
autofiltervalue attribute
Complex custom filters and advanced filters
Saving hidden filtered rows

autofilter attribute

Specifies that filter drop-down arrows and the default filter value are displayed in a column header row on a worksheet. This attribute can be specified in TD and TH elements and contains one of the following constants.

Constant Description
all The column is not filtered and all rows are displayed. If the autofilter attribute is not specified, all rows are displayed by default.
blanks Display rows that are blank in the column.
nonblanks Display rows that have data in the column.
top Display the top number of rows or a percentage of rows. The number or percentage is contained in the autofiltervalue attribute.
bottom Display the bottom number of rows or a percentage of rows. The number or percentage is contained in the autofiltervalue attribute.
custom Display rows that meet the criteria specified by the formula contained in the autofiltervalue attribute.

When the autofilter attribute is specified, the worksheet displays rows that meet a criterion. The user can change the display to show or hide rows of data by using the drop-down arrows on the column header row. When more than one column is filtered, only rows that satisfy all the filters are displayed.

Example

In the following example, the Product column is filtered for non-blank columns and the Channel column is filtered for columns containing the word "Wholesale". Rows that have a non-blank Product field and the word "Wholesale" in the Channel field are displayed.


<TD AUTOFILTER="nonblanks" AUTOFILTERRANGE="A1:D14">Product</TD>
<TD AUTOFILTER="custom" AUTOFILTERVALUE="Wholesale">Channel</TD>

autofilterrange attribute

Specifies a range that a filter is applied to. This attribute specifies a single range in A1-style. If the autofilterrange attribute is not specified, there is no AutoFilter range. This attribute can be specified in TD and TH elements that specify the autofilter attribute.

The AutoFilter range is a named range in Microsoft Excel, but the name is not stored as a named range when the workbook is saved as a Web page.

Example

This example displays the channel, movie title, date, and day of the week for movies on channel 5 on Thursday. The data being filtered is in the range A2:D50.


<TD AUTOFILTER="custom" AUTOFILTERVALUE="=5" AUTOFILTERRANGE="A2:D50">Channel</TD>
<TD AUTOFILTER="all">Movie</TD>
<TD AUTOFILTER="all">Date</TD>
<TD AUTOFILTER="custom" AUTOFILTERVALUE="=Thursday">Day of the Week</TD>

autofiltervalue attribute

Specifies the number or percentage of top or bottom rows, rows that match a user-defined string, or rows that match the results of a formula. This attribute can be specified in TD and TH elements that specify the autofilter attribute containing the string constants bottom, custom, or top.

Examples

This example displays statistics for the top 10 percent of students in a graduating class.


<TD AUTOFILTER="top" AUTOFILTERVALUE="10%">GPA</TD>

The following example displays statistics for the bottom 5 students with the lowest GPA.


<TD AUTOFILTER="bottom" AUTOFILTERVALUE=5>GPA</TD>

In the next example, the Restaurants column is not filtered because the autofilter attribute contains the string constant all, and so all the rows are displayed. The Types of Food column is filtered on the word "All". The Price column is filtered on the word "Moderate". The Location column is not filtered.


<TD AUTOFILTER="all" AUTOFILTERRANGE="A50:D50>Restaurants</TD>
<TD AUTOFILTER="custom" AUTOFILTERVALUE="=All">Types of Food</TD>
<TD AUTOFILTER="custom" AUTOFILTERVALUE="=Moderate">Price</TD>
<TD AUTOFILTER="all">Location</TD>

This example displays students who have at least a 3.0 GPA and are over the age of 18.


<TD AUTOFILTER="all"><B>Name</B></TD>
<TD AUTOFILTER="custom" AUTOFILTERVALUE=">=3.0"><B>GPA</B></TD>
<TD AUTOFILTER="all"><B>Rank</B></TD>
<TD AUTOFILTER="custom" AUTOFILTERVALUE=">=18"><B>Age</B></TD>

Complex custom filters and advanced filters

Filters can contain up to two formulas, allowing complex filtering of a series of rows. The string and or or between the formulas specifies whether both or either formula must be satisfied to display the row. For example, the following filter displays students who have a GPA from 2.0 to 2.5.


<TD AUTOFILTER="custom" AUTOFILTERVALUE=">=2,and,<=2.5">GPA</TD>

A comma is used as the delimiter between arguments.

An advanced filter is stored in a Web page using the XML AdvancedFilter element in the XML element at the worksheet level. The following table lists the subelements of AdvancedFilter.

Subelement Description
CopyTo Optional. Specifies whether the filtered rows are copied to another location or filtered in the original location, and if specified, contains the destination range.
CriteriaRange Required. Specifies the range containing the filter criteria.
ListRange Required. Specifies the range to filter.
UniqueRecordsOnly Optional. Specifies whether only unique rows are displayed. If <x:UniqueRecordsOnly/> is specified, only unique rows are displayed.

Example

This example filters the list in range A2:G9 on Sheet1 using criteria from the range F14:G16. The filtered rows are copied to range H2:N9.


<x:AdvancedFilter>workbook_sheet.htm
 <x:ListRange>"Sheet1!$A$2:$G$9"</x:ListRange>
 <x:CriteriaRange>"$F$14:$G$16"</x:CriteriaRange>
 <x:CopyTo>"Sheet1!$H$2:$N$9"</x:CopyTo>
</x:AdvancedFilter>

Saving hidden filtered rows

When a filtered table is saved as a Web page, some rows might be hidden. The display:none style is used to hide the data and store its status. Because this style is also used for hidden, unfiltered rows, the mso-ignore:display style is also specified to distinguish it as a filtered row that is currently hidden.

The following example displays the weather and temperature in New Orleans. The information for Charlotte and Fairhope is not displayed.


<TABLE>
<TR> 
<TD AUTOFILTER="all" AUTOFILTERRANGE="A1:C4">Location</TD> 
<TD AUTOFILTER="custom" AUTOFILTERVALUE="=sunny">Weather</TD>
<TD AUTOFILTER="all">Temperature</TD></TR>
<TR STYLE="display:none;mso-ignore:display">
<TD>Fairhope</TD><TD>Thunderstorms</TD><TD>82</TD></TR>
<TR STYLE="display:none; mso-ignore:display">
<TD>Charlotte</TD><TD>Overcast</TD><TD>79</TD></TR>
<TR>
<TD>New Orleans</TD><TD>Sunny</TD><TD>86</TD></TR>
</TABLE>