OpenOffice Is Looking Good

Published Mon, Nov 9 2009 12:45 | Bob Phillips

Normal 0 false false false MicrosoftInternetExplorer4

I have blogged previously about what a rubbish job MS have done with Conditional Formatting in Excel 2007, but it seems it is worse than I feared.

In this same file I now had CF setup as I wanted, after much trials and tribulations, but I needed to insert a new column, but the CF was required to be different than the CF on either side.

First thing I tried was to simply insert the column. Of course, this meant that it inherited the CF from the column to the left, which was not what I wanted, so I opened the CF dialog and thought I would change the range that the rule applies to from those two columns to just my newly inserted column, and change that rule. This I did, but it wiped out the rule entirely from the other column. And to compound the problem, it cleared the format fill colour from the newly inserted column, and when I changed the rule and added back the fill colour, every cell in my range showed the colour, even though only a single cell matched the condition being tested for.

So I tried another approach. I created a column off the data area, setup the CF exactly as I wanted for that column, then added an index number in the first free row so that I could sort the columns into my required order. For some unknown reason, this removed my CF, gave it the CF of the column to its (new) left, and also managed to setup data validation as was applied to its (new)O column to the right. Genius! I knew what I wanted to do, but the designers at MS decide they know better, in two ways.

So how did I fix it? I first deleted the CF from the column that is already present, then inserted my new column. I then re-instated the CF that was in the previous column, and added the CF in my new column. Not a great chore, but I SHOULDN’T HAVE TO FIGHT THE SOFTWARE to do what I want to do. And you can bet that the next time I need to do it, I will have forgotten, and I will go through all this pain again.

Whilst I am having a good rant, when are MS going to default CF (and Data Validation) edit boxes to update, so as to avoid accidentally inserting rubbish when you hit the arrow keys. I can’t recall ever thinking the default was useful.

Filed under: , , ,

Comments

# Jan Karel Pieterse said on November 11, 2009 2:34 AM:

A simple way to insert a column without CF or DV is to copy an empty column BEFORE doing the insert.

# Bob Phillips said on November 11, 2009 2:40 AM:

Hi Jan Karel,

Yes that does work, but again it is getting around the fundamental problem, namely that the CF dialog is broken, it doesn't work well and sould be fixed.

# Jan Karel Pieterse said on November 12, 2009 4:08 AM:

I totally agree, that piece of UI is terrible.

And to add to the fun, there is also a bug. Copying a range with CF and pasting that range elsewhere ADDS the CF to the new range rather than overwriting the existing CF's.

# Bob Phillips said on November 12, 2009 5:19 AM:

I have blogged that particular feature before, and I honestly think it is by (very bad!) design.

# JP said on November 13, 2009 3:51 PM:

Does OpenOffice handle this better?

# Harlan Grove said on November 13, 2009 5:58 PM:

OOo conditional formatting is similar to that in Excel 2003, so it does less but doesn't fubar.

Leave a Comment

Name:  
Website: