How to sort more than three columns in Excel

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.

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

References

For more information about sorting, click Microsoft Excel Help on the
Help menu, type sort a list in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Properties

Article ID: 268007 - Last Review: Jan 27, 2007 - Revision: 1

Feedback