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
Yesterday I wrote about some work I was doing with Tables recently. Today I want to do the same for conditional formatting – specifically, using colour scales. (For a refresher, or for those that are new to this blog, you can read up on changes to conditional formatting in this series of blog posts, and you can read specifically about colour scales here).
In this case, I was looking at a table that contained the results of a set of tests. The table looked like this (again, I made up the data for this blog entry).
(Click to enlarge)
Essentially, we run a bunch of scenarios against a series of tests periodically and look at the results. 0 is ok, positive numbers are good (the higher the better), and negative numbers are bad (lower means worse). Seemed like an ideal candidate for a colour scale. Using a button that I had added to my Quick Access Toolbar, I applied a colour scale to the table. Here is the result.
As you can see, there are a couple of problems. The biggest one is that there are obviously some outliers on the low end of the scale that mean that everything else looks good (remember, that in the default application, the 3-colour scale uses the lowest point, the 50 percent mark, and the highest value to assign colours … if there is an outlier on either end of the data, it can lead to results like this). You can really see the problem if you click on the image above. So, to fix that, I made some adjustments to the rule. Using the manage rules dialog …
… I made a couple of changes to how the rule was going to be evaluated. First, I changed the midpoint from 50% to a number, and set that number to 0 (since above 0 was good and below was bad). Second, I used a formula to determine the value for the minimum (lower bounds) colour to eliminate the effect of the outliers. Specifically, I changed the minimum to be of type “formula” and entered this formula, which simply determines what the value is of the 5th percentile: =PERCENTILE($C$3:$I$52, 0.05). (Choosing the 5th percentile is not terribly scientific, but I had seen this report enough to know that there were never more than one or two outliers, so using the 5th percentile should handle them).
So now my rule ran something like this – “turn -1.36 (what the formula calcs to in this data set) and everything below it deep red. Everything between -1.36 and 0 is a gradient fill between red and yellow. Everything between 0 and the highest value is a gradient fill between yellow and green. The results look something like this:
That was better, but still not what I wanted, since the effect was pretty busy. So, I decided to make one more change. Once again I opened up the Edit Rule dialog, and this time I changed the colour for the midpoint from yellow to white.
Once I hit OK a couple of times, the result looked like this …
… which is pretty much what I wanted. Values close to 0 are mostly white, so they are not called out as good or bad visually, the outliers on the negative side (probably measurement problems) are not skewing the results, and there is good gradation on both sides, allowing me to quickly eyeball around for things to investigate.
Hopefully that was interesting – the point being that by tweaking the rules themselves, there is no end of possibilities available with conditional formatting.
Comments: (26) Collapse
Huh? What has happened to your Ribbon(tm)? I seem to remember that it was about three times as high.
Assume I have tabular data, and I want to paint in red an entire row when one cell (say, in the first column) of that row meets some condition. How do I do that? AFAICS, conditional formatting is per cell only.
Seeing that huge quickstart bar, I take it the own food doesn't taste good enough? ;)
ZivC - you use the "Formula Is" function of conditional formatting, available in all conditional formatting (or at least back to Office 2000, that I know of).
For all the cells you want to format based on one cell, you would use "Formula is" and return TRUE or FALSE based on that formula, using an IF, or OR or AND, etc.
e.g.
highlight several cells in a row to be colored based on column A and apply this "Fomula Is" conditional formatting to all cells
=IF($A1=1,TRUE,FALSE)
you can drag or copy/paste this formatting down all the rows, and all the rows will change format according to the values in column A
That still looks a little busy to me, even with the white. I like the bar-graph backgrounds a bit more.
On that note, is there a way to apply custom formatting to a cell based on the value of another cell?
And, I guess you prefer a large quickstart bar to using the ribbon? Any particular reason? How convoluted is doing all this with the ribbon, instead of the quicklaunch?
I don't understand why all the border types aren't available in conditional formatting, as well why can't things like text be set?
joe, a normal =$a1=1 would work as well because it evaluates to true/false by default. the if becomes redundant
One set of gotta have QAT icons for CF, another set for external data, another set for pivot table operations, another set for drawing, . . .
Since the QAT can't extend to multiple rows of icons, kinda looks like there's still some apparent need for old style toolbars.
Maybe the ribbon is a better menu, but that's all it is. Toolbars served a different need. Too bad Microsoft decided to fix something that wasn't broken.
As for the example images, you've redefined UNprofessional. How about an icon of a dog food dish for the dog's lunch formatting option?
When you conditional format a range with the icons, can a legend be displayed automatically that shows what each icon represents?
It's the ad-hoc choices that you're making about the boundaries for the different conditional formats that worry me
If you have some knowledge about the distribution of the underlying data points - such as Normal - then it would be much better to calculate the mean and standard deviation of the sample points and then normalize the data points before applying conditional formats
Mike, I think David was showing ad-hoc analysis of data, so that was the whole point.
Harlan, David stated that he is a heavy keyboard user. What is your problem with that? (beside trolling)
Leta, how is the size of the QAT related to keyboard use? Or do you mean that David's heavy keyboard use explains the ugly conditional formatting?
If Excel had 3D contour plots, color-coded CF as shown above would be unnecessary.
Superphilipp, Mario, Kdbertel, what you see in the post with respect to UI is the fact that I added some buttons to the Quick Access Toolbar and have collapsed the ribbon. Since I use mostly keyboard shortcuts, I find that for most work, the shortcuts and a number of items on the Toolbar are sufficient, and for the odd time where I need the Ribbon, it is one keystroke (CTRL + F1) away. This setup also gives me more space for cells on my monitor than Excel 2003. None of this is a comment on the Ribbon – simply that this is the way that I work.
To answer the other question asked in this area, things are no harder or easier with the Toolbar relative to the Ribbon; it is simply a matter of personal preference.
ZivC, I think Joe answered your question.
Kdbertel, yes, you can now apply number formats as part of conditional formatting, which includes custom number formats.
MarkD, we enabled custom number formats this time around. If there is a lot of demand for borders, we will take a look.
Brent, you would have to add one manually (add the icons to some other cells and type text). Good feature idea for the future.
Mike, I wholeheartedly agree.
"To answer the other question asked in this area, things are no harder or easier with the Toolbar relative to the Ribbon; it is simply a matter of personal preference. "
Well how about MS respecting this "Personal Preference" and giving me the "choice" to use the Classic UI or the Nasty Ribbon
Sam
David: Yes, Joe and Mike answered my question (thanks guys). I'm disappointed with the answer, however. Isn't the whole point of the ribbon to make such features easy to find? In my personal experience, it's far from obvious.
ZivC - i was answering how to use "Formula Is" in Excel 2003 and below. i haven't tried to use it in Excel 2007 yet.
I agree that the "Formula Is" functionality is hidden, and hard to use - it usually takes me a couple tries before I actually get what I want. i have no idea if this particular feature is improved or more visual in Excel 2007. I don't think the ribbon addresses this issue - maybe the ribbon will get you to use Conditional Formatting better, but other design features will have to be implemented better in order to show people how to use CF more effectively.