Pin Me

Microsoft Excel: How To Specify More Than Three Columns In A Sort

written by: Mr Excel•edited by: Tricia Goss•updated: 7/18/2008

Problem: As shown in Fig. 630, you want to sort by product within Customer, Rep, District, Region, and Country. The Sort dialog only offers three columns by which to sort.

  • slide 1 of 2

    Strategy: Use two calls to the Sort menu. The first sort will get the minor sort criteria of Sales Rep, Customer, and Product. The next sort will sort by Country, Region, and District.

    1) Select a SINGLE CELL within your list and choose Sort from the Data menu. In the Sort dialog box, choose the last three fields in your list, as shown in Fig. 631. Choose OK to complete the first sort.

    2) Again select Sort from the Data menu. This time, select to sort by Country, Region, and District, as shown in Fig. 632. For all records where there is a tie between these three fields, the sequence of the first sort will be used as the tiebreaker.

    Alternate Strategy: Use the AZ button on the Standard toolbar. Select one cell in the Product column and choose the AZ button on the toolbar. Next, select one cell in the Customer column and choose the AZ button. Continue selecting single cells in each prior column and choosing the AZ button.

    Summary: When there is a tie among the three columns in a sort, Excel retains the prior order of the records. Thus, it is possible to sort six columns by using two calls to the Sort command, provided that you sort the least important fields first.

  • slide 2 of 2

    Images

    Fig. 630Fig. 631Fig. 632Fig. 633