Once your records are organized into a list, you can use several commands on the Data menu to rearrange and analyze the data. The Sort command allows you to arrange the records in a different order based on the values in one or more columns. You can sort records in ascending or descending order or in a custom order, such as by days of the week or months of the year.
To sort a list based on one column, follow these steps:
ON THE WEB
The Pivot.xls example is on the Running Office 2000 Reader's Corner page.
Click a Cell, Sort a List
To quickly sort a list based on a single column, click a column head or a cell in the column, and then click either the Sort Ascending or Sort Descending button on the Standard toolbar. Excel rearranges the list in the order that you selected.
If you have ties in your sort— that is, if some of the records in your list have identical entries in the column you're sorting with— you can specify additional sorting criteria to further organize your list. To sort a list based on two or three columns, follow these steps:
The following screen shows how a sort would look based on the options shown above. Note that the columns you specify in the Then By sections are used only to resolve ties in the list— not to control the entire sort. (For this reason, numbers in the Sale column are only in descending order when both the Sales Rep and the Description fields are identical.)
To restore a list to its original order after a sort, choose Undo Sort from the Edit menu immediately after running the sort, or display the Undo button's drop-down list and click an earlier sort action to reverse it.
Excel allows you to create custom sort orders so that you can rearrange lists that don't follow predictable alphanumeric or chronologic patterns. For example, you can create a custom sort order for the regions of the country (West, Midwest, East, South) to tell Excel to sort the regions in the way you want rather than by strict alphabetic rules. When you define a custom sort order, it appears in the Sort Options dialog box and is available to all the workbooks in your system.
To create a custom sort order, follow these steps:
The new custom order appears in the Custom Lists list box, as shown in Figure 22-3. You can now use this sorting order to sort your columns, as described in "Using a Custom Sort Order" next.
You can also use the Custom Lists tab to edit and delete other list formats in your system. For example, you could rearrange the days-of-the-week sorting order so that Monday appears as the first day of the week and Sunday appears as the last.
When you want to sort based on an order that isn't alphabetical or numerical— the days of the week, for example, or the months of the year that have been entered as text rather than dates— you can click the Options button in the Sort dialog box and specify a custom sort order to use for the comparison. To use a custom sort order, follow these steps.
Figure 22-3. The Custom Lists tab lets you add, delete, and edit Excel's collection of custom sorting orders.
You can also specify custom sort orders for the second and third sort options if you want to, in which case the title of the sort order drop-down list box will change to match the key that you're defining.