Microsoft Office Tutorials and References
In Depth Information
Using Tables to Sor t and Filter Data
Using Tables to Sort and Filter Data
In Excel 2007, the primary way to sort and filter data is with tables. The Table feature is a continuation of
the List feature introduced in Excel 2003. Tables offer more than just sorting and filtering, however —
they are a new paradigm for working with tabular data in general, including structured formatting and
naming within the table. Excel Services supports all aspects of the Table feature, but in the context of
interactivity, the focus is on using the Table feature to sort and filter. Excel Services also supports
autofilters, which enable you to sort and filter values in a sheet not in the context of a table.
Excel Services loads tables and filters them based on the settings in the workbook. You can also perform
the following sort and filter operations directly in the browser:
Sort ascending and descending within a column
Use Excel 2007’s new Number, Date, and Text filters (for example Equals, Above Average,
Before, Next Week, and Begins With)
Filter on specific values in a column (including Excel 2007’s new automatic grouping of date
Use custom filters
Similar to Excel client, when a column is filtered, a filter icon is displayed in the column header.
In the following Try It Out, you use Excel Services to sort and filter the data in a table. For this exercise,
you again use the sample workbook.
Try It Out Using Tables to Sort and Filter Data
To use tables to sort and filter data, follow these steps:
Open the original Sales Report.xlsx workbook in Excel client and publish it to the server. Ensure
that you have the Open In Excel Services option selected so that the browser opens with the
workbook displayed. If you already published the workbook, you can simply open it in the
browser directly from the document library on the server.
Switch to the Source Data Tables tab. (It is the last sheet in the workbook, so you may need to
scroll to it.) This sheet has the Source Data table in it. You can sort and filter on each of the
Select the sort-and-filter drop-down menu on the Region column. On the Text Filters fly-out
menu, select Begins With, as shown in Figure 10-7.
In the Custom Filter dialog box, enter North as the Begins With text. This filters the table to
show only the north regions, as shown in Figure 10-8. Your table should now look like the table
in Figure 10-8. The sort-and-filter icon for the Region column drop-down menu is replaced with
a filter icon, which signifies that the column is being filtered.