You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
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).
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
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.
Comments: (17) Collapse
Good. You're taking the better ideas from both Gnumeric (which beat you to the simpler dialog for specifying sort keys, and seems to allow one sort key for each column in the selected range rather than imposing an arbitrary limit of 64) and OpenOffice Calc (which provides custom lists, though only one per sort, and allows the sorted result to be copied elsewhere). FWIW, Lotus 123, like Gnumeric, allows all columns in the sort range to be sort keys, so no arbitrary limits.
Both Gnumeric and OpenOffice Calc provide options to sort formats with values or not. Will XL12?
To those who're going to call me a spoil sport: I'm very glad Microsoft is making this improvement even if the evidence of what their competition has managed would seem to imply that they could have done much more.
As for 'sorting' by color or icon, if you mean grouping, then say grouping. 'Sorting' means *all* colors sorted together, so all reds together, all yellows together, all greens together, all whites together. If users can supply custom sorting lists for text, why not allow them to specify custom sorting lists for colors? Start off with a scrollable list of each distinct color used in the range sorted by RGB index and allow users to reorder them by drag & drop. IOW, if all you're providing is grouping by one color at a time with all as yet ungrouped colors remaining unsorted/ungrouped, why would a user 'sort' rather than filter?
Hi Harlan. I do not understand your question about "sort formats with values". With regards to sorting with colour, you can absolutely sort a number of colours at the same time - see the sort dialog example above.
I like every sorting features you explained. Even if there are more possibilities, it seems intuitive to use.
I like also Harlan's idea
| If users can supply custom sorting lists for
| text, why not allow them to specify custom
| sorting lists for colors?...
This seems easier to use.
David, would the 2 steps you showed to Sort by Color be reflected in the sort dialogue box (the same way of your Cell Icon example) ?
Jean
Everyone - I added a bit to the post to provide an example of sorting by multiple colours at once.
Harlan/Jean - custom colour lists is an intersting idea, thanks for the feedback.
Jean - yes, the sort dialog box would reflect the 2 steps.
David Gainer...
| . . . I do not understand your question
|about "sort formats with values". . . .
Excel's current sorting semantics with respect to formatting arguably make sense, but can be unexpected. With _'s representing bottom borders and *'s representing boldface text, Excel would sort the initial range
*1*
_*4*_
2
_3_
in ascending order as
_2_
3
that is, sorting boldface with the values but leaving the borders in original locations. OpenOffice and Gnumeric, sorting formatting with cells, would have produced
that is, moving all formatting attributes with the values. It's nice to be able to choose, and you're the one claiming that users should always be in control.
| . . . With regards to sorting with colour,
|you can absolutely sort a number of colours
|at the same time - see the sort dialog
|example above.
It seems by making each separate color a separate sort key. So if a user has 5 standard sort fields and 60 distinct colors to sort by, they're out of luck since XL12 provides only 64 sort keys? Ditto if there are more than 65 distinct colors.
Is there at least helpful functionality that if the same column (or row) is selected multiple times as a 'sort key' by color or icon, that colors or icons selected in higher precedence sort keys are excluded from the list of colors or icons presented as options for the lower precedence instances of those columns (or rows)? Or do users get to sift through the full set of colors or icons for each 'sort key'?
I think you are missing a trick with sorting by colour. Just as the order of numeric values, text or dates is straightforward (1 is less than 2, A comes before B and January before February) so colours have a natural progression based around the colour wheel.
Using the HSB colour model rather than the RGB model more familiar in computing, each unique colour is represented by three values (Hue, Saturation, Brightness). For the purposes of sorting by colour, it would be the Hue value that should form the basis of progression.
The Hue value is a number between 0 and 360, representing a colour's position on the colour wheel. There are 360 degrees in a circle and on the colour wheel red is at the top, i.e. 0 degrees, green is a third of the way around at 120 degrees and blue is another third of the way around at 240 degrees. Other colours are placed relative to these on the colour wheel. For example, in additive colour mixing yellow is made up of an equal amount of red and green and is therefore found midway between them on the colour wheel at 60 degrees.
Excel holds colours as RGB values but there is a relatively simple formula for converting RGB values to HSB. I would ask that, at the very least, the default sort by colour should arrange the column in order of Hue such that one click would give a logical and natural colour progression down the column.
"Or do users get to sift through the full set of colors or icons for each 'sort key'?"
I agree with Harlan. On large amount of data, the way it is currently done will be not very user friendly.
What about having, instead, a single sort key entry 'Cell Color' that display a list of all the colours used in the column, and provide for each colour small 'move down' and 'move up' buttons that allows to define the sort order?
Jean-Marc
Pardon my forgetfulness, David. You mentioned in Table session that sorting will be automatically done when data changes within a table (provided that sort is already defined), right?
>>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 –
"not specified"? Can we specify that the relative positions of what is left do not change?
Some responses.
1) The HSB works, but you would have to be able to rotate it since you may not want to start with the value at H=0.
2) If you do allow custom color lists, allow for direct manipulation (i.e. drag and drop). Nothing irritates me more than having to click: Move Up, Move Up, Move Up, Move Up, etc.
Orion,
"Nothing irritates me more than having to click: Move Up, Move Up, Move Up, Move Up, etc" - Maybe there should be a 'Move up ten times' button then ;-) Just kidding. Btw, I don't pretend to be a usability expert.
I take your point. Give me the chance then to reformulate my suggestion: " What about havinga single sort key entry 'Cell Color' that display a list of all the colours used in the column, and provide the ability to reorder the colours box by drag and drop to define the sort order?"
I suggest that because on my side the issue I see with custom list for colours is how you will guess the names of the colours, especially when there will different kind of blue or green? Is that blue named cyan or light blue or seagreen ?
Howdy everyone – again, thanks for the feedback and ideas about colour sorting.
Tianwei – We will not automatically sort the table, but we now provide a button to re-apply a filter on a table (so if you have set ten conditions, you don’t need to re-set all 10). See the last picture in the "Tables Part 4: AutoFilter improvements: much more than just multi-select …" post.
Jim – That is what happens – the relative positions do not change.
I'm not sure that this is the place to put this post, but I can't seem to contact David any other way!
My company internet filter is blocking all the images from your blog David, claiming that they are related to 'games'.
Is there any chance of removing any game related meta data from the pictures or the server they're on?
I know it's a big ask, but I've been loving the posts so far and it's a shame to lose the pictures which add so much to the blog!
My Employer uses Web(non)Sense.
That too (not the same one is it) blocks all the images now. It didn't two weeks ago.
Actually, they block pretty much everything else useful too now.
M
1) The aspect of sorting I come up against is lacking a "recent sorts" option, especially one linked to a specific (last used?) range
Generally the range I'm sorting does not change but I want to sort with different columns and in different orders.
Mpemba Snow
Comments: (loading) Collapse