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!
https://share.adobe.com/adc/document.do?docid=d683d417-9cf0-11dc-8fe2-d702873d34e2
