For this example, we will assume you have related data in columns A, B, C, D, and E, and you want to sort this data first by column A, then B, then C, then D, and then E. To do this, you need to be able to sort backward - in other words, sort by the last field first, and then work back to the first field.
Select columns A through E and then select Data » Sort. Select the sort order by specifying that column C be sorted first, then D, and then E. Click Sort. Now select columns A through E and select Data » Sort. This time, sort by column A and then by B. Click Sort, and everything will be in order. Excel will have sorted the columns by five fields instead of the usual three.
If you want to automate this task, you can use a macro that will sort the selection and guess whether your data has column headings based on the formatting of the first row in the selection. If headings are in bolded, Excel will know they are column headings and will not sort them. Instead, it will sort by the leftmost column first, through to the rightmost column, for any number of columns up to 256.
The macro code you need to use must be placed into a standard module. To get it there, select Tools » Macro » Visual Basic Editor (Alt/Option-F11), then select Insert » Module and enter this code:
Sub SortByX( ) Dim l As Long For l = Selection.Columns.Count To 1 Step -1 Selection.Sort Key1:=Selection.Cells(2, l), _ Order1:=xlAscending, Header:=xlGuess, Orientation:=xlTopToBottom Next l End Sub
To return to Excel, either close the window or press Alt/-Q. Once you have the hang of it, you will be able to perform much more complicated sorts than just the standard types on offer.updated