OfficeTalk: Bringing Improvements to Tables in Excel 2007 (Part 4 of 6)

  • Article

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Summary: Microsoft Office Excel 2007 makes filtering more versatile. Learn how you can set complex filtering options with just a few clicks of the mouse and how you can sort based on color. (6 printed pages)

Frank Rice, Microsoft Corporation

November 2007

Applies to: Microsoft Office Excel 2007

Contents

  • Overview

  • Filter Feature is Easier to Find

  • Complex Filtering is Easy to Perform

  • Filtering by Color

  • Determining Filtering and Sorting State with Visual Clues

  • Conclusion

  • Additional Resources

Overview

Sorting and filtering are two common analyses that you perform on data. Microsoft Office Excel 2007 improves sort and filter functionality by exposing common tasks, by simplifying key tasks, and by providing functionality that is not available in previous versions of Excel. In this column, I discuss filtering, known as AutoFilter in previous versions of Excel. For the remainder of this column, I refer to this functionality as the Filter feature.

Improvements to the Filter feature in Excel 2007 include:

  • Multiselect in filters so that you can select any number of items for your filter condition

  • Sorting and filtering by color

  • An increase in the number of items available in the Filter list (from 1,000 to 10,000 items)

  • A quick filter option to enable data type–specific filtering

  • The ability to organize by date when using date filters

  • The ability to reapply a set of filters with a single click

  • More visual clues to quickly determine what filters are applied to a range or table

Filter Feature Is Easier to Find

In Excel 2007, the Filter feature is easier to find and enable. To find the Filter feature, on the Office Fluent Ribbon on the Data tab in the Sort & Filter group, click Filter.

Figure 1. Sort & Filter Group and the Filter toggle button

You activate the Filter feature by selecting a range of data and clicking Filter. This displays an arrow at the top of the range. Clicking the arrow displays the Sorting and Filtering dialog box.

Figure 2. Sorting and Filtering dialog box

Sort options remain at the top of the list but now reflect the type of data being filtered (Smallest to Largest for numbers, Oldest to Newest for dates). Excel 2007 also adds the ability to sort by color. In addition, you can remove all filter conditions from a single column quickly. Below the sort options, you see the filter options and the filter items themselves.

Note

As you can in previous versions of Excel, you can set custom filtering options by using the Advanced filtering option. To use this option in Excel 2007, on the Office Fluent Ribbon, in the Sort & Filter group, click Advanced.

Complex Filtering is Easy to Perform

Let's look at filtering in more detail, starting with multiselect. In previous versions of Excel, to select multiple items, you needed to use the Custom dialog that limited you to two conditions. In Excel 2007, you simply select the items you want to see included in your filter and then click OK. This is much faster, easier to discover, and supports as many items as you want to filter by.

Figure 3. Filtering on more items

With Excel 2007, you can express more complex filtering conditions. Excel provides quick filtering options based on the data type of the column. For example, assume that you are reviewing a record of sales for the past couple years and want to see how much revenue you generated last month and which sales brought in the most money. By clicking the filter list on the date column and then clicking the Date Filter menu item, you are presented with a large list of date filters, among which is the option to filter records for last month.

Figure 4. You have more filtering options

As you can see in Figure 4, Excel 2007 offers many date filters that simplify filtering by different date ranges. What makes these date filters special is that they are based on the system clock so the Last Month filter always filters data to the previous month.

There are quick filters for numeric data as well. For example, after you filter by date, you can then filter the revenue column to show only sales that were above average.

Figure 5. You can easily see data that is above average

As you might guess, Excel 2007 also provides text-based filters for working with nonnumeric columns.

Figure 6. Intrinsic text-based filters

Another feature for date-based columns is that the filter organizes dates by day, month, and year rather than displaying a flat list of dates. This allows you to pick a specific series of dates. If you want to select all dates in a particular month, for example, you can do so in two clicks.

Figure 7. Easily select the dates you want

Filtering by Color

In Excel 2007, you can also filter by color. For example, if you have a table of data and you use orange fill for some rows and red fill for other rows that need special attention, you can filter to see all rows that have red fill. You can set up that filter with just a few clicks.

Figure 8. The Filter by Color dialog

Filter by color allows you to filter by font color and by cell fill color. In addition, filtering also recognizes conditional formats on cells, including regular formatting, gradient fills, and conditional formatting icon sets. For more information about conditional formatting, see OfficeTalk: Conditional Formatting: Adding Custom Conditional Formatting to Excel 2007.

In addition to filtering by color, Excel 2007 also allows you to sort by cell color and by background color. You can see examples of sorting later in this series of columns.

Determining Filtering and Sorting State with Visual Clues

Excel 2007 provides visual clues that help you determine when a table has filtering or sorting applied as well as what the sort or filter is. For any column that has a filter set, Excel 2007 changes the filter icon to denote its filter state. The same thing happens, using a different icon, for columns that have a sort order. In Figure 9, you can quickly see which columns in the table have a sort order and which columns have a filter. In addition, when you move the pointer over the icon, Excel 2007 displays a small box that describes the sort and filter state of the column.

Figure 9. Visual aids indicate filtering and sorting settings

As in previous versions of Excel, Excel 2007 applies a sort or filter only at the time you create the sort or filter criteria or when you refresh a query if the table is connected to external data. This ensures that data does not move or disappear as you edit it. Of course, there are times when you need to reapply a sort order or filter, such as after you copy and paste several new records to the bottom of a table or range. Excel 2007 makes it easy to reapply all sorting and filtering conditions on a table with a single click of a button on the Office Fluent Ribbon.

Figure 10. The Reapply filtering command on the Ribbon

As you see in Figure 10, you can remove all filters from all columns in a table with a single mouse click.

You can use everything discussed in this column without the table feature. However, there are certain advantages to using these options in conjunction with tables. Each table has its own filter, whereas the sheet can only have a single filter. This means that if you need to filter more than one dataset on a sheet, tables are your only option. Similarly, tables also retain their own sort conditions, so if you need to maintain multiple sort states across a sheet, you need to use tables.

As in previous versions of Excel, data sets can share table rows and columns of data. If two tables share the same rows, filtering in Excel 2007 works as it does in Excel 2003, in that the last filter applied to a set of rows wins. So assume you have two tables that share rows, Table1 and Table2. When you apply a filter to Table1 and then apply a filter to Table2, the result is the same as if you had not filtered Table1 at all.

Conclusion

Excel 2007 offers many improvements to filtering, from ease of discovery to filtering by color. In future Office Talk columns, I discuss table sorting and advanced formatting.

Additional Resources

For more information about working with tables in Excel 2007, see the following resources: