Did you know! Conditional Formatting in Excel 97-2003/2007 limits to 3

A lot of the time, there are things that totally baffle you when you least expected them – limitations, extensions, workarounds, unavailability etc. And that’s exactly what I’d like to uncover in my ‘Did you know’ series! Without much talk in this one, I really want to keep these ones short and succinct, here’s the first…

DID YOU KNOW!

The Conditional Formatting of Excel 97-2003/2007 has the major limitation that only 3 formats can be specified (excluding the one that’s already present in the cell).

All this brings up the argument of why even give such a facility when it’s not really much use! This may not seem like a big limitation, but only when you really try to put this feature to good use will you be irked!

The Situation – I wanted a cell to be formatted in 4 different ways based on filter criteria.

  1. A dollar format if it’s tabulating currency
  2. A number format for frequency counting table values
  3. A percentage when applicable
  4. and finally when ‘#N/A’ appears, I’d like it to dissolve into the background!

And appears this is not what the conditional formatting can handle!

The workaroundVBA! Always!

Additional tip

There’s a ‘Stop if True’ feature in the Conditional Formatting rules. Conditional formatting executes the format rules in the order in which they are added! So, if you select this feature, the Conditional Formatting stops at that particular rule, and doesn’t go further! If I had to explain using code, it’s more like

If <condition = true> then

<body of statements>

<exit sub>

End if

If we ended up using an ‘If’ with an ‘Exit Sub’, that’s exactly what ‘Stop if True’ would do – exit the function if any of the ‘If’s’ is true! Although, given this limitation of just 3 formats, I’m not sure how much help this is – but surely, if you have just 3, this is a good one.

About these ads

One thought on “Did you know! Conditional Formatting in Excel 97-2003/2007 limits to 3

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s