Fun With Conditional Formatting

re: Fun With Conditional Formatting

Superphilipp — Wed, 19 Jul 2006 16:35:17 GMT

Huh? What has happened to your Ribbon(tm)? I seem to remember that it was about three times as high.

How do you conditionally-format a row based on a cell?

ZivC — Wed, 19 Jul 2006 17:23:26 GMT

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.

re: Fun With Conditional Formatting

Mario Goebbels — Wed, 19 Jul 2006 17:28:48 GMT

Seeing that huge quickstart bar, I take it the own food doesn't taste good enough? ;)

re: Fun With Conditional Formatting

joe — Wed, 19 Jul 2006 17:32:41 GMT

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

re: Fun With Conditional Formatting

Kdbertel — Wed, 19 Jul 2006 18:27:55 GMT

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?

re: Fun With Conditional Formatting

Mark D — Wed, 19 Jul 2006 19:19:41 GMT

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

re: Fun With Conditional Formatting

Harlan Grove — Wed, 19 Jul 2006 19:42:23 GMT

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?

re: Fun With Conditional Formatting

Brent — Wed, 19 Jul 2006 20:06:09 GMT

When you conditional format a range with the icons, can a legend be displayed automatically that shows what each icon represents?

re: Fun With Conditional Formatting

Mike Staunton — Wed, 19 Jul 2006 20:12:57 GMT

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

re: Fun With Conditional Formatting

Leta — Wed, 19 Jul 2006 20:56:19 GMT

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)

re: Fun With Conditional Formatting

Harlan Grove — Wed, 19 Jul 2006 23:24:49 GMT

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.

re: Fun With Conditional Formatting

David Gainer — Thu, 20 Jul 2006 02:17:35 GMT

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.

re: Fun With Conditional Formatting

sam — Thu, 20 Jul 2006 10:19:58 GMT

"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

re: Fun With Conditional Formatting

ZivC — Thu, 20 Jul 2006 19:10:30 GMT

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.

re: Fun With Conditional Formatting

Joe — Thu, 20 Jul 2006 22:22:18 GMT

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.

re: Fun With Conditional Formatting

David Gainer — Fri, 21 Jul 2006 00:49:09 GMT

ZivC, we considered advertising this at the top level in the ribbon, but based on our research, the new conditional formatting rules we added covered by far the majority of the cases that people use formulas for in current versions of Excel. We will continue to think about this area going forward.

re: Fun With Conditional Formatting

Andrew — Fri, 21 Jul 2006 19:12:18 GMT

How do I programmatically add a formatting rule? For example, I would like to create a macro that creates a number of formatting rules that can be used later by the user or write an Add-In that generates formatting rules. Does the Excel Object Model include these rules?

Thanks,
Andrew

re: Fun With Conditional Formatting

David Gainer — Fri, 21 Jul 2006 23:08:07 GMT

Andrew, check out this post for some VBA examples:

http://blogs.msdn.com/excel/archive/2005/10/14/481237.aspx

re: Fun With Conditional Formatting

Biff — Sat, 22 Jul 2006 07:05:00 GMT

"the "Formula Is" functionality is hidden, and hard to use"

I don't know about Excel 2007, but in earlier versions it's not hidden at all and is exactly where you would think it should be, under Format. It's "easy" to use. In fact, I use "Formula Is" exclusively. I never use "Cell Value Is".

"use "Formula is" and return TRUE or FALSE based on that formula"

Using a logical comparison that returns a boolean is the most common method, however, you can use any formula expression that returns a numeric value as well. Any numeric value other than 0 will be evaluated as TRUE and the format will be applied. A numeric return of 0 will be evaluated as FALSE and the format will not be applied. A very simple example would be:

=COUNTIF(A$1:A$10,"x")

If the result was 0 no format would be applied.

If the result was 7 the format would be applied.

re: Fun With Conditional Formatting

Colin Banfield — Sat, 22 Jul 2006 20:09:43 GMT

Biff, the “Formula Is” equivalent is also easy to find in Excel 2007. You select New Rule from the Conditional Formatting menu (which displays after you click the Conditional Formatting button on the Ribbon). In the New Formatting Rule dialog box, there's a list of rule types and one of these is "Use a formula to determine which cells to format."

I suspect that the real concern being alluded to is contained in the "...and hard to use" part. Except for providing examples in a help window, I don't know how the UI could make using specific formulas in conditional formatting more "discoverable." For example, how can the UI expose the fact that a formula like =COUNTIF($A$1:$A$10,A1)>1 highlights duplicates in the selected range A1 to A10? From this perspective, formulas would appear "hard to use." However, what you can do is figure out the most common conditions that folks use conditional formatting formulas for and expose these conditions as highlighting options in the IU, thus making these specific conditions “discoverable.”

In this respect, Excel 2007 does a reasonably good job. For example, on the Conditional Formatting menu you can choose to highlight cells containing duplicates, unique values, top N items, top N %, bottom N items, bottom N %, above average, below average and 1 to 3 standard dev above or below average. You can highlight cells with dates occurring today, yesterday, tomorrow, last 7 days, next month and so on. You can highlight cells with text containing, not containing, beginning with & ending with. You can highlight cells with blanks, non-blanks, errors or no errors. Finally, there’s a column comparison option available to Tables. To highlight cells with any of the preceding conditions in earlier versions of Excel, you'd need to use formulas, and this is where discoverability becomes an issue for most folks. In Excel 2007, there should be far less need to use formulas at all.

On a related note, it’s unfortunate that Data Validation wasn’t a similar beneficiary of any new conditions in Excel 2007. For example, the ability to use UDFs directly in data validation formulas (the workaround to reference a UDF in a worksheet cell is lame and doesn’t work if you want to apply the UDF to a bunch of input cells). Also, I’d have liked to see a “like” operator, so that you could validate a specific input string format.

re: Fun With Conditional Formatting

Biff — Sun, 23 Jul 2006 01:09:39 GMT

How does Excel 2007 conditional formatting handle these:

** highlight cells containing duplicates

Is the first instance of a value considered a duplicate?

A1 = Smith
A2 = Smith

Are both cells highlighted or is just A2 highlighted?

** Top/botton N lists

Are "ties" accounted for?

A1 = 1
A2 = 1
A3 = 1
A4 = 2
A5 = 3

Would the bottom 3 be A1:A3 or A1:A5 ?

Or:

A1 = 1
A2 = 1
A3 = 1
A4 = 1
A5 = 3

Would the bottom 3 be A1:A4 ?

re: Fun With Conditional Formatting

Colin Banfield — Sun, 23 Jul 2006 01:23:00 GMT

Is the first instance of a value considered a duplicate?

A1 = Smith
A2 = Smith

Are both cells highlighted or is just A2 highlighted?

<<<Both Cells are highlighted.>>>

A1 = 1
A2 = 1
A3 = 1
A4 = 2
A5 = 3

Would the bottom 3 be A1:A3 or A1:A5 ?

<<<A1:A3>>>

A1 = 1
A2 = 1
A3 = 1
A4 = 1
A5 = 3

Would the bottom 3 be A1:A4 ?

<<<Yes.>>>

re: Fun With Conditional Formatting

Biff — Sun, 23 Jul 2006 01:31:49 GMT

The only gripe I have about CF Formula Is, is that teenie tiny microscopic little box for the formula. Argh!

re: Fun With Conditional Formatting

David Gainer — Sun, 23 Jul 2006 08:33:09 GMT

Interesting discussion. Colin, thanks for the explanation. One note – I am sorry to say the “column comparison” is going to be gone in the beta refresh (and for 2007). We turned up some pretty significant bugs recently, and we are past the point where we can do the work required to address them, so that is going to have to wait for the next version. Thanks for the data validation items.

Biff, I agree with you about the smaller refedits. That’s something I would like to try and solve all over the place in the future.

re: Fun With Conditional Formatting

Biff — Sun, 23 Jul 2006 08:56:50 GMT

I would upgrade just for that one improvement!

re: Fun With Conditional Formatting

Gareth Horton — Tue, 25 Jul 2006 20:59:01 GMT

David,

We develop a product that exports to Excel. In the past, we have not pushed too much of our metadata over to the Excel side, but with the new Open XML formats, we are in the position to do this much more. One of the main areas for this is Conditional Formatting. We are able to define conditional formatting with our product and now push it over to Excel Conditional Formatting rules, rather than just applying the formatting on the cells/ranges themselves.

Our product is capable of dealing with quite a lot of data, as well as having fairly complex formatting rules. What we found when we started to do this with XLSX is that as with Excel 2003, Excel 2007 remains very buggy in all areas of conditional formatting: display and configuration.

1. On a fairly up to date machine (18 mths old middle end PC, 1GB RAM) XLS (using BIFF8) or XLSX files with more than 2000-3000 conditional formatting 'rules' seems to be the tipping point to poor display performance. We can exceed that very easily with an export from our product.

2.Whereas OpenOffice.org 2.0 will show similar problems when opening an XLS file with large numbers of conditional formats, once opened, it will outperform Excel 2007 in display performance.

3.In our efforts to produce creative formulas in XLS to overcome the 3-rule limit, we discovered that the boolean operator OR() will evaluate both of its expressions, even though the first evaluates to true, (as we later confirmed in the Excel 2003 help). Could this be fixed in 2007?

Seeing as the opening up of the Excel file format to developers (only true Excel dependents such as ourselves tackled native BIFF8) will allow many more applications to create rich, fully featured Excel files, now potentially up to 1 million rows and numerous columns in size, could you review the way Excel handles conditional formatting from a performance perspective.

The legacy way that conditional formatting is implemented in openxml, might benefit from a different approach, such as using formatting IDs as with traditional xfids, allowing cells to be a member of a cell formatting group, as opposed to a post-display evaluation

I can certainly generate very complex files for you, if you need them.

Thanks in advance

Gareth

gareth_horton@datawatch.com

Web Development Tips, Tricks & Trivia » Blog Archive » HOW TO create a Ribbon-less pre-Excel 2007 look

Wed, 27 May 2009 14:28:26 GMT

PingBack from http://webdevelopment.mobiforumz.com/2008/09/19/how-to-create-a-ribbon-less-pre-excel-2007-look/

Microsoft Excel Fun With Conditional Formatting | bar stools

Microsoft Excel Fun With Conditional Formatting | bar stools — Fri, 19 Jun 2009 09:49:12 GMT

PingBack from http://barstoolsite.info/story.php?id=1841