Sort On More Than 3 Columns
Excel’s sort feature allows you to sort on up to three different columns. If you need to sort on more than three, you can sort the same range multiple times. This takes advantage of the fact that if two values are equal, Excel will keep them in their original order. For this to work properly, you must sort in reverse order. For instance, if you’re sorting on five columns, first sort on columns 3,4,5, then sort on 1,2.
In this example, the data is sorted on Company Name, Contact Name, Country, Region, City, in that order.
The unsorted range:
The first sort:
The second sort:
The final sorted range:
Gary Hunt:
Thanks for the tip, however I would of thought that excel would allow you sort sort by more than 3 columns, my old Amiga could do as many as you wanted and/or up to your memory limit. This is a seroius limit to large spreadsheets. I hope they sort it out in the next update.
21 October 2005, 4:48 amJon Peltier:
David Gainer has reported that Excel 12 will sort on up to 64 criteria:
http://blogs.msdn.com/excel/archive/2005/09/26/474258.aspx
21 October 2005, 12:41 pmDebbie Ruzich:
Can you have more than two consecutive sorts? When I try to sort a third time, the first two sorts are overwritten. I seems to only work for the first two Data Sort boxes.
8 November 2005, 4:38 pmBill Doherty:
If I understand your example correctly, when sorting columns A.B.C.D.E, (E being the least important). I sort in this order E.D.C and then A.B.
This did not work for my completely numeric Leader Board. Columns C and D were wrong
Sorting C.D.E and then A.B. did
17 January 2006, 9:54 amKuba:
The example is correct. The author says at the beginning that the table is to be sorted by “…Country, Region, City”. This and the screens are consistent with Bill’s conclusion on CDE&AB.
21 February 2006, 4:11 amKuba:
When sorting large number of columns I suggest concatenating them.
21 February 2006, 4:14 amFor example, you have 10 columns to sort one by another. IN the 11th column deploy the concatenate(col1, col2, …) function and then sort by this column only.