|Important||This document may not represent best practices for current development, links to downloads and other resources may no longer be valid. Current recommended version can be found here.|
OfficeTalk: Bringing Improvements to Tables in Excel 2007 (Part 5 of 6)
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 introduces significant changes to sorting table data. You can now sort up to 64 columns of data. You can also sort based on colors and custom lists. Read about these and other changes in this column. (7 printed pages)
Frank Rice, Microsoft Corporation
Applies to: Microsoft Office Excel 2007
In my previous Office Talk column about table filtering in Microsoft Office Excel 2007, I briefly discuss changes to sorting features. One of the more important changes in sorting is the elimination of the three-condition limit. You can now sort up to 64 columns. Office Excel 2007 introduces updates to the Sort dialog box. Figure 1 shows sorting a table by five columns.
Figure 1. Sort Options dialog box
The Sort dialog box is similar in behavior to the Conditional Formatting Rules Manager dialog box that I discuss in the series of Office Talk columns on conditional formatting. For more information, see Additional Resources. To create sort conditions, on the Data tab, click Sort, and in the Sort dialog box, click Add Level. Like filters, sort levels change based on data types. Office Excel 2007 uses that information to offer settings that are more descriptive and easier to understand than "ascending" and "descending." For example, for text columns, you have A to Z and Z to A sorting; for numeric columns, you can sort from Smallest to Largest and Largest to Smallest; and for date columns, you have Newest to Oldest and Oldest to Newest sorts. You can reorder sort conditions by using the buttons at the top of the Sort dialog box. You can also copy sort levels to save time. The Options button opens the Sort Options dialog box, which allows you to specify whether the sort should be case-sensitive and allows you to specify the sort orientation. Both of these features existed in Microsoft Office Excel 2003 but are more discoverable in Office Excel 2007.
The following example illustrates additional sort functionality introduced in Office Excel 2007. Assume you have the following table of formatted data.
Figure 2. Sample table with formatted data
One of the common requests from Excel users is the ability to sort by color, either manually applied or applied by conditional formatting. In Office Excel 2007, you can sort by:
Background color (however applied)
Font color (however applied)
Cell icon (applied by using conditional formatting)
For example, you can set up several conditions on the table data as seen in Figure 3.
Figure 3. You can easily set up multiple sort conditions
When you click OK, Office Excel 2007 applies the sort starting from the top. Notice the buttons at the top of each column indicate that the column is being sorted. The filter state is displayed here if a filter is applied.
Figure 4. Buttons at the top of each column provide sorting and filtering status
In addition to sorting by built-in values and color, you can specify a custom sort order by selecting Custom List at each level of sorting. You can create a custom list from the Sort dialog box.
Figure 5. You can specify a custom list to sort by
In Office Excel 2007, you can sort by color or cell icon by using in-grid filter capabilities (also known as AutoFilter). For example, assume that you have the table shown in Figure 6.
Figure 6. Sorting by Cell Color
To sort the table data so that all of the yellow and green items appear at the top of the column, use the sort button to sort first by the green background.
Figure 7. Sort first by green background
This organizes all of the green values at the top of the column.
Figure 8. Result of sorting on the color green
Next, repeat the procedure for the yellow items. This results in the following table.
Figure 9. Result of sorting on two colors sequentially
Sorting by color moves all rows that meet the criteria to the top of the table. This capability is also added to the context menu in a table or filtered range. For example, in the table shown in Figure 6, you can right-click a cell with a green background and select Put Selected Cell Color On Top in the Sort submenu.
Figure 10. Sorting by using the context menu
Next, consider an example that illustrates how to sort on a custom list of values. Assume you have a table with a column of modes of transportation.
Figure 11. Column of text values
First, in the Sort dialog box, specify the column name and then, in the Order list, select Custom List. Click OK.
Figure 12. Select Custom List in the Order drop-down
In the Custom List dialog box, select NEW LIST and then type in the data values separated by commas. Click OK when you are finished.
Figure 13. Resulting table after sorting on a custom list
The table is now sorted by the custom list of values you specified.
Figure 14. Sorted column
The ability to sort up to 64 columns of data gives you greater control over how your data appears. Likewise, sorting by cell color or font color enables you to put the data that is important to you at the top of the table column or a range. The ability to sort user-defined lists of values allows you to customize tables in a way that is relevant to your needs.
For more about tables in Office Excel 2007, see the following resources: