Friday, July 6, 2007

Create a custom Excel color palette

Microsoft Excel was never meant to be a graphic layout tool, but that doesn't eliminate the need for Excel documents to look nice. I work for a financial firm, so naturally we do a LOT with Excel, and I'm always being sent Excel files to "make pretty", or given spreadsheets for reports and presentations that must look nice. After getting really tired of manually tweaking Excel colors to match company colors and de-uglify spreadsheets, I finally put together a complete custom palette that incorporates our company colors, logo colors, and coordinating colors of various hues.

Did you know that you can actually customize every color available in the Excel palette, and then apply it to any spreadsheet? Here's how to design a custom Excel color palette so you can define your colors once and never have to worry about them again...and easily apply your color variations to existing spreadsheet.

1. Plan the palette
Take a look at the standard Excel color palette. It contains black, white, a range of grays and then several primary colors in varying tints. In other words, a selection of primary colors and tings and shades of those colors. When you design your own color palette, consider how your company color palette maps to these swatches. So if your company color is green, you probably want to replace all the greens in the Excel palette with tints (lighter) and shades (darker) of your company's green color. Most likely you also have other colors you frequently use, so in your custom palette you'll want to place these colors in the location that's closest to that color.

Keeping your custom colors in the closest possible palette location to their equivalent in the standard Excel palette ensures that when you apply your palette to a document with colors already used, that the colors stay relatively the same, but with the proper shades assigned.

2. Set up the document
The way I went about designing my custom palette was to create a series of square blocks in Illustrator (you can also use PhotoShop or any good graphics tool with color capabilities, but you'll see in a minute why Illustrator is great for this), 8 blocks wide and 5 blocks high. This replicates the number and location of the swatches in Excel.

Set up the document - notice I've pasted a screen shot of the Excel color menu for a reference

3. Define the colors you know you'll need
Then, I colored the blocks corresponding to their location on the palette with the colors I knew I wanted - black, white, grays, and my company's main logo color, picking the location on the grid that was closest to my custom color. This left me with about 75% of my blocks still uncolored. Then I used Illustrator's Color Guide palette and Live Color dialog to expand on these colors (which makes it super fast and easy to create complete palettes - hence the reason for using Illustrator!).

These were my starting colors - colors from our existing palette.

4.Fill in any missing color spots with a coordinating equivalent
The color guide can show lots of variations on the selected color, by tint/shade or vivid/muted, so most of the work of creating new shades of my primary colors was already done for me, it was just a matter of picking what looked good. I added lighter tints and darker shades of my starting colors. Then I picked a coordinating color from each hue in the spectrum (red, orange, yellow, green, blue and purple) to be my "base" colors. I put these colors in the second row from the top, which seemed to be the closest hue/value match for them.

For the blocks directly below those colors (lighter shades of those colors), I created a discreet series of tints, starting with 80%, then 60%, 40%, and 20%. This created a nice variation of tints that should work for many uses. After this there were a couple blocks left over, so I filled those with neutral browns and tans.

My complete Palette, in Illustrator

5. Bring your palette into Excel
Now I had a complete color palette, which I needed to bring into Excel. This was the "there's no way around it" un-fun part. Open a blank spreadsheet, go to Tools > Options and pick the Color tab. Here you can select each color in the palette and hit the "Modify" button to change it. If you go to the custom tab in the Modify dialog, you can type in custom RGB values. Go back to Illustrator, and get the RGB value of each color block, and transfer it to Excel. Having the color palette open and displaying the RGB values for the selected color in Illustrator makes this easier. Manually go through every color in the Excel palette and change it to your custom color. Don't forget to save the document early on so it starts auto saving, because this takes awhile and God forbid you lose this work!

6. Use the palette
Once you've assigned all your colors, save the document in a place you'll remember. Then to use it, open up both your color palette spreadsheet and the document you want to colorize. Go to Tools > Options in your working document, and go to the Colors tab. You'll see at the bottom of this dialog there is a drop down of all open documents, with an option to "Copy colors from" (see figure above). Select your palette document, and hit OK, which copies the colors from it to your new document. Viola, your custom colors are now available in your new document! If there were colors already assigned in your new spreadsheet, they should have shifted to match your new palette.

I also colored the cells in the first sheet of my custom palette spreadsheet with one cell corresponding to each color in the palette, just so it was obvious what was in that document (since the color settings are not initially invisible) and so you can get a bigger preview of all the colors for future modifications. This is also helpful for when you share the sheet with others. You may also want to try printing your swatches just to be sure they still look good when printed.

Update: if you'd like a copy of this palette, you can download it from the link below, via Adobe SHARE!


Dave said...

Thanks for this - Big Help!! I was very frustrated till I read your post.
Kind regards,

Rae said...

I'm glad it helped you! I know I would have saved many hours over the past several years if I had learned to do this sooner :)

Tom said...

any chance of a copy of your palette?

Rae said...

Yes, please email me at the address above - IE my blog name at gmail! I'll be happy to share it.

Neha said...

Hello.. This stuff is great.. can I get a copy of it at...

Kim said...

Thank you so much for the clear instructions and the spreadsheet for download! You saved me a lot of time on a project that is very tight on time.


Kim said...

One question - can I use Fill Patterns as part of my palette? I need to create on palette for color printing and another for black and white. I don't see how I can incorporate fill patters in a B/W palette. How do you handle charts for folks who need to print in black and white?


Sue said...

Thank heavens I found your instructions. I rarely work in Excel and now have an assignment to create custom color palettes for an investment firm. This is very helpful, great instructions.

Anonymous said...

GREAT tutorial, thanks a LOT!

Monica said...

I used to do this in excel 2000, but I can't seem to change the standard palette in excel 2007. There is a way to change what colors will be seen in *previous* versions of excel. but no way I can see to modify the palette in the current version of excel.

Rae said...

I just started using MS Office 2007 at work recently, and I was thinking I would have to find out if this info still applies. I will post my findings soon!

Anonymous said...

This is great and it displays the new color in my worksheet, but when I print (purple for instance) I still get the reddish purple instead of my custom purple.

Anonymous said...

Please can you also send to me :

Many Thanks

Do you ever have a situation where the custom color palette is reset to excel default and the entire workbook formatting reverts to standard colors? I email reports to others and they open the .xls to find a mess of oranges & greens instead of the color scheme I designed!

Wendy! said...

A true story.
I was working on an excel file.
Multi-tasking with a few different things, some not even excel.
One moment I had the color palette I was used to - the next time my file got focus the palette was all different, not standard and the whole file was ugly.
I know how to fix it, but HOW DID IT HAPPEN??
I can't figure out how to MAKE it happen without multiple steps... so how did I do it by accident?
I don't NEED this answer, but it might be the final incident that pushes me over the brink of insanity...

Anonymous said...

Very Useful.

Thank you for sharing.

PJ said...

Thanks very much for your explanation and for sharing your work !

What a relief for our eyes !

Warsmoke said...

Thanks for sharing this colour palette. It's a much nicer scheme than the default one.

G-sj said...

this is awesome!

Andy said...

Hi, We have recently been updated to Office 2007 at work and i was wondering if you had had any luck working out how to change the standard palette in excel 2007?


Anonymous said...

Me too. I installed Excel 2007 today and was immediately herded into a variety of canned color themes when I went to see if my custom color palette survived the upgrade. Nope. And no obvious way to get it back either. I'm worried I will have to dial in each custom color every time I use it. Erg. Microsoft!

Anonymous said...

Thanks for the help and for the free download - this has saved me a lot of time and helped me a lot!!!

Anonymous said...

I found the answer to costomizing the color fields.
Hit F1 and within Excel help, look for this:
Apply, customize, and save a document theme in Word or Excel

I have just created my own theme in 20 minutes.

cdgnfg said...

Today is the gold für wow second day of 2009 ,world of warcraft gold it also a mesos special for me .cheap wow gold Because i have cheap maplestory mesos a chance to go to an english speech of LiYang and crazy to learn english follow him . He is a firendly,kindly person who make me feel gold kaufen What‘s more ,maple story mesos he very confidence .And he make me sure what he can do i also can do,as long as i make a determination and force myself to do it every second,Crazy just like him .wow geld I learn one setence from his book ever :maple story items If you want to succeed always force yourself to do more .I can't agree more with him .wow gold farmen To be honest ,when i was a littel girl i already fall in love with english.But what a pity i am poor in english ,Maple Story Account and it make me feel frustrating .So i want to give up many times ,but i can't as i still love it .So i tell mysefl :if you think you can you can wow leveling,and all your maple story power leveling hard work will pay power leveling Today i am very happy i can listien this wonderful speech of LiYang. I reap a great benifits from him .maple story money As he say :i am the best ,and every one can do it . world of warcraft power leveling Yes,i belive i can do it if i crazy as he

Anonymous said...

When I needed a different palette I taught that you can enlarge the palette by creating a sheet with, let say, 256 different colors and copy with the brash from that sheet to your sheet. I was sure that some one has already done the work and "get out" looking for it in the internet. Only when I found your palette and tried simply to copy it to my sheet I discovered the limitations of Excel. If you try to copy you get a color from execl's default colors. Only then I return to read your explanation.

By the way, some one now about good recommendations for display appearance values and DPI for a high definition 1000X1400 laptop screen (it is hard to see internet pages).

Thanks for your great palette and for the well introduced explanations.


Danielle said...

Great advice, thanks!

Sarad said...

Hi Rae,
Any development on the Office 2007 front?

piroshka said...

Hi Rae,

this is great. I knew about most of this before, but your Illustrator process is just about to make life easier :)

Cheers for this,

Pinto said...

Thank you, this is exactly the info I was hoping for - but to supply the pallette file itself was simply superb!

BKrisanski said...

Hi guys,

I have recently also upgraded to Excel 2007 and also wanted to bring over my modified colour palette. Trawling over the net I soon found out how to modify the colours in 2007. Simply follow these instructions:

1. Start Excel, and then open your workbook.

2. Click the Microsoft Office Button, and then click Excel Options.

3. Click Save.

4. Next to "Choose what colors will be seen in previous versions of Excel" and then click Colors.

You can then change whatever colours you need etc basically the same as described in this blog post.


Anonymous said...

You ROCK!!!

Kennen said...

Once you change the colors in "Choose what colors will be seen in previous versions of Excel"how do you go about using those colors in other Excel documents. They don't seem to show up for use in the document. How do you bring in these new colors to a new Excel document?

Watson said...

Love it. It's much better than the default palette. Now my tables and charts look so nice.
Can't wait for the second one really.
Thanks a lot.