Back
Excel

Tables Part 5: Everything you wanted to know about sorting in Excel 12

I mentioned in my previous post that the three-condition limit on sorting (Data|Sort) has been removed in Excel 12.  As someone correctly pointed out in a comment many posts ago, this means the current sort dialog has changed in Excel 12.  Here’s what the new dialog will look like (as always, all the details aren’t finalized, but the key features are clear) if you were in the middle of trying to sort a table by 5 columns:


(Click to enlarge)

The dialog is similar in behaviour to the Conditional Formatting Rules Manager discussed in an earlier post.  To create sort conditions, users just need to just click on the “Add†button – users can now sort on up to 64 columns – and specify what they want the sort criteria to be.  Just like with filters, sorts are smart about data types so we use that information to offer settings that are more descriptive and easier to understand than “ascending†and “descending’.  For example, for text columns you will see “A to Z†and “Z to Aâ€, for numeric columns you will see “smallest to largest†and “largest to smallestâ€, and for date columns you will see “newest to oldest†and “oldest to newestâ€.  Sort conditions can be reordered using the buttons at the top of the dialog.  Sort conditions can also be copied to save time.  The Options button allows users to specify whether the sort should be case sensitive and allows users to specify the sort orientation (both of these features exist in Excel 2003).

To see a bit more of the new functionality we have enabled in Excel 12, let’s take a look at another example.  Assume I have the following table which contains both manually-applied and conditional formatting (ignore the data – I used RANDBETWEEN() to generate that).


(Click to enlarge)

One of the common requests we hear from users is the desire to sort by colour, either manually applied or applied by conditional format.  In Excel 12, we bring you exactly that – in addition to cell value, you can sort by

  • background colour (however applied)
  • font colour, (however applied)
  • cell icon (applied via conditional formatting). 

For example, I might set up several conditions on the table of data we just looked at …


(Click to enlarge)

… and when I press OK, Excel will apply the sort appropriately.  Notice the filter buttons again show me the state of each column.


(Click to enlarge)

I want to mention two other improvements that we have made that I think certain people will really appreciate.  First, we have made it possible to specify a sort order of “Custom List†at every level of sorting.  Second, we have made it possible to create a new custom list from within the context of the Sort dialog.


(Click to enlarge)

We have also added the ability to sort by colour or cell icon to our in-grid filter capabilities (nee AutoFilter).  Say I started with the following table …


(Click to enlarge)

… and I wanted to see all the yellow and red items at the top.  I would simply use the sort/filter button to sort first by red …


(Click to enlarge)

… which would group the red values at the top …


(Click to enlarge)

… and I would repeat for the yellow items, which would leave me with a table that was sorted like this.


(Click to enlarge)

Essentially, what sorting by color does is move all rows that meet the criteria all the to the top.  What follows after is not specified – what matters is that rows with a certain format are moved to the top.  For convenience sake, we have also added this capability to the context menu in a table or Filtered range.  For example, in my table above, I could have just as easily right-clicked on a cell with red fill and selected “Sort by this cell’s fill color†in the “sort and filter†submenu.


(Click to enlarge)

That about wraps up my overview of sort and filter improvements.  In my next post I will talk about a new feature that allows easy removal of duplicates from a table of data.

Update to post to clarify sorting by multiple colours concurrently

I want to throw in one more example to illustrate how it is possible to sort on multiple colours at one time.  For example, if you had a table with a bunch of formatting on one column and you wanted to sort the table by that formatting …


(Click to enlarge)

… you would simply bring up the sort dialog and specifiy the order of colours that you wanted to sort by …


(Click to enlarge)

and press OK.  The table would sort by the conditions you specified.


(Click to enlarge)

I hope that makes sense and clarifies things.