 | Color Palette Conversion for Excel 2007+ | [email_link] |
Download
Note: Enable macros when opening.
See this blog post for further explanation and user comments –
Excel's Color Palette Compatiblity Issue & Solution
Video Tutorial
Screenshot
Help & Instructions
Click the help button 
in the file to see onscreen instructions (macros must be enabled).
Help Page – online version
Description
The Color Palette Conversion tool automates the process of updating the previous version 56-color palette in Excel 2007 (and Excel 2010) workbooks. Excel 2007 files actually contain two color palettes. The one you work with is the new theme based color palette. The 56-color palette from previous versions is also stored in you workbook, and can be modified. When a workbook created in 2007 is opened in a previous version, Excel automatically converts the new theme colors to similar colors in the default 56-color palette of previous versions. This can really change the look of your spreadsheet design and cause problems for users on older versions of Excel. The conversion process is explained in more detail below.
This tool will allow you to quickly update your previous version color palette. With the click of a few buttons you will be able to retrieve all the colors you've used in your workbook and then apply them to the previous version palette, replacing the default colors of the previous versions with new colors from your theme.
Features Include:
- Retrieve all colors used in your workbook.
- Easily create a custom previous version color palette with colors used in your workbook and/or colors from your theme palette.
- Update the previous version color palette in one or multiple workbooks.
- Restore palettes to default settings if needed.
- Retrieve colors currently stored in previous version palette from any workbook.
The Color Palette Conversion tool will ensure that your beautifully designed workbooks will be viewed the same way by all users of Excel. This is crucial if you are:
- Sending workbooks to customers or colleagues and referencing colors.
- Designing a workbook where presentation is critical.
- Creating a workbook for public use.
- Sick of wasting time manually adjusting the previous version palette.
Color Palettes Explained
Excel 2007 and later versions use a theme based color palette which includes 10 themes colors with 6 shades/tints for each, and 10 standard colors.
Excel 2003 and older versions use a 56 color palette.
When a workbook created in Excel 2007 or later is opened in a previous version of Excel, the new colors are converted to their closest match on the 56-color palette.
Some of the conversions are close, but can still make your workbook look like the following.
And here's that same file opened in a previous version of Excel after using the Color Palette Conversion Tool.
Blog Articles
Excel's Color Palette Compatiblity Issue & Solution
Contact us with questions, comments, ideas for improvement, or additional services.
Thanks for your video.
Ahsan
Hi Jon,
Seems MS may have issued an Office update (or something has changed in my config) that has stopped this tool from working correctly. I last used it successfully over a year ago now, since our organisation has (finally) moved away from XL2003 for the vast majority of users. Today however I find myself with an xlsb workbook containing custom pre-2007 colors that I want to get rid of (because the cells containing these custom colors look awful when copied and pasted into a different workbook). I loaded up your tool but experience the following problems:
1) The combobox controls are not clickable
2) When clicking the “Update previous version palette” button (“Picture 14”) the associated VBA throws a “variable not defined” error when it encounters any reference to a named Forms 2.0 control (e.g. “UpdateWbBox”) on the main worksheet.
Do you know if this is due to some deprecation of Forms 2.0 (as far as I recall, it’s ActiveX controls that were notoriously broken and never properly fixed by MS as part of a security patch) or am I missing something opvious?
I tried re-downloading the CPCtool in case I’d meddled with my copy and forgotten doing so, but teh fresh copy has the same problem as described above.
I’m on XL2007 (12.0.6611.1000) SP3 MSO (12.0.6607.1000)
Thanks for any assistance you’re able to offer.
Hey Bruce,
I’m sorry you are experiencing issues with the tool. It’s been along time since I looked at it or used it. I’m not sure what would be causing that in 2007, and I don’t have a machine to test with right now. You are right about the ActiveX controls in 2007, but the tool did work fine in 2007 when I initially built it.
This tool will not change the color palette in 2007. It will only change the palette if you open the file in 2003.
Hi Jon and thanks for getting back to me. Yeah, I figured it might’ve been a while since you looked at it, and yes I never had any problems with it before!
Not to worry. Despite searching prior to posting here (and being unable to find what I was looking for) I eventually figured out that XL2007 retains the ability to modify/reset any file’s 2003 palette. For anyone with the same issue as me: open the file, go to Excel Options, select the “Save” section, click “Colors” > “Reset” > “Ok” > “Ok” > Save.
Re your last point: surely if a file is utilizing old style indexed colors (even if it’s now saved as a 2007+ file format) then modifying the 2003 palette will have an effect no matter what version of XL you open the file in? Certainly that’s my experience.
Thanks again!
Bruce
Why can’t I get yellow?