Custom Formats

[kkjensen]

Hello all,

I'm learning how to do custom formats on cells and it's seems like a nice alternative to having conditional formats all over the place.

I have a couple questions about the scalability of it.

If I put
[>2][red]">2";0.00
I will get a red >2 if the number is greater than 2...otherwise it's 2 decimals.

I've played around with it and other colors work and i can put more conditions (ie if a number is greater than 2 it's red but if it is less than one it's green)

Is there any way to combine conditions? Like "less than 2 AND greater than 1"???

What about making this bold or in italics?

Is there any other functionality that in this kind of formatting?

It's turning out to be quite useful as I can still refer to the value of the cell even though it states ">2" and this saves me from having to create redundant columns to summarize what is happening.

 

[Greg Truby]

AFAIK you cannot have more than one comparison operator and you can't do bold nor italics with custom formats.

As far as the question about 2>x>1, you could do something like this:

[Black][>=2]0.0;[Red][>1]0.0;[Black]0.0

which would format numbers between 1 & 2 in red, all others black, 1 decimal place for all.

HTH

 

[kkjensen]

Bummer...I hope they add more to this and add some better documentation to the help files. I work in Engineering and it's handy to dummy-proof things by adding lbs or kg as a unit so people can't enter the wrong units by accident.

oh well. Thanks!

 

[Greg Truby]

FWIW - You can also use Data Validation to display an "Input Message" and set the validation to accept any value.

 

[kkjensen]

How many of these conditions can be stringed together?

I've taken your example and modified it to this:

[Black][>2]">2";[Blue][>1]0.00;[Red]0.00

Which simply warns me of anything less than one and lumps together everything bigger than 2.

I'd like to expand it a bit more to give me somewhat of an idea HOW much bigger than two the value may be using the following:

[Black][>5]">>2";[Black][>2]">2";[Blue][>1]0.00;[Red]0.00

...but it returns an error. It this limited to 3 or am I just missiong something in the details of the format?

 

[Mark W.]

A custom number format consists of up to 4 parts -- one of which is reserved for text formatting. That leaves only 3 for conditions like the ones you are using. See the Excel Help topic for "Create or delete a custom number format" for a definition of each part. Also, review its subtopic, "Decimal places, spaces, colors, and conditions".

 

[Greg Truby]

As Mark has already explained, using custom formats, you get three to play with. And don't forget, you can also have up to three conditional formats. So in essence you get up to six [sort of -- custom formats can't change background colors nor borders and have limited font color options whereas conditional formats don't change what's actually displayed (i.e. the numberformat); so the twain ain't twins]. In any event, after six, it's VBA-time. Your options with VBA are essentially infinite, but you drag security settings/enable macros into the mix. Up to you whether employing VBA is worth it to get the more than six conditions.