Conditional Formatting In Excel 2007 – Chav or Mini?

Yesterday I had an incredibly frustrating time.

I was updating my RibbonX Autogen routine to handle more types of controls. This is driven from a worksheet, some columns of which are generated, some of which depend upon user input. So being a helpful sort of guy, I have decided to shade the input cells as a visual aid. But in some circumstances, an input cell may not be required (for instance, a group does not require an image, so suggesting that it does by shading is a tad misleading). Being the helpful sort that I alluded to earlier, I decided that I would use conditional formatting, to test those conditions, and remove the shading where appropriate. Which is where I came up against the MS designers, who seem to me to live in a land far from reality, totally unaware of what people really do.

Okay, we all know that conditional formatting was limited, 3 conditions was never enough, and that the CF dialog was rubbish, but why didn’t they just address those issues? Why re-design the whole way it works? Don’t MS realise that evolution is just as valid an approach as revolution, change the things that really requirie changing first, see how it gets used, and then gradually build upon that improve. In other words, let the real users have some influence.

The first problem that I came across was the new behaviour whereby if you create a condition, and then paste the format of another cell over the top, another cell that also has CF, the target cell adds those format conditions. No options, you can’t say you don’t want to add, it just does it. Of course, unless you are aware of this, you may find your cells reacting to a differenet condition, and you then start wondering why the CF no longer works.

Next, I added some CF conditions to a number of rows over 2 columns. That was fine until I decided that I wanted one of the columns to have an extra test in the condition. So I selected the cells in that column, and edited the rule to include the new test. Lo and behold, the other column also adopted these tests. When you set conditional formatting on a range, Excel remembers which cells are using that rule, and so change the rule in any cell and they all get the change. Again, no choice that I can see, I can’t tell it what I want, I get what MS decide because MS knows best. In the end the only solution that I could find was by deleting the formatting on both columns and then recreating singly on each.

And of course, when you are in a cell that was part of a range that has been conditionally formatted, the formula is no longer adjusted to that cell as it was it previous versions of Excel, it shows the actual formula you applied. So I am in P15, and in the rule I see the formula =OR($A3=”break”,$B3,AND($A3=””,$M3<>””)). The first time I encountered this it really threw me. What possible purpose can this change serve? I cannot see it.

Add to all of this rubbish the new, glitzy, childish icon sets, the fact that the formula box still defaults to input (so you have to F2 to actually use it), the fact that the dialog is not resizeable in order to better view your formula; I can only conclude that this is another very bad user dialog.

Considering that Excel 2007 was supposed to be simpler to use, and remove some of the mysteries of Excel, I think the designers of Conditional Formatting in Excel 2007 have failed abysmally. As seems to be the way with MS and Excel, too much effort was spent on adding unnecessary and distracting glitz, and very little on real usability.

4 thoughts on “Conditional Formatting In Excel 2007 – Chav or Mini?

  1. I agree, Bob. I have some budget worksheets that make very heavy use of CF’s, and the mantra is now paste & check, paste & check. You basically have to check every line once you paste anything, to make sure it hasn’t added a duplicate CF. Very painful.

    While the limits are gone, which is a good thing, the design on CF’s is painful now, and needs to be addressed.

  2. You’re assuming the top priority was logical extension to conditional formatting rather than cramming as much eyewash into cells as possible.

    This is a symptom of Microsoft’s believe (possibly accurate) that Excel is used mostly for reports and dashboards. Excel as an extension of Word and PowerPoint requires hand holding that Excel as a stand-alone platform for analytical modeling doesn’t.

    It’s been obvious for several versions (at least since Excel 2002) that ‘visual enhancement’ is Microsoft’s priority with Excel.

  3. No Harlan, I am not assuming that. I know as well as yourself that glitz concerns MS more than functionality these days, but it doesn’t stop me complaining about it, nor should it. If we are quiet, they will think this rubbish (and I do not use that word lightly, CF in Excel 2007 is rubbish IMO) is acceptable.

    I use Excel extensively for reports and dashboards, but my view is that the ‘visual enhancements’ that MS are adding show that they nothing about good visual presentation, or how the brain processes visual information.

  4. I agree completely. Excel 2007 has advantages, more rows, more columns, more sort bys and more conditions in conditional formatting. However, a lot of things including charting and conditional formatting don’t work as well. They assume they know what you want and if you are doing anything that is not basic it makes the wrong assumption. Another problem I have with copying and pasting conditional formats is that the order of precedence of the formats changes.

Leave a Reply

Your email address will not be published. Required fields are marked *