In the past I have written a number of posts on the use of Custom Number formats including
Selective Chart Axis Formats
Custom Chart Axis Formats (Part 2)
A technique to quickly develop custom number formats
Chandoo has written about Custom Number Formats in:
Custom cell formatting in Excel a few tips tricks
Color Modifier
As part of these techniques you have the option to set the colors using the [Color] modifier
You can use a Custom format of: $#,##0;[Red]-$#,##0
10 Dollars will be displayed as $10
-10 Dollars will be displayed as -$10
Colors available include Red, Blue, Green, Yellow
However there is a much larger color palette available
Anybody who has or is still using Excel 2003 or prior will have a color picker which looks like this:
Well these 56 colors are all available and not just in Excel 95-2003 but in All Excel versions up to and including Excel 2013.
We have two methods to access these colors:
1. Using the Colors Name or
2. Using a Color Number.
Color Name
In Excel 95-2003 you can Right Click on a cell and change the Font or the Fill color
Simply select a color like below:
Note that a Green Color has been selected, the Dialog shows the name of the Color as Sea Green
To save you opening an early version of Excel here are all the colors listed above:
Top Row
Black, Brown, Olive Green, Dark Green, Dark Teal, Dark Blue, Indigo, Grey-80%
2nd Row
Dark Red, Orange, dark yellow, Green, Teal, Blue, Blue-Grey, Grey-50%
3rd Row
Red, Light Orange, Lime, Sea Green, Aqua, Light Blue, Violet, Grey-40%
4th Row
Pink, Gold, Yellow, Bright Green, Turquoise, Sky Blue, Plum, Grey-25%
5th Row
Rose, Tan, Light Yellow, Light Green, Light Turquoise, Pale Blue, Lavender, White
6th Row
Periwinkle, Plum, Ivory, Light Turquoise, Dark Purple, Coral, Ocean Blue, Ice Blue
Bottom Row
Dark Blue, Pink, Yellow, Turquoise, Violet, dark Red, Teal, Blue
To use these use the format $#,##0;[Color Name]-$#,##0
eg: [Blue Grey]$#,##0;[Sea Green]-$#,##0
This will display Ten Dollars as $10 and Negative Ten Dollars as -$10
Color Number
The Alternative method is to use a Custom Number Format and using the Color Number modifier like [Color Number]$#,##0;[Color Number]-$#,##0
[Color4]$#,##0;[Color3]-$#,##0
This will display Ten Dollars as $10 and Negative Ten Dollars as -$10
Once again to save you trialing each color you can see the effects of each color on a white and Black background below:
Warnings:
I haven’t tested it but I am sure the Color Names will be different in different language versions of Excel.
I haven’t tested these techniques on a Mac version of Excel but I am pretty sure these techniques should work.
Forward compatibility should be ok, but can’t be guaranteed.
