PDA

View Full Version : Using the Number Formatting Language to Specify the “BOLD” Attribute



Cyberdude
05-26-2010, 04:48 PM
Excel has a very flexible custom number formatting language that is used in the “Format Cells” box. For example, if I write a custom cell format like #0.0 then if I enter the number -3.5 into the cell, Excel displays the number 3.5. Note that the minus sign is removed because the format specified doesn’t have a minus sign.

Next I format the cell with #0.0;-#0.0. Here I added the feature that recognizes negative numbers (the semicolon and the part that follows it). When I enter -3.5 into the cell, Excel displays the minus sign (-3.5).

I can take this a step farther by writing the format as follows:

#0.0;[Red]-#0.0

where[Red] tells Excel to color the cell contents red for negative numbers.

Finally, my problem. I would like to format a number so that all positive numbers are NOT bold and all negative numbers ARE bold. The reason for this is that black numbers usually show up dark enough, but red numbers tend to be shaded lighter. “But”, you say, "why don’t you substitute the number of the darker red value, like maybe 23?” To do that I would have to make the format look something like:

#0.0;[Color23]-#0.0

I know how to change font color, but what I don’t know is how to include in the format an instruction to BOLD the font if it’s negative. In fact, I also don’t see how I could change the normal font to italics if I chose to do so.
Anyone out there got the answer?
Sid

austenr
05-26-2010, 06:38 PM
Sid,

According to Chip Pearson it can't be done. Having said that someone will probably come along and prove him wrong.

mikerickson
05-26-2010, 06:41 PM
Rather than using Number Format, both the color and the bold can be done via Conditional formatting.

Cyberdude
05-27-2010, 10:19 AM
Thanx, guys. I know how to do it in other ways . . . I just wondered if it could be done with the formating language and I missed that part of my education.
Sid :bug: