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:

SortMulti1

The first sort:

SortMultiCol2

The second sort:

SortMultiCol3

The final sorted range:

SortMultiCol4

7 thoughts on “Sort On More Than 3 Columns

  1. 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.

  2. 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.

  3. 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

  4. 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.

  5. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *