Tuesday, January 12, 2010

Design tip: Custom colors in Excel (97-2003)

As metioned in the comments section on the Slide:ology review, I've made myself a custom palette in Excel (2003, that is). Since this is the version available at work, I've become more and more frustrated by the colors available in Excel and spent too much time editing colors (see end of this post for "how-to" change colors in Excel).

Anyway, below is my current palette (left) and the standard Excel 2003 palette:

Let me explain the background for this. You can see that the top right colomns are identical in both paletts. The reasons for that is that shades of greys are cool, but mainly because I couldn't think of any useful colors to replace them with. Above the line, you see that the rest of my palett is of different shades of grey, red, blue, orange and greens in the rows. Good to have the options of different shades of one color, but also note how well the colors in each colomn goes together.

Now for the bottom two rows. You can see that the rows consist of pairs of colors, one deep, saturated and one lighter shade. The thing is that all the rich colors goes well together and all the light colors goes well together, if you need multiple colors in a chart. (On a sidenote: The observant reader might have notice the odd couple out, the two colors on the bottom right. I initially made a deep/light color pair there as well, but later figured I needed the strong red. And that orange there is one of my favorite colors, which I also missed a bit). But here is the smart part: As a default, Excel starts choosing colors in charts from the bottom row. Hence, I get some useful colors by default, and saves work! Let me illustrate this with an example. First off, there is my palette (yes, I've removed the grey background and formated the axis, but the series colors are by default):

This is actually a set of good colors, good contrast without overpowering each other. Also the deep colors makes a good contrast to the background, and both lines and text is easy to see. Now, the same graph, with the same formate on the axis, but with the colors choosen by Excel with the default palette:
Quite a difference, no? The deep blue color on "East" is fine by itself, but the colors doesn't go well with each other and makes the graph look tacky. The "West" series is very muted compared to the others and the text is hard to read. The "East" and "South" series stand out more then the two other series.

Anyway, this is a continious experiment for me, and the palette on the top may not be the one I'm ending up with. But I sure gonna make my own palette, because it is so much better then the default one. So, here are my pro's and con's of the current palette:

Good selection of shades of each color. Great for tables, lines, etc.
Almost all the colors in the palette goes well together (as long as I choose the same level of saturation)
Better default colors in charts, at least to some extend.

I really would like some more yellow shades.
The reds aren't shades of reds. They are pink, and makes my chart look like a three year old girls coloring book.
I would love to be able to replace the default grey background in charts with white, but I'm afraid changing a grey to white would affect something else that needs to be grey.

It's worth noting that from Excel 2007 this is not as big issue, since Microsoft changes the whole color tool in that version.

How to change the default palette in Excel.
From the Tools meny, click Options and select the color tab. Select a color, click modify, and change it to whatever you like (bonus tip: On the bottom, you can import the palette of any open Excel-fil). Our palette will be saved for that Excel file and all the sheets in it. Any of the old colors you've used in that file, will be replace with the new you've defined. But once you open a new Excel file, it will still have the standard palette.

If you want to save a custom palette, and have it as default in new Excel files, you have to make it the default woorkbook. Save it as an Excel template file, with the name "bookxlt" (or "bok.xlt" in Norwegian) in the xlstart folder. The folder is usually located at C:\Documents and Settings\USER\Application Data\Microsoft\Excel\XLStart, where USER is you login.


Wilhelm said...

The top RIGHT columns are identical, you mean?

Good stuff, man. How did you pick the various hues? Trial and error? Is this directly transferable to PP?

Anders said...

Good call on the type-o, mister. It's been corrected.

No, it's not transferable to PowerPoint, since powerpoint isn't limited to a 56 color palette. You can basically choose any color you like.

The colors: I basically found some good colors on the interwebs, used a screen screen grab and a color picker to get the values.

Wilhelm said...

Cool, then

Anders said...

No. I'm more like lukewarm water...

Anonymous said...

Would you be willing to post a blank excel sheet that has your color pallete so that I can import it? I've edited my colors but not to this extent... its great!

Lee said...

Please post a workbook so that we can have your beautiful colours!

Phil said...

I would also like to have your color palette. Do you think it's possible?

Thank you!

Anonymous said...

Can you post a macro that shows us how to create that palette? I thought Excel 2003 only let's you use 56 colors or no color? Do you think it would work for 2007 or 2010?