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:
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.
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
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.
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
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.
When sorting large number of columns I suggest concatenating them.
For 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.
The REAL TIP is Kuba’s 2nd tip on concatenating columns! Thanks, saved me a lot of work!