Hello awesome folks. It has been a while since I posted on Chandoo.org. And there is a reason for that. As you may know, recently (on October 12th) a category 3 cyclone (hurricane) passed thru our city devastating trees, power lines, cellular towers, old houses & roads on its way. This means our family was left without power, water, telephone and internet for almost 10 days. Early last week we got power & water. Then slowly the internet started working too. (more on this here)
I am swimming thru heaps of email & backlog work. Thanks to everyone who emailed me with kind thoughts, prayers and love. I can’t tell you how thankful I am for having you in my life.
I am really glad to be back online, sharing my stories, knowledge & tips with you all.
As it has been a while, I want to share a few quick announcements first.
#1 – The VLOOKUP Book Anniversary
Around this time last year, I published my first book – The VLOOKUP Book. As the name suggests, it’s a comprehensive guide to Excel lookup formulas. We sold more than 1300 copies of this book in first year. And the feedback has been overwhelmingly positive, with many 5* ratings on Amazon.
To celebrate the first anniversary of this book, I am running a VLOOKUP Sale on 30th & 31st of this month (Thursday & Friday). In this 2 day sale:
- You get 50% discount on The VLOOKUP Book
- You get 25% discount on The VLOOKUP Book + Video combo pack.
- Remember, the sale starts on 30th October morning (Japan time) and ends on 31st October midnight (Pacific time)
To avail this sale, just go to The VLOOKUP Book page.
#2 – Introducing ready to use Dashboard Templates from Chandoo.org
This is the top most request from our customers & readers. A ready to use dashboard template that is easy, intuitive & awesome. So during the cyclone inflicted downtime, I created a set of ready to use dashboard templates. I am still polishing the product. This will launched on 13th of November (Thursday).
This is how our templates can help you:
- Type your data and generate beautiful dashboards. That simple.
- Generate 9 different dashboards from one set of data. Click & choose what you want.
- Customize with ease. Change currency codes, financial year starts etc.
- Build your own calculations and the template displays them just as beautifully.
- Ready to show, ready to print, ready to publish – All in one awesome bundle.
- Save time & worry about things that matter.
Here is a sneak-peek (click on it to enlarge):
#3 – 50 Ways to Analyze Data – Coming in Jan 2015
Last month I asked you to tell me the challenges you face when analyzing data. Based on all your feedback, I am designing an analytics course to help you most. It is 15% done. I will be completing rest of the course development during November & December.
So the 50 ways to analyze your data course will be launched on 21st of January 2015 (Wednesday).
Click here to sign up for the waiting list of this course.
I will email you details about the course as they get ready.
So that is all for now. Tomorrow, I will come back with an awesome Excel tip. Until then…
17 Responses to “Custom Number Formats – Colors”
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.
@Duncan
Each version of Excel, post 2003, renders colors slightly differently
Different language versions may also have different default color palettes
Hello in french
excel 2010
colo1 = couleur1 = black
[couleur1]; [couleur2]; etc..
@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
@Andras
Without a Hungarian version of Excel 2003 I don't think I can assist
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]
Here a possible solution: find an English version of Excel, write there the formats using English names, then open the file in the Hungarian version and see the translation.
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!)
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?
Unfortunately the TEXT function doesn't color the cell as number formatting does.
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.
[…] explains how to set up custom number formats with a wide array of […]
Thanks Hui - works a treat!
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?
Exactly what I was looking for - thank you!
colour in the Australian doesn't work - we have to go American and no problem.
I always thought is was 56 colours notice you have 57. Cool.
thanks
Analir Pisani
Customised Microsoft Office Training Specialist
Sydney - Australia
http://www.azsolutions.com.au
Thank You!