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
Greetings folks. Everyone, thanks for the questions and comments. Appreciate your feedback and thoughts.
Graham, Simon, thanks. Simon, unfortunately, no changes have been made to our cell precedents features this time out.
Ian, being a product team guy that doesn’t run the beta program, the best I can do for now is give you all the information I can via this blog!
Graham, there has been an overhaul of the conditional formatting object model (to go with the new features), and I will try and sneak in a post about that this week if I can find the time. In general, where I talk about features, you can assume we have done the matching object model work. To answer your other question, no there has not been major change to VBE in this release. Our cross-Office programmability team has done some work for all the applications – for example, a nice programmable task pane – and I will make sure I have a topic on the work they have done at some point in the future.
Biff, I understand exactly what you mean, but no, we did not do any work in this area this release.
Vlado, let me double check before answering your question. I want to make sure I give you accurate information.
Andy, we tried to give users a lot of options in designing these new features, so you should be able to set things up exactly as you like.
Helen, thanks for the feedback. While we haven’t done exactly what you are suggesting, we have made it a bit easier to see the conditionally formatted range from the rules editing UI (more this week), and we have done some work around dynamic named ranges too to make them easier to use in formulas (more on that when I talk about Tables in a few weeks). Stay tuned!
Jon, yes, you can use a formula to control any of the new conditional formats.
Stephen, when I talk about the new UI this week, you will see we give you a couple of choices of colours by default, so feel free to give me more feedback later this week.
Harlan, we haven’t added new chart types this release, although we have done a huge amount of work in the area of charting. Specifically, charts now look superb & are much easier to create and edit, we have one unified charting engine across Excel, PPT, and Word, and PivotCharts are much more fully featured. Much more about charting to come …
David Gainer...
...
|Harlan, . . . we have one unified charting
|engine across Excel, PPT, and Word, and
|PivotCharts are much more fully featured.
One charting engine is good.
Will there also be one find & replace engine, so Excel, PowerPoint, Outlook and (dare I hope) Access would all be able to use the extended wildcards (will you finally call them regular expressions) Word has provided since, what, Office 97?
Damn, I wrote a cursory VB script to do this when I was an intern at GEICO in high school! I was sitting on a GOLD MINE!
Harlan - Our work on find and replace this version was around making sure it works well for the bigger grid.
I'm sorry, but should I just cream my pants now or wait until later in private? You are my hero. Gradient color coding, PDF conversion, sexiness all around, my job is going to get significantly easier. I give you 15 hearts of pure joy.
OK, great work on the formula bar. At last. Now I can see my cells. What a relief.
Now you only have three left of the four improvements that have been obvious since the early nineties to any Excel user with a 3-digit IQ.
2. Get 4 million rows and 1024 columns, so you beat this baby: http://www.jps-development.com
3. Make the data forms on the Data menu resizable. The fix size data forms look exactly the same in Excel 2003 as they did in Excel 4.0 back in 1992.
4. Enable multi-row worksheet tabs, so you can raise the 31-character worksheet name limit to 64 (at least) and let users view more tabs at once instead of using those pathetic VCR buttons.
Mats Hindhede