Conditional Formatting – overview of what we did, and what’s a “data bar”?

re: Conditional Formatting – overview of what we did, and what’s a “data bar”?

Vic Eldridge — Mon, 17 Oct 2005 08:45:17 GMT

I notice that when you specify a number for the shortest/longest bars, if the actual data falls outside those limits, Excel will just draw either the shortest or longest bar. I think that could be dangerously misleading in certain situations. Perhaps arrowheads could be used to indicate when the data is off-scale. A lot of people might say "set it up using the Highest/Lowest value option". The trouble with that is when the data varies by orders of magnitude, you quickly run out of pixels to display the smaller values. Also with regards to Data Bars, I'm interested to know how you're going to handle negative values. Is the zero point going to be somewhere in the middle of the column, with negative values to the left of zero and positive values to the right ?

Also, I must congratulate you and the team for the work you've done. I have to admit I was beginning to think that >256 columns was >Microsoft but I'm glad to say you've proved me wrong. I'm just itching to get my hands on it !

Regards,

Vic Eldridge

Sparklines?

Adam S — Tue, 11 Oct 2005 20:09:19 GMT

This is a really cool looking feature. What about having sparklines (miniature graphs) that could either be in a cell, or quickly generate a cell-sized line graph of a column or row?

re: Conditional Formatting – overview of what we did, and what’s a “data bar”?

Jean Martineau — Sat, 08 Oct 2005 02:38:50 GMT

Very excited . I am also hurry to see new features for pivot table. I am looking to see calculated items from a previous field item. This would be very practical for MPS: StartingInventory = EndInventory of the previous period and EndInventory is a calculated item. See examples at

www.apics.org/.../SalesForecast.htm

re: Conditional Formatting – overview of what we did, and what’s a “data bar”?

mschaef — Fri, 07 Oct 2005 12:49:48 GMT

Here's another approach to doing something similar in 'legacy' Excel. This technique uses the old line art characters from the original IBM PC.

www.mschaef.com/.../databar.txt

One question I have about the "Real McCoy" in Excel 12: will it be possible to have bars that progress to the left, as well as bars that progress to the right? It'd be nice to be able to use this feature to do things similar to this:

www.czso.cz/.../graf08.gif

re: Conditional Formatting – overview of what we did, and what’s a “data bar”?

David Gainer — Thu, 06 Oct 2005 19:12:40 GMT

Chris - at the moment, we don't (though we have made it easier to select conditional formats from the ribbon), but that is a great idea.

re: Conditional Formatting – overview of what we did, and what’s a “data bar”?

Chris Rae — Thu, 06 Oct 2005 15:32:55 GMT

Good afternoon, Mr Gainer -

One thing I've noticed with these new conditional formats is that the "scope" of the format now matters much more. While it's always been a little tricky to tell how far your conditional number formatting extended, it now makes quite a difference if you format A1:A10 to show data bars, and then independently format A11:A20 to do the same. The numeric ranges the bars pertain to are now different for what appears to be a contiguous range. Is there any possibility we could have even a hotkey (much like Ctrl-/ for array formulas) to show the region that this particular bar format covered?

Chris

re: Conditional Formatting – overview of what we did, and what’s a “data bar”?

David Gainer — Thu, 06 Oct 2005 06:51:44 GMT

Biff - thanks for the idea. With cell alignment and column width, you may be able to get the value inside the bar.

re: Conditional Formatting – overview of what we did, and what’s a “data bar”?

David Gainer — Thu, 06 Oct 2005 06:49:53 GMT

Howdy,

sn, Step, Marcos, Helen, and everyone else – Glad you like what you see.

Anon – IGX is a separate diagramming component. Also, the idea of ISVs writing other visualizations is interesting, although in this release, they will have to rely on the Excel object model.

Chad – We didn’t address that specific scenario in our conditional formatting work this time out.

Charles – Performance is always something we test and spend a lot of time on in order to make sure it is acceptable. Specifics depend on the amount of data you are using, the machine specs, etc.

Tianwei – No, we haven’t done any work to enable referencing formats in formulas. In Excel, formatting is applied after calculation, so there would be the danger of conditional formats changing after calculation.

Andrew – Thanks for the link.

XL-Dennis – Absolutely – the new conditional formatting features will have a full VBA object model.

Rob – That is a great idea, but not one we have implemented in Excel 12. What kind of scenarios would you use that for?

Pete – You will be able to filter by formatting, conditional and otherwise, in Excel 12. That will be the subject of some posts later this fall. We have a lot to talk about this release!

re: Conditional Formatting – overview of what we did, and what’s a “data bar”?

Biff — Thu, 06 Oct 2005 06:02:20 GMT

This seems like a nice feature and judging by the comments, greatly anticipated.

Just a thought.....

Place the numeric value "inside" the color bar.

re: Conditional Formatting – overview of what we did, and what’s a “data bar”?

Pete Wall — Thu, 06 Oct 2005 03:17:29 GMT

I don't get overly excited by conditional formatting though it certainly has its uses.

A bit like Tianwei wrote, the feature would be useful if one could filter ("directly")on the conditional formats. Without that capability, the conditional formating is to me only doing part of what I need. A list of say 10,000 items and 50 of them highlighted by conditional format is not much help, IMO, if I can't readily filter to (de)select them. This is obviously not related to data bars, which are the subject of the post. So, maybe it will be addressed in future articles?

regards