How to sort more than three columns in Excel (268007)

SUMMARY

Microsoft Excel limits you to a maximum of three columns or fields when you sort a list. If you want to sort a list of more than three fields, you must sort multiple times, with three or fewer fields at a time. Also, you must sort the fields in the reverse order of their importance in the sort.

This step-by-step article contains an example of how to sort a list by more than three fields.

back to the top

How to Sort More Than Three Columns

The following example creates and sorts a list that contains four fields: manager, employee, amount, and year. The year field is the least important field, so you sort it first.

1. Start Excel.
2. Type the following data in Sheet1:

      A1: Manager  B1: Employee   C1: Amount   D1: Year
      A2: Bob      B2: Mark       C2: 1        D2: 1999
      A3: Sue      B3: Jane       C3: 1        D3: 1995
      A4: Bob      B4: Paul       C4: 1        D4: 1993
      A5: Bob      B5: Paul       C5: 1        D5: 1999
      A6: Sue      B6: Jane       C6: 2        D6: 1998
      A7: Sue      B7: Mary       C7: 2        D7: 1993
      A8: Bob      B8: Paul       C8: 2        D8: 2000
      A9: Sue      B9: Jane       C9: 1        D9: 1999
     A10: Bob     B10: Paul      C10: 1       D10: 1991
     A11: Sue     B11: Jane      C11: 1       D11: 1990
   					

3. Select cell A1.
4. On the Data menu, click Sort.
5. In the Sort dialog box, click Year in the Sort by list
6. In the first Then by list, click (none).
7. In the second Then by list, click (none).
8. Click OK.
   
   The list is now sorted by year (the fourth, or least important, field).
9. On the Data menu, click Sort again.
10. In the Sort dialog box, click Manager (the first, or most important, field) in the Sort by list.
11. In the first Then by list, click Employee (the second-most important field).
12. In the second Then by list, click Amount (the third-most important field).
13. Click OK.

The list is now sorted first by manager, then by employee, then by amount, and then by year:

   A1: Manager  B1: Employee   C1: Amount   D1: Year
   A2: Bob      B2: Mark       C2: 1        D2: 1999
   A3: Bob      B3: Paul       C3: 1        D3: 1991
   A4: Bob      B4: Paul       C4: 1        D4: 1993
   A5: Bob      B5: Paul       C5: 1        D5: 1999
   A6: Bob      B6: Paul       C6: 2        D6: 2000
   A7: Sue      B7: Jane       C7: 1        D7: 1990
   A8: Sue      B8: Jane       C8: 1        D8: 1995
   A9: Sue      B9: Jane       C9: 1        D9: 1999
  A10: Sue     B10: Jane      C10: 2       D10: 1998
  A11: Sue     B11: Mary      C11: 2       D11: 1993
				

back to the top