More Conditional Formatting Features in Excel 2010

re: More Conditional Formatting Features in Excel 2010

J.R. — Mon, 07 Feb 2011 03:50:15 GMT

I'm new to 2010, came from 2003. I have a spreadsheet where I WAS able to apply up to 3 rules per cell, ex: if the value in the cell was below 100 highlight it green and above in red. I am unable to see in 2010 how to do that. All I've found so far is to apply 1 rule. How do you apply more than 1 rule to a cell in 2010?

re: More Conditional Formatting Features in Excel 2010

Shane Devenshire — Sun, 06 Sep 2009 23:21:57 GMT

Regarding BEL8490's comment in the Icon Set Improvement blog regarding creating a legend. How about providing a tool tip like in the PivotTable area where you tell us the where every cell gets its value (column and row label fields) and have those in a condtional formatting range to provide backup equivalent to a legend. The problem of course is that you can apply an unlimited set of condtions but since there appear to be fewer options for the icon sets and possibly data bars, this might be helpful.

re: More Conditional Formatting Features in Excel 2010

Shane Devenshire — Sun, 06 Sep 2009 23:09:43 GMT

A comment for the previous post on data bars, can't post there because its closed.

You can put the text in one cell and the data bars in another cell in 2007, which helps solve one of the discussed problems.

Suppose A1:A10 contain your values

In cell B1 just enter =A1 and copy down.

Set data bars on for column B and turn On the Show bar only feature.

re: More Conditional Formatting Features in Excel 2010

Mike — Thu, 20 Aug 2009 21:49:17 GMT

Just saw this. I like the improvements.

One question. In 2007 when I have a conditional format applied to a range, and I cut/copy from the middle of that range, the conditionally formatted range becomes split. With multiple copy/cuts these ranges can spawn out of control, eating up a tremendous amount of resources. Has this been fixed?

I'm sure that's fairly confusing. Let me illustrate:

If my initial range (conditionally formatted) is this: =$A$1:$A$10, and I cut from say A5 and paste it at A12, then my conditionally formatted range becomes: =$A$1:$A$4,$A$6:$A$10,$A$12. Imagine this on 10,000 rows with twenty or thirty cuts and you can see why it becomes a problem.

re: More Conditional Formatting Features in Excel 2010

AmitVelingkar — Thu, 13 Aug 2009 18:04:06 GMT

IgorM: unfortunately, there isn't a really good way of doing thing. We are looking at this for future releases.

Currently, the recommended way is to create separate rules that apply to each row (format painter is a great way to accomplish this). This works the best if the number of rows will remain constant on data refresh. Thanks for giving us your feedback.

re: More Conditional Formatting Features in Excel 2010

Sam Rad — Thu, 13 Aug 2009 16:23:57 GMT

Sorry, ended up removing a comment, I'll repost here:

IgorM: Amit should be replying to your question shortly.

Colin: I don't remember that option (I was new to the team around that time)...Was it doing something that couldn't be done through a cf formula rule?

Steve: Can you do this with a formula rule today and a relative ref? Like select a1:a10 and new rule -> =X1 where column x returns true or false? Or are you talking about something else?

Colin part 2: Yeah the quick turn on for CFs is nice w/o having to build the formula rule, unfortunately we just didn't spend time there this release.

Noah: see Colin's link, something like that was in the beta as well but didn't make the final release. iirc there are some design complications around c/p partially highlighted rows, and losing formatting, and the fact that it only worked with tables. we should look at this as well as colin's cf, and other easy to set presets (ideas?), in the future.

re: More Conditional Formatting Features in Excel 2010

Noah — Thu, 13 Aug 2009 15:49:07 GMT

Will Excel 2010 contain a simple way con conditionally format entire rows based on the contents of a cell in that row? I've been looking for a function like that since Excel 97.

re: More Conditional Formatting Features in Excel 2010

Colin Banfield — Thu, 13 Aug 2009 13:48:29 GMT

"Colin: I don't remember that option (I was new to the team around that time)...Was it doing something that couldn't be done through a cf formula rule?"

These were Dave Gainer's posts. Here's the link to the original discussion. Scroll down to the section titled "More Conditional Formatting Goodness"

blogs.msdn.com/.../tables-part-2-stickiness-structured-selection-and-more.aspx

Like the other cell highlighting rules, the option was designed to avoid having to use formulas...the discoverability thing.

I can't locate the follow-up post that explained why the option was being dropped for the Excel 2007 release.

re: More Conditional Formatting Features in Excel 2010

Steve — Thu, 13 Aug 2009 00:21:02 GMT

I never saw the Compare columns in 2007B but it sounds similar to a feature I've been looking for where conditional formatting can be applied to say, a list of names in column A based on some formula in column X.

re: More Conditional Formatting Features in Excel 2010

Colin Banfield — Wed, 12 Aug 2009 18:07:23 GMT

Sam, out of curiousity, what ever happened to the "Compare Columns" rule that made a brief appearance in Excel 2007 beta but was canned in the final release because of bugs that couldn't be fixed in time? Why has this option not returned in Excel 2010, given that by the time Excel 2010 ships, you'd have had three years to fix the problems?