How to sort more than three columns in Excel

Article translations Article translations
Article ID: 268007 - View products that this article applies to.
This article was previously published under Q268007
Expand all | Collapse all

On This Page

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: January 27, 2007 - Revision: 4.1
APPLIES TO
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 97 Standard Edition
Keywords: 
kbhowtomaster KB268007

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com