Custom Formats

kkjensen

Active Member
Joined
Jun 22, 2004
Messages
352
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.

:p
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,021
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

Active Member
Joined
Jun 22, 2004
Messages
352
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

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,021
FWIW - You can also use Data Validation to display an "Input Message" and set the validation to accept any value.
 

kkjensen

Active Member
Joined
Jun 22, 2004
Messages
352

ADVERTISEMENT

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.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
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

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,021
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.
 

Forum statistics

Threads
1,148,529
Messages
5,747,230
Members
424,070
Latest member
smanni3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top