Custom Number Formats – Colors

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.