# Custom Formats

#### kkjensen

##### Active Member
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.

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### Greg Truby

##### MrExcel MVP
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
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
FWIW - You can also use Data Validation to display an "Input Message" and set the validation to accept any value.

#### kkjensen

##### Active Member
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
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
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.

Replies
4
Views
149
Replies
8
Views
224
Replies
2
Views
168
Replies
1
Views
516
Replies
0
Views
98

1,181,786
Messages
5,932,045
Members
436,816
Latest member
Composh

### 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.

### Which adblocker are you using?

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

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