User interface for sorting a table by multiple columns

Question

I need a user interface that allows users to sort a table according to multiple columns (e.g. sort by color and then price within color, or alternatively price and then color within price). The only such interface I'm familiar with is the dialogue box found in Excel under data > sort, but this is rather clunky and does not yield itself to quick switching between views. I would much prefer an iTunes-style interface that allows quick sorting by clicking on column headers. However, such interfaces typically only allow sorting by one column (an exception is iTunes itself which has a very limited, apparently hard-coded ability to sort by "Album by Artist" and "Album by Year" by clicking on the Album header).

I can envision an interface where each column header has some numbers, such that clicking on 1 makes the column the primary sort key, clicking on 2 the secondary key, and so on. Alternatively, clicking (or right-clicking) on a column header could bring a drop-down menu with "primary sort", "secondary sort" etc. However, I've never seen such an interface implemented, and I don't have a good intuition of usability issues that may arise.

Are there applications that allow sorting by multiple columns using the column headers? Would you be able to point me to these? Are there any useful usability results regarding such interfaces -- which work better, which less so?

Also, while I am mostly interested in the specification of the interface, any hints to pass on to the people implementing it would be appreciated, e.g. publicly available libraries that provide parts of a solution (especially Java).

Edit: Two people have suggested using an Excel-style dialogue box. This is not going to work. For my application, users need to find the "best match" among existing table entries (which is often not a perfect match). The table is too big to keep in your head, so you need to keep scanning the relevant parts, and it's useful to repeatedly sort the table in order to get multiple views. Having to go through a dialogue box with multiple options for each change of view is just too slow; by the time you're done with the box, you've forgotten the results from the previous view.

Answer 1

Have you taken a look at Excel? That's a perfect example on how to sort on multiple columns.

Also, I use a detailed listview sometimes, and let the user hold the Ctrl key while selecting one or more columns to sort the info. (Clicking twice on a column does a descending sort.)

Answer 2

I think Outlook supported sorting by multiple columns. After you clicked a columnheader, you would then shift-click additional column headers. I no longer use Outlook so I can't verify this. Hopefully it will be a starting point for you.

Answer 3

Seriously, what you want, Excel does. Why not use an interface people already know how to drive. I would suggest that. Or else you're going to either have to buy from a third party, or roll your own.

Development with Office is facilitated by VSTO (Visual Studio .NET tools for Office). It's up to you how much you want to automate Excel within an inch of its life :). It can be done.

In fact that is what I had to do. I had to read spreadsheets, extract data, convert to objects, then persist to Access (don't ask). Each sheet represents one record, and each directory represents a location, you get the idea. All done using the Office PIA Interop assemblies.

Just a thought.

Answer 4

I've seen the shift-click interface that caparcode mentioned in a few applications, however I can't name any right now. Here's a nice web-based example though.

Answer 5

I would suggest using a Ctrl-Click (or Shift-click or whatever-click) approach to let the user select multiple columns. Clicking without Ctrl will just sort by the selected column, but holding the Ctrl-key adds the column to the sorter. Clicking again changes the direction. And give a feedback about the current sorter(s) similar as in these pictures. Most of the time the users will only use the single column sorter anyway, only advanced users will want to sort by multiple columns. But you should have the behavior documented somehow in the manual and in the "did you know" thingy.

But since users don't read, there is the risk that nobody will find the feature. But for this maybe you could have a feature like Windows when first started wanting you to click the start-button once to ensure you found the button. When you see the user not using the sorter at all, show him that clicking the column header will sort. Later, when you see he's not using the multi sort feature show him once that the feature exists.

Answer 6

How about the following? A single droplist labeled “Sort” in top margin of the table. The dropdown lists all fields to sort. Each field appears twice, once for ascending and once for descending sorts. Beside the dropdown is a button labeled “More” or perhaps simply “+”. The user chooses the field for the primary sort key from the dropdown. The sort is instantly applied (no “Sort” command button). If a secondary sort key is desired, the user clicks the More button and another dropdown list is inserted and opened for the user to select the second key. Additional lower order keys may be added with successive clicks of the More button. Dropdowns each include a “Clear” item to delete a key.

This makes a compact, simple, and uncluttered UI for the simplest and most common case of a single sort key (unlike Excel’s Sort Dialog or having numbers to the column headers), while also supporting the sorting an indefinite number of keys (again unlike Excel’s Sort Dialog). The user can see the sort order at a glance (unlike Excel and more easily than with numbers in the columns). It avoids the clunkinest of a dialog box.

Clickable column headers is a de facto standard that is also a good idea to include along with the above. It’s good practice to do what Outlook and Windows Explorer do and make lower order keys out of former sort orders. So, for example, if the table is sorted by date and the user sorts by category, then the table is sorted first by category and then by date. A user can thus do multi-ordered sorting by picking the lowest order sort field first and working up. However, this has poor discoverability and user may find that working “backwards” is counter-intuitive, so it should be supplemented with something like the dropdowns and More button. Relying on shift-clicking or ctrl-clicking likewise has discoverability issues.

Answer 7

Do it the way Excel does, or rather the way real people use Excel, which almost never involves that clunky dialogue box which you rightly wish to avoid. Basically, Excel preserves your current sort order as much as it can - so if you currently have it sorted in descending order by price, and now you sort by color (using the A-to-Z button on the toolbar, NOT the dialogue box), the prices within a color will remain in descending order.

This approach does require a bit of reverse thinking from the user, because you sort first by the least important column, but it is very intuitive once you get it. (Yeah, I know, that sounds self-contradictory, but I don't know how else to describe it.)

To implement it, you'd have to store the user's sorting history somewhere; but as far as the interface is concerned, all you'd need is an up arrow and a down arrow in each column. Or, if you only want to allow A-to-Z sorting, just make the column headings themselves into links or buttons or whatever.