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:
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).
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 â¦
â¦ and when I press OK, Excel will apply the sort appropriately. Notice the filter buttons again show me the state of each column.
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.
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 â¦
â¦ 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 â¦
â¦ which would group the red values at the top â¦
â¦ and I would repeat for the yellow items, which would leave me with a table that was sorted like this.
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.
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 …
… you would simply bring up the sort dialog and specifiy the order of colours that you wanted to sort by …
and press OK. The table would sort by the conditions you specified.
I hope that makes sense and clarifies things.