Custom Number Formats – Colors

Posted on February 3rd, 2015 in Excel Howtos , Huis , Posts by Hui , Quick Tip - 11 comments

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:

2003 Color Picker 2

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:

2003 Color Picker3

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:

Color Numbers

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.

 

Written by Hui...
Tags: ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

11 Responses to “Custom Number Formats – Colors”

  1. Duncan says:

    You are right, Chandoo. I was playing with the colour numbers last week and some of them don't appear different from each other. Others are totally different from yours.

  2. Hui... says:

    @Duncan
    Each version of Excel, post 2003, renders colors slightly differently
    Different language versions may also have different default color palettes

  3. polo says:

    Hello in french
    excel 2010
    colo1 = couleur1 = black
    [couleur1]; [couleur2]; etc..

  4. Andras Ujszaszy says:

    @Hui, thank you very much again for this great post.
    However - under Excel 2007, Hungarian version your solution does not work with color names. I've tried both English and Hungarian names, but drops an error message "not valid formats"

    Do you have any idea how to solve this issue?
    thanks in advance

  5. Nigel says:

    In Excel 2007 I can't get the colour names to work e.g Sea Green but the numbers do e.g color3 - colour3 does not work so I must bow to the country that has stolen my language (ha ha!)

  6. Hey chandoo, nice Tip!
    Wouldn't be easier just apply some conditional formatting for negative numbers and another for positive numbers? Or there's some cases that you can't do that?

  7. Unfortunately the TEXT function doesn't color the cell as number formatting does.

  8. Khalid NGO says:

    Hi Hui,
    Great post Sir, love the new way of formatting with color numbers.
    I am using 2007, and it leads me to the last color number 56.

    Thanks Hui.

  9. […] explains how to set up custom number formats with a wide array of […]

  10. Colin says:

    Thanks Hui - works a treat!

Leave a Reply