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.
Tips
How-to
News
Videos
Stories
The second new visualization that we have added to Excel 12 is something we are calling “colour scales” (again, that may change later when we finish official feature naming). It shares a lot with data bars as described in a previous post – it is a comparison between a selected range of cells, it uses visual effects to communicate the results to users, and it is just as configurable as a data bar with respect to setting colour and thresholds. So how is a colour scale different from a data bar? A colour scale uses cell shading, not bars drawn in the cells, to communicate relative values. This is extremely useful when you want to communicate something else about your data beyond the relative size of the value of a cell – for example, if high numbers are good and low numbers are bad, or if you want to understand variation in your data, colour scales are a great choice.
Let’s look at an example. Say you have some data about investment returns (note that all this data is completely pretend stuff I just typed in myself for illustrative purposes):
(Click to enlarge)
If you select that range and apply a colour scale, the range now looks like this:
It is now easy to see good returns, weaker returns, trends, outliers, etc., where red is bad, green is good, and yellow is in the middle. This works in a similar fashion to data bars - Excel is comparing the values in each of the selected cells and assigning a background colour based on a cell’s value relative to all other selected cells. The colours provide clear information to users. By default, when you apply colour scales with one click, Excel uses the lowest, highest, and midpoint values in the range to determine the colour gradients. As with data bars, you can specify the values that determine what colours to use – you can tell Excel to use the lowest/highest/middle value, or, for each of the colours, you can specify a number, percent, percentile, or formula. Here is a shot of the (not final) UI to change settings on colour scales:
(Click to see list)
Colour scales are a good example of why it is great to have Excel 12 support 32-bit colour – you can see that we have an almost infinite ability to vary the background colour of a cell between red, yellow, and green (or whatever colours you choose). Excel 12 will offer colour scales that support 2 and 3 different colours. Since we have already seen a 3-colour colour scale, let’s finish the post with a picture of a 2-colour colour scale. In this example, in one case we are moving from green to yellow, and in the second case I have set the lower colour to be white, so it appears to the user that the intensity of the blue colour increases as the value of a cell increases.
Hope this is interesting information. Next time, icon sets.
Comments: (21) Collapse
This is getting to be addictive, David.
Thanks - and keep it coming!
David
This new formatting stuff is very impressive, I can see lots of uses for it.
Will Cell.precedents (or something similar) include depedencies that drive conditional formats? currently you have to parse the conditions manually to work out whats driving the formatting. And while I'm on the subject will cell.precedents include off sheet precedents?
Actually have you revamped the auditing and error reporting tools?
Cheers
Simon
David,
This is great but your just being a big tease.
Drop a link to a beta and let us all have a play!
***...
David, it's obvious that this new functionality must be coming with a substantial revision of the excel object model. But I'm sure we're all eager to know whether there's going to be any evolution to the VBA language itself, or any major changes to the VBE? Is this a topic you plan to post on before too long?
Thanks, Graham
One of my biggest pet peeves about applying conditional formatting is the teenie, tiny input box where you place a formula. It is a real pain if you have a rather long formula and need to edit it for some reason. Not being able to see the entire formula makes it more difficult to edit.
I've pretty much resigned to writing the formula in a worksheet cell then copy/pasting into the input box. This is really an unecessary step.
Please tell me (us) that these formula input boxes have been enlarged so that we can see the entire formula!!
The same applies to all the formula input boxes on all of the UI forms. (data validation, defined names, etc)
Thanks for taking the time to read and respond to all the questions. It's not everyday that the average user has the ear of someone from Microsoft!
Biff
Wiil it be able to run Excel 12 (Office 12) on older versions of Windows, which versions ?
These formats looks great. I think having a full spectrum graded format is confusing: once you introduce three colours, how do you know which is "larger". Does GREEN indicate better, or is it RED. Hmmm, there's even some YELLOW.
However, two colours is excellent, and with the customisation, one can forget about the three colour option.
Really enjoying these blogs, it's all pretty exciting.
Another excellent feature. Up until now we have had to code a similar approach to doing this. If you are looking for a new name, we call the resulting coloured-in range a 'heat map'.
Nigel
I am looking forward to hearing more about the dreaded Tools/Options dialog and specifically what changes have been made to things with regards to the various Application, Window, Workbook & Worksheet wide settings and how they interact with each other. i.e. Are the days of opening a workbook last saved with Calculations set to Automatic and having it affect the entire application and all other open workbooks going to be history? (Among the many other frustrating interactions.)
Thanks again, and keep it coming,
Excellent stuff, I've been doing the exact same thing manually for years, laboriously tweaking the cutoff levels for the 3 different conditions to get a good distribution between the 3 different colours of blue we use at work.
We call them heat maps, too.
Three colours is definitely good - for example if you use blue for negative temperatures and red for positive, you'd want 0 to be white and not purple.
Another wish regarding conditional formatting: give us feedback on conditional formatting formulas. When you edit a formula (for a dynamic named range) in the "Insert name" dialog, Excel marks the range defined by your formula, so you can see whether it's right. The conditional formatting should show what the formula evaluates to for the currently selected cell.
This is a great visual aid, kudos to the guys who thought this up ;-)
Can we use a formula to control the gradient, as we can with the current conditional formatting (which now seems so inadequate)?
Like a fairly large percentage of the world's male population, I'm mildly red/green colour-blind. In your second image, the cells that stand out depend on how lazy my eyes are, whether I look straight-on or obliquely, or how much I concentrate on it. To me, there doesn't seem to be much to distinguish the cells - it's just a big reddy-greeny mess. *Please* don't use this green/yellow/red as a default!
Stephen, I am sorry about your color perception. Yet I'd say it's a vast majority of the business community that use the green/yellow/red as a default notion.
What, no variable rates for blinking text?
Seriously, visualization is nice. Will Excel provide box and whisker plots, 3D scatter plots (XYZ plots), contour plots, pairwise scatter plots? Will text values in chart series still be treated *always* as zeros?