Out of Sorts in Excel

Without family, where would Excel blog ideas come from? My daughter called today, and asked how to sort more than 3 columns in Excel. She uses Excel 2003, and the Sort dialog box only allows three column choices. There’s no box to choose the fourth column – Name.

SortMulti03

Obviously, she didn’t check my website, where she would have found the instructions to sort by four columns in Excel. You can sort in groups of 3, working up from the least important to the most important. So, I told her to sort the fourth column (Name), then the first three (Service, City, Rating).

Sorting in Excel 2007

If you like to sort lots of columns, you’ll be happier in Excel 2007. There, you can sort up to 64 columns at one time. If my daughter had Excel 2007, she could choose all four columns, and sort them in one step. In the Sort dialog box, click the Add Level button, to include another column in the sort specifications.

SortMulti01

Automating the Sort

If you turn on the macro recorder while sorting in Excel 2007, you can create a macro to sort the table. Then, run that macro later, when you need to sort the table again.

However, if you’re sending the workbook to someone who uses Excel 2003, they’ll get an error message if they try to run your sort code.

SortMulti02

Just like families, sometimes the different generations don’t communicate too well. ;-) Because the sort feature changed so much in Excel 2007, the older versions don’t recognize some of the new properties.

If you plan to use the workbook in both versions of Excel, record the sort macro in Excel 2003. This will also run without problems in Excel 2007. Here’s the code from this example, with the Name in column D being sorted first.

SortMulti04

_______________

You may also like...

5 Responses

  1. Fred Chidester Sr says:

    Say does your daughter live near my son? He call the other day and asked almost the same question. I said say what? Send it to me, I used 2007, sorted it save it as 2003 and sent it back.

  2. Fred, I think all kids live in that same neighbourhood, don’t they? ;-)

  3. Fred Chidester Sr says:

    What really gets me is he went to LSU to learn all this stuff for business edu. I had to learn it by trial and error. Even as we sit here I’m doing a PPT presentation for my daughter for a Monday evening meeting. She just had to go back because she did copy the pitcures to disk right, Oh well so is life. I do want to throw this plug in for three of your friends – John W I can read and understand his books, they are written in plain English, Mike A. the Data Pig great articles, and my new friend from over seas Jan P thanks for the book and to you my first site to look how to, thank you Deb some day we’ll met and I’ll show you how to play pool.

  4. Thanks Fred, those guys do write great Excel stuff, and make it easy and fun to learn more about Excel. And I hope your son and daughter appreciate all your efforts!

  5. Contextures Blog » Sort By Colour in Excel says:

    […] Excel 2007, the Sort dialog box is much fancier, and you can include up to 64 sorting levels. I’ve never needed anywhere near […]

Leave a Reply

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