From the Desk of Steve Colton
( Return to Index )

Conditions in XL’s Custom Number Formats
I consider myself an Excel expert, a black belt power user. Not quite techy enough to be a developer, but knowledgeable well beyond 98% of Excel users. I read the blogs and cruise the web sites of the “acknowledged” Excel gurus and occasionally I pick-up a tidbit. Well, it happened this morning. I was reading Daily Dose of Excel and the poster, Dick Kusleika, demonstrated how he got the look he wanted in a ranking system by using conditions in custom number formats.

Conditions in custom number formats?

Never heard of it before. Never seen it used. Cool!

I did some digging to make sure this wasn’t a PC only feature. I’m a Mac person to the core and I use Excel 2004 (and as an aside, I WILL NOT UPGRADE until Microsoft restores VBA to the Office suite for Mac... supposedly the next version). Anyway, I was pleased to find this was NOT a PC only feature. It had been available to me all along. I just didn’t know it. Now understand that I use custom number formats extensively. So this was quite cool. Something I could and would readily add to my arsenal of XL tricks. I can already think of dozens of applications.

Here’s an example of how it works:

I want numbers less than zero to be flagged as an error. I want numbers greater than zero but less than 1000 to be “ho-hum black” and I want numbers greater than 1000 to be green. The custom number format looks like this:

     [Red][<0]“Error”;[Black][<1000]#,##0;[Green]#,##0

And here’s what it displays. The left-hand column is using the General format. The right-hand column shows the SAME number with the custom format.

So that’s it. Experiment with the syntax. I will. I tried to add my standard format for “equals zero” (”- “) to the above custom string, but XL kept giving me an error. There must be a limit on the LENGTH of a custom string. I’ll keep experimenting until I know for sure. I also want to experiment with a combination of this trick (conditions in a custom number format) AND conditional cell formatting. My gut tells me the combination could prove very flexible. And then I will need to experiment with setting all of this within a macro. <G> Never stop learning. That’s my mantra. Never stop learning.

steve colton
October 9th, 2008