Fun With Conditional Formatting

re: Fun With Conditional Formatting

Gareth Horton — Tue, 25 Jul 2006 18: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

re: Fun With Conditional Formatting

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

I would upgrade just for that one improvement!

re: Fun With Conditional Formatting

David Gainer — Sun, 23 Jul 2006 06: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 — Sat, 22 Jul 2006 23: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

Colin Banfield — Sat, 22 Jul 2006 23: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?

<<>>

A1 = 1

A2 = 1

A3 = 1

A4 = 2

A5 = 3

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

<<>>

A1 = 1

A2 = 1

A3 = 1

A4 = 1

A5 = 3

Would the bottom 3 be A1:A4 ?

<<>>

re: Fun With Conditional Formatting

Biff — Sat, 22 Jul 2006 23: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 — Sat, 22 Jul 2006 18: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 — Sat, 22 Jul 2006 05: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

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

Andrew, check out this post for some VBA examples:

blogs.msdn.com/.../481237.aspx

re: Fun With Conditional Formatting

Andrew — Fri, 21 Jul 2006 17: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