Tips, tricks, tools, and techniques for Microsoft Office

For simple sorts (chronological, smallest to largest value, and so on), Excel’s built-in sort feature is probably adequate for most users. When you need a more powerful sort, Special Sort from Primitive Software is a low-cost ($14) and very effective add-in that gets the job done with ease. We tested the latest version (1.62).

There’s no complicated installation procedure – copy the single XLA file to your add-ins folder (the readme file provides the details), then open Excel. Now on the Data menu, just below Sort, is a new option: Special Sort. Choose the type of sort you want to perform, select up to three levels (just like in Excel’s own sort feature), pick the sort order (ascending or descending), and you’re done. The power of Special Sort is in the types of sorts it supports.

If you sort titles, for example, the "x Articles" option ignores "a", "an", and "the" and any "junk characters" before the first alphabetic character. The utility’s "x Articles - Nums" option treats numbers as though they were spelled out. (See what we mean by powerful?)

Special Sort is a wiz with numbers, too. For example, Excel sorts decimals from left to right, which means 1.10.1 comes before 1.2.1. Of course, sorting "ten" before "two" makes no sense to you and me -- only to Excel. Special Sort’s "decimal" option sorts each part of a value (any number between decimal points, for example) in true numerical order (so you don’t have to re-enter 1.2.1 as 1.02.1). With Special Sort, 1.2.1 comes before 1.10.1, just as you’d expect.

Special Sort uses the same logic to properly sort numbers when you tell it to skip initial text in a cell, so "Part 123" follows "Part 45".

Special Sort in Action - Click to enlarge

You can sort by the color of the cell or the font used in that cell. Other options include sorting by date (month, day, year, day of week), text length (short words sort before longer words), prefix (characters to the left of a separator character, such as a dash, determine the sort order), the first "n" characters, or the suffix (characters after a separator, or last "n" characters).

Special Sort is also smart about cell borders. In standard Excel sorts, the cell borders remain unchanged after a sort. In Special Sort, check the "Include Borders" option and the cell borders follow the data. That is, if a cell containing "1" has a red border and a cell containing "2" has a blue dotted border, those border properties follow the cells after the values are sorted. The author points out that for large ranges, this can slow down the sorting operation, but a progress bar will appear if the sort range is very large. We tested its speed by sorting a 10,000 row column; without including borders, the sort was nearly instantaneous. With the option turned on, the operation took a mere 40 seconds.

While you’re learning what each item in the "Sort Type" list means, you can double-click on an option to see an example. When there are further options to set, Special Sort makes it clear how to set them (the illustration shows how you set the color order for a color sort). The program also offers an "Undo" option for setting your data back to its pre-sorted order.

Special Sort is distributed in a rather unique way. You first contact the author and ask him to send you the program. If you like it, you arrange to pay for it (using PayPal, for example); if not, you promise to erase the add-in from your system. You’ll find the details at http://www.realezsites.com/bus/primitivesoftware, along with a host of free Excel add-ins that do everything from printing multiple selections one above the other to shading rows.

Excel’s built-in sort feature is fine for simple sorts. When you need more power, Special Sort is a great solution.

-- James E. Powell

^ Top of page ^

^ Top of page ^