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.