OfficeTalk: Bringing Improvements to Tables in Excel 2007 (Part 5 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 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

December 2007

Applies to: Microsoft Office Excel 2007

Contents

  • Overview

  • More Sorting Options

  • Sorting by Colors

  • Sorting by Cell Color

  • Sorting with Custom Lists

  • Conclusion

  • Additional Resources

Overview

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

More Sorting Options

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.

Sorting by Colors

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

Sorting by Cell Color

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

Sorting with Custom Lists

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

Conclusion

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.

Additional Resources

For more about tables in Office Excel 2007, see the following resources: