fbpx

Custom Number Formats – Colors

Share

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn

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.

 

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Chandoo is an awesome teacher
5/5

– Jason

Excel formula list - 100+ examples and howto guide for you

100 Excel Formulas List

From simple to complex, there is a formula for every occasion. Check out the list now.

20 Excel Templates

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Weighted average formula in excel

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

14 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

    • Hui... says:

      @Andras

      Without a Hungarian version of Excel 2003 I don't think I can assist

    • Sarah says:

      Have you tried using the colour numbers? I couldn't get the names to work (despite using an english version of excel). but it did work with the numbers though. I left out the "u" and was easily able to produce burgundy using [color9]

  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!

  11. John Smith says:

    Thank you, very helpful.
    Trying to figure out if it is possible to apply color only to a part of the cell?

    E.g. I have a value formatted as Accounting with a currency symbol.
    Those I find somewhat distracting though necessary. If I could make them less obtrusive by coloring them gray while the number would stay black, that would be great. Tried tinkering with the format string, but didn't get the desired result. Single color for complete cell value works, but coloring just part of it could not be achieved. Maybe somebody managed that?

  12. Shaun says:

    Exactly what I was looking for - thank you!

Leave a Reply