Sorting Rows and Columns

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:

  1. Click the column or field in the list that you want to use as the basis for sorting the list.
  2. From the Data menu, choose Sort. Excel selects all the records in your list and displays the following dialog box:
  3. ON THE WEB
    The Pivot.xls example is on the Running Office 2000 Reader's Corner page.

  • The Sort By drop-down list box contains the heading for the column you selected. If you like, you can select a different column in the list box now for the sort.
  • Click one of the sort order option buttons to specify ascending order (A to Z, lowest to highest, earliest date to most recent) or descending order (Z to A, highest to lowest, most recent date to earliest).
  • Click OK to run the sort. If you sorted the first column in ascending order, your screen will look similar to this:
  • click to view at full size.

    TIP
    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.

    Sorting on More Than One Column

    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:

    1. Click a cell in the list that you want to sort.
    2. From the Data menu, choose Sort. Excel selects the records in your list and displays the Sort dialog box.
    3. Select the primary field for the sort in the Sort By drop-down list box. Specify ascending or descending order for that column.
    4. Click the first Then By drop-down list box and pick a second column for the sort, to resolve any ties in the first sort. Specify ascending or descending order for the second sort as well.
    5. Click the second Then By drop-down list box and pick a third column for the sort, again to resolve any ties remaining after the first two sorts. Once more, specify ascending or descending order. (Your sorts needn't be in the same direction.) A Sort dialog box that has three levels of sorting is shown here:
    6. Click OK to run the sort.
    7. 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.)

      click to view at full size.

    TIP
    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.

    Creating Your Own Custom Sort Order

    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:

    1. Choose Options from the Tools menu, and then click the Custom Lists tab.
    2. Click the line NEW LIST under Custom Lists, and the text pointer will appear in the List Entries list box. This is where you'll type the items in your custom list. (In this example, you'll create the custom order West, Midwest, South, East.)
    3. Type West, Midwest, South, East, and then click the Add button. You can either separate each value with a comma or type it on a separate line.

      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.

    4. Click OK to close the Options dialog box.

    TIP
    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.

    Using a Custom Sort Order

    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.

    click to view at full size.

    Figure 22-3. The Custom Lists tab lets you add, delete, and edit Excel's collection of custom sorting orders.

    1. Click any cell in your list.
    2. From the Data menu, choose Sort. Excel selects the records in your list and displays the Sort dialog box.
    3. Select the primary field for the sort in the Sort By list box. Specify ascending or descending order. (The direction you specify will also apply to the custom sort.) In our example, we selected the Region field, ascending order.
    4. Click the Options button to display the Sort Options dialog box.
    5. Click the First Key Sort Order drop-down list box, and click the custom order that you want to use.
    6. NOTE
      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.

  • Click OK in each dialog box to run the sort. Your list will appear sorted with the custom criteria you specified.


  • Running Microsoft Office 2000 Small Business
    Running Microsoft Office 2000
    ISBN: 1572319585
    EAN: 2147483647
    Year: 2005
    Pages: 228
    Authors: Michael Halvorson, Michael J. Young
    BUY ON AMAZON

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net