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”?

sn — Wed, 05 Oct 2005 09:59:27 GMT

Are you English or from one of the Commonwealth countries? I noticed you said "colour scales" not "color scales" :)

Love the feature. Will Office 12 B1 be available broadly? Or atleast freely within Microsoft? Where can I go to register for B1 within the company?

Thanks. I am a huge Excel user and fan. I really appreciate the blog and your team taking the time to post to it.

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

Step — Wed, 05 Oct 2005 10:09:33 GMT

Awesome! I've been wanting improvements to Conditional Formatting for a while - very exciting! I can see uses for the data bars, but I'm really looking forward to seeing what you've done with the "regular" Conditional Formatting dialog.

You guys really seem to have outdone yourselves on this release. Looking forward to B1.

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

anon — Wed, 05 Oct 2005 12:42:50 GMT

The real kicker would be that the data bar is only a working sample of a more general approach to visual widgetry.

My question is, how does this component relate to IGX?

I see a number of ISVs willing to either write such XML-controlled widgets and/or migrate arbitrary ActiveX to this. Your thoughts?

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

chad — Wed, 05 Oct 2005 13:53:18 GMT

One item in conditional formatting that I've always desired is the ability to hide (or not print) rows/columns using conditional formatting. For example, if a row has all zeros in it, conditional formatting would either hide the row or it wouldn't be printed.

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

Marcos Martins — Wed, 05 Oct 2005 14:49:32 GMT

Good features!!

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

Charles — Wed, 05 Oct 2005 15:46:04 GMT

Office 12 looks very impressive: I guess you must have started working on it way before Office 2003 shipped.

Bars look great, but whats the performance hit like when Excel has to do the comparisons on a large column?

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

Helen — Wed, 05 Oct 2005 15:55:47 GMT

This looks great and will be a very useful feature. Now I'm really looking forward to the new version!

Conditional Formatting Reference in Forumulas?

Tianwei — Wed, 05 Oct 2005 16:52:07 GMT

David, thanks for the detailed postings. I am getting more excited about Excel 12 every day now.

Quick question: one of the things I have longed for over the years is the ability to reference a format (colors, patterns, etc) in a formula. Case in point, you have a detailed page of all data and conditional formatted cells. Then you create a summary page where you would like to say: if the cell in detail page is formatted like (ref to a "hard formatted" cell) then retrieve the value. Would there be such a feature in Excel 12?

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

Andrew — Wed, 05 Oct 2005 19:31:01 GMT

Hi David,

These new features look like they will benefit a lot of users.

I just had a shot at doing something similar for earlier Excel versions.

http://blog.livedoor.jp/andrewe/archives/50066157.html

Not quite as good as though ;-)

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

XL-Dennis — Wed, 05 Oct 2005 19:47:59 GMT

Very nice and it seems that we can replace all the workarounds we now use with built-in features.

Will we also be able to fully access C/F via VBA?

Kind regards,
Dennis

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

Erik Svensen — Wed, 05 Oct 2005 20:38:40 GMT

Great feature ... can't wait to implement this in my Excel solutions :-)

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

Colin Banfield — Wed, 05 Oct 2005 20:48:53 GMT

Very nice feature indeed! The in-cell visualization may make it easy to create, say, a project Gantt chart in Excel.

Colin

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

Rob van Gelder — Wed, 05 Oct 2005 22:30:29 GMT

Chad,

What you require is conditional number formatting - a feature which does not exist (yet?)
eg.
=0 then NumberFormat = ;;;

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

Jon Peltier — Thu, 06 Oct 2005 04:51:58 GMT

David -

Two weeks ago I build a VBA procedure to draw rectangles in a column of cells to compare values visually. Last week I saw my technique built into the new CF, demo'd at the MVP Summit. This is way cool, and the new Excel is going to rock.

- Jon

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

Jon Peltier — Thu, 06 Oct 2005 04:52:39 GMT

Rob -

The =0 part of the number format is between the second and third semicolons. But you knew that.

- Jon

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

Pete Wall — Thu, 06 Oct 2005 05: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

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

Biff — Thu, 06 Oct 2005 08: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”?

David Gainer — Thu, 06 Oct 2005 08: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”?

David Gainer — Thu, 06 Oct 2005 08: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”?

Chris Rae — Thu, 06 Oct 2005 17: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 21: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”?

mschaef — Fri, 07 Oct 2005 14: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.

http://www.mschaef.com/cgi-bin/blosxom.cgi/tech/excel/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:

http://www.czso.cz/kraje/roc/data2004/13310104/data/grafy/graf08.gif

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

Jean Martineau — Sat, 08 Oct 2005 04: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
http://www.apics.org/Resources/Magazine/Current/SalesForecast.htm

Sparklines?

Adam S — Tue, 11 Oct 2005 22: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”?

Vic Eldridge — Mon, 17 Oct 2005 10: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

Conditional Formatting Trick 1 – Mulit-Coloured Data Bars

Microsoft Excel 12 — Sat, 25 Feb 2006 00:50:48 GMT

A few months ago, I described the new features we have added to Excel 2007 in the area of conditional...

Data Bars and DataGridView

Mabsterama — Mon, 20 Mar 2006 00:21:52 GMT

A lot of people out there will by now have seen the screenshots of Excel 12 (now Excel 2007) and its...

Tables Part 2: Stickiness, Structured Selection, And More

Microsoft Excel 2007 (nee Excel 12) — Sat, 27 Jan 2007 04:02:38 GMT

One of the key benefits of tables is how other features in Excel 12 behave more predictably and more

Excel's new gradient Data Bar feature is cool: you can do it too!

Calvin Hsia's WebLog — Fri, 28 Sep 2007 03:43:15 GMT

I’ve seen demos of Excel 12 and it’s conditional formatting Data Bar and thought it was cool. In each

conditional formatting in excel

conditional formatting in excel — Sun, 25 May 2008 09:07:34 GMT

PingBack from http://jamarion.availablenewsmaterial.info/conditionalformattinginexcel.html

conditional formatting in excel

conditional formatting in excel — Sun, 25 May 2008 09:07:56 GMT

PingBack from http://royce.clearviewice.info/conditionalformattinginexcel.html

excel conditional formatting

excel conditional formatting — Sun, 29 Jun 2008 00:47:46 GMT

PingBack from http://hailee.sextalessite.com/excelconditionalformatting.html

excel conditional formatting to sort

excel conditional formatting to sort — Sun, 29 Jun 2008 01:05:53 GMT

PingBack from http://haden.toplovestories.com/excelconditionalformattingtosort.html

excel conditional formatting to sort

excel conditional formatting to sort — Sun, 29 Jun 2008 03:15:25 GMT

PingBack from http://angeline.finestmatingstories.com/excelconditionalformattingtosort.html

Excel Roundup 1

Excel Roundup 1 — Thu, 14 Aug 2008 21:42:53 GMT

PingBack from http://www.automateexcel.com/2005/11/10/excel_roundup_1/

Custom Data Bars | keyongtech

Custom Data Bars | keyongtech — Thu, 22 Jan 2009 09:39:49 GMT

PingBack from http://www.keyongtech.com/4078482-custom-data-bars

Microsoft Excel Conditional Formatting overview of what we did and | garden decor

Sun, 14 Jun 2009 09:51:00 GMT

PingBack from http://gardendecordesign.info/story.php?id=6131

Microsoft Excel Conditional Formatting overview of what we did and | Outdoor Decor

Fri, 19 Jun 2009 07:03:57 GMT

PingBack from http://outdoordecoration.info/story.php?id=4949