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
A few months ago, I described the new features we have added to Excel 2007 in the area of conditional formatting. One of the new formats we added is called a “data bar” … check out this earlier post for a refresher, but the basic idea is that Excel draws a bar in each cell representing the value of that cell relative to the other cells in the selected range. Here is a shot from that post.
(Click to enlarge)
The Excel 2007 UI allows you to choose whatever colour you want for your data bars, but, by default, all the data bars you apply to a range have to be the same colour. Someone on our team recently showed me how to use a tiny bit of VBA to simulate having multiple colours of data bars on a range conditionally applied, so I thought I would pass along the trick.
Say, for example, you are looking at student grades, and you want all the data bars for students with a passing mark (60%+ and above) to be green, and those with a failing grade (59% or less) to be red. The first thing you would do is to add some red data bars to your data, and then some green data bars. By default, Excel shows you the last set applied, so the data bars would be green. If you then launch the VB Editor (Alt + F11) and in the immediate window (Ctrl+G), type:
selection.FormatConditions(1).formula = "=if(c3>59, true, false)"
You would see that your data now looks like this, which makes it easy to spot the failing grades.
So how does this work? Every conditional format has a Formula property, which allows you to specify a formula which determines whether the conditional format is visible. In this case, we are simply saying that the green data bars (the most recent ones) should only be visible if a value is greater than 59.
This property is available on all conditional formats, so I expect that users will find all sorts of creative uses beyond just this case.
Comments: (13) Collapse
It would nice if the example above could be part of the dialogue box (for data bars).
Pretty slick that you can combine conditional format modes.
ps. typo on the topic mulit<>multi
I was thinking the same thing, although the VBA code is trivial. However, we're back to the feature discoverability problem. I suspect that many average users will want to create a conditional format similar to the one described but won't have a clue how to do so. Colin
Interesting... would there be anyway discoverable (read: code-less) way to do this?
Maybe some combination with Color Scales?blogs.msdn.com/.../477948.aspx
(Also, on the topic of color scales, can that color scale editor become more like gradient editors in applications like Photoshop? It would be nice to be able to drag to adjust gradient points and add colors)
Does not seem to work very well in beta 1. When running the code in the immediate window, I get an automation error, "disconnected client". And then the selection in Excel does not work anymore.
This is a great trick. I would keep it in VBA for this release - the entire formula in the current conditional formatting is hard for people to learn. If you added it to the dialog box, it would overly complicate the new feature.
For the Power Excellers who want to show off, they can easily go to VBA to set this amazing trick up.
Hi
Rob, thanks for spotting the typo.
Brandon, good feedback. For XL2007, the design is pretty much set.
Mike, there is a bug in beta1. Will be ok in the next build you get.
All, we thought about putting the property in the mainline UI, but it seemed to confuse people more than help.
Off specific topic but it is on the general topic of Excel......
My full version: Excel 2002 (10.6501.6735) SP3
I've been having a problem with Excel crashing. It's happened for a few years now and since I have this opportunity for someone from MS to "hear" about it, here goes!
Whenever I use Formula Auditing>Evaluate Formula on "complex" array formulas like this one:
=IF(ROWS($1:1)
Biff: Updating my Office application NEVER has fixed this problem!
I tried your formula in Excel 2003 and it evaluates fine. Maybe you need to update you Office application after all.
"I tried your formula in Excel 2003 and it evaluates fine. Maybe you need to update you Office application after all."
But I have: My full version: Excel 2002 (10.6501.6735) SP3
As recently as tonight!
Toffu: I tried your formula in Excel 2003 and it evaluates fine. Maybe you need to update you Office application after all.
You are conflating update with upgrade.
Biff is keeping Office 10 updated. Like Biff, I would like to see an answer that doesn't involve upgrading to Office 11 (2003).
tj
I share a problem just like discussed by Biff.
(My version Excel 2003-SP3 Calculation Engine 114210 )
Whenever I use the any Array Formula like
SUM(($H$4:H715>=$AG$1)*($I$4:I715<>"Swi")), and I use Auditing>Evaluate, excel crashes everytime at evaluating the formula.
Thank you David for the Great Work. Hopes to listen on "Validation" soon
Kanwal -
You can use a normally entered Sumproduct formula which will evaluate just fine:
=SUMPRODUCT(--($H$4:H715>=$AG$1),--($I$4:I715<>"Swi"))