Master Excel with my tips, tricks & tools!
Subscribe to my free email newsletter
& receive a Free Gift!

Excel’s Color Palette Compatibility Issue & Solution

In the following video I explain the difference between the color palettes and a solution for making your new workbooks look the same when opened in Excel 2003 or earlier.



Excel 2007 workbooks are Ugly in previous versions

The new theme based color palettes in Excel 2007, 2010, and 2013 are great for designing spreadsheets and charts that are visually appealing.  But did you know that those colors are being converted to the old 56 color palette when opened in a previous version of Excel?  This means your new spreadsheets could look like the following when opened by clients or colleagues using Excel 2003 or earlier versions.

Excel 2007 Table Colors Converted Earlier Version
Click to Zoom

The Previous Version Palette

Excel 2007 workbooks contain a 56 color palette that will be used when the file is opened by previous versions of Excel.  You can see this palette by opening an Excel workbook and going to: Office Button > Excel Options > Save (option in left sidebar) > Colors… button (after: Choose what colors will be seen in previous versions).

This is the default color palette in older versions of Excel, and Excel automatically converts your new theme colors to these colors.

Excel 2007 Color Palette Converted Earlier VersionFor users of previous versions to see the same colors you do, this previous version palette must be updated with the colors you’ve used in your workbook.

Does this sound confusing, time consuming, and frustrating? Well don’t worry…


Here is your simple and free solution!

The Color Palette Conversion Tool is a simple utility that retrieves the colors you’ve used in your workbook and updates the previous version palette automatically.  With the click of a few buttons you will ensure that all Excel users see the same colors you do in your fonts, borders, and background fills.  This is critical for presentation purposes, and general sanity if you have Excel 2007 at work and a previous version at home.

The tool has a few advanced features that allow you to control your previous version palette colors, plan for future designs, make updates in multiple workbooks, and easily view or restore default palette colors.  The tool is just a single worksheet in a workbook that contains macros to run this process.  You can use it as a stand alone, or add in to your workbooks if you want to make frequent updates.

Here is what your spreadsheets will look like in previous versions after using the Color Palette Conversion tool.

Excel 2007 Table Colors Converted with Tool
Click to Zoom

Save time and eliminate color confusion

If you’re currently going into your previous version palette and modifying colors, you know how time consuming it can be to change the colors.  You have to manually type in the RGB codes for each color you use from the new theme palette.  It’s a painful process, but must be done if you want your spreadsheet colors to be universal with all Excel versions.  This tool eliminates all that work.  I explain the color palettes in more detail on the download page.  I hope you find this tool useful and please post questions or suggestions below.

Color Palette Conversion Tool Screenshot

Excel 2007-2010 Color Pallette Conversion Tool Screenshot
Click Image to Zoom

Download Page & Instructions

Excel 2007 Color Palette Conversion Tool

Color Palette Conversion Tool

YFGY29S8735D

Come Learn Excel With Us (it's Free!)

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "hey, how did you do that?"

You will also received email updates with new blog posts and videos to help you save time everyday and learn Excel.

CLICK HERE TO DOWNLOAD MY FREE EBOOK

23 Responses to Excel’s Color Palette Compatibility Issue & Solution

  1. Very nice tool, but I’m not sure I’m doing it right, or maybe something is missing?

    The tool maps ‘Color 1′ from an new sheets Color Palette into ‘Color 1′ in an old sheets Color Palette (in my case Excel 2003 format).

    This does not result in a nice looking sheet in my case, as the colors are not located in matching areas, e.g. the gray scale colors in Excel 2007 are mapped as colors 1-6 and as colors 43-48 in Excel 2003 (and goes from dark to light).

    It would be nice to automatically change the color at the position, which my Excel-2007-custom-color is actually mapped to in Excel 2003.

    Example: Excel 2007 color 26 (light blue) is converted to Excel 2003 color 48. Thus I want color 48 from Excel 2003 to get the color of position 26 from Excel 2007.

    Any help is greatly appreciated :)

    Regards,
    Christer, Denmark

  2. This looks really cool.
    Would it be difficult to develop a more simplified interface? Say just a ribbon interface that has the 2003 color palette for cell fill colors…when you select cells and click on the color…it changes the cells to the selected colors?

    • Thanks Chris. I agree that it could be simplified so you don’t have to open the color conversion workbook each time.

      It sounds like you want to be able to use the 2003 colors in your 2007 workbook. This tool actually does the opposite. It allows 2003 users to see the 2007 colors you’ve used in your 2007 workbook by preventing Excel from converting the new colors to the older 2003 palette.

      So a tool that does both might be the ultimate solution? hmmm…

  3. Can we have the 2003 palette as an option on 2007.

    The 2010 colours seem very dull, and it’s a lot of work to keep recreating the previous colours

    Thanks for any advice

    Paul

  4. CORRECTION

    Can we have the 2003 palette as an option on 2010.

    The 2010 colours seem very dull, and it’s a lot of work to keep recreating the previous colours

    Thanks for any advice

    Paul

  5. Seems like many people would prefer to keep the 2003 and previous color palette; I am in the boat as well.

    As noted, this is the opposite approach of the handy tool provided here, but I’m wondering if there is a solution that makes it easier to use the “traditional” color palette in Excel 2007 or 2010.

  6. Yes yes yes. Our team has just discovered the color palette limitation in 2007 (and we assume 2010 too). Our problem is not cell fills, but graphs, carefully assembled so the colors mean something. We’ve considered going back to Excel 2003 but hope that this tool is our answer. The 2007 colors are awful in themselves, and the lack of a palette (as opposed to this thing they call themes) is a big, big problem. What were they thinking?

  7. Oops, just read the comments that say the tool is for using 2007 colors when viewed in 2003. Great idea, but not what we’re after. Ah well.

  8. I’ll definitely be playing with this tool.

    But I’m with the others who would like to see “Menu” palette in “Ribbon” apps. I too find the new color schemes too blah and grey and boring.

  9. I have a related problem: I created a bunch of charts (3D graphs) in Excel 2007 that I thought looked OK. Then I lost access to Office 2007 and fell back onto my copy of Office 2003, which with the appropriate extras can open Excel 2007 files (albeit with some features missing, obviously).

    The problem I have is that my Excel 2007 charts look awful when opened in Excel 2003. I’m trying to find a way to import/convert the original 2007 palette into 2003 so that I regain my color scheme (even if the graphic quality isn’t as good) – but without access to 2007. Everything has to be done from within Excel 2003.

    I had the foresight to extract the 2007 template I created before I lost access to Excel 2007; I don’t know whether I can use that in 2003 (doesn’t appear so, from my research).

    Grateful for any thoughts.

  10. Thanks for this tool. Stunned by the comments that want to use the old EGA colors in the new versions of Excel. I realize this is somewhat subjective and a matter of opinion, but from a design and readability perspective, the old colors are from palettes no longer widely used by professional designers, because they are harder on the eyes and look dated. The new palettes are better and kinder to your audience (look at the colors used in tables or graphs in any high-quality professional publication). For comparison, in my opinion, the old colors ensure results look projects done by a highschool student, where the new ones give a professional look. Or, like the early web sites of the ’90s with all the animated GIF’s all over the pages, vs modern more subtle and elegant web pages.

  11. I have two secondary problems when using your tool.
    I used the tool on a file, and then edited it in Excel2003, which all worked fine and retained the 2010 Theme Colours.
    Problems are when you go back to using Excel2010 on the file:
    1) if you cut+paste the data into a new sheet it changes the colours – it seems to use the “Previous Version” palette to derive the new colours.

    2) the edited file no longer changes colours when you change the Theme Color setting – and this is also true for the copy made in problem 1).

    Pls. can you look for a way to fix problem 1) at least?

    Update

    Hi Jon,
    I was going to say that I noticed that my blog entry was inaccurate. The cut+paste problem occurs when copying to a new workBOOK. There are no problems copying to a new worksheet.

    But actually I think I know what’s happening.
    When you edit a 2010 file using 2003 or earlier, the 2010 Theme colours in the file are changed to be marked as “from the Previous version palette”

    When 2010 re-opens that file, it doesn’t realise the colours used are the same as the Theme colours it is using (even though to the human eye they look the same), and so doesn’t change them back to Theme colours.

    I think this explains both of the problems I reported.

    The copying into a new workbook will be to a new file which will have a default Previous version palette, and hence the colour changes during copying.

    And changing the Theme colours in 2010 doesn’t change the colours that have been “through” 2003 or earlier because they’re not marked as coming from the Theme palette.

    So now the question is different – is there any way that 2010 can be persuaded (preferably automatically) to remap colours marked as from the Previous version palette back to the matching Theme colours?
    Hope this moves things forward.

    Regards,
    Graham.

    • Hi Graham,

      You are correct about the issue with 2010 not recognizing the color as a palette color after you have edited the file in 2003. An easy way to tell is by selecting a shaded cell and then looking at the Fill Cell palette. The screenshot below shows that the color is not recognized on the palette for the file edited in 2003 or earlier.

      Color Palette Recognition Comparison

      I believe the reason for this is that the cells contain additional properties for ThemeColor and TintandShade in 2007+, that are not compatible with earlier versions. So those properties are lost when you open the file in 2003. The 2003 properties basically contain RGB values for the color properties, and those are not matched to the new palettes in 2007+.

      Your question about remapping the colors is possible. It would require a macro to be run on the workbook opened in 2010 to check all the font and fill colors to see if they match any of the color palette colors. It might take a long time to run depending on the size of your workbook, but I believe it is possible. I’ll try to add this to the existing Color Palette Conversion tool when I get some time.

      Thanks again for taking the time to look into this.
      Jon

  12. I would like the old colors back for charts. The new ones are dull. Worse yet, they are not at all friendly to the color blind. How exactly do I make the new excel use the older color set by default?

    • Robert,
      I get this request for the old colors in the new palette a lot. I have a 36% solution for this. :) Excel allows you to create your own theme palette,but this is limited to 10 colors. However, you could create a theme palette with 20 of the 56 colors of the old palette. Here’s how to do it.

      At the bottom of the new palette there is a row of 10 standard colors. I believe 5 of these colors are in the old palette and the others closely match colors in the old palette. So there are your first 10 colors.

      Now go to the Page Layout tab in the ribbon, and click the Colors drop-down menu (located in the top left of the ribbon). Here you can change the theme colors, or even create your own. The issue is that you are limited to 10 different colors. The other 50 colors in the new palette are what MS calls a “tint and shade” adjustment on the 10 theme colors. Basically different hues of the theme color.

      Create a theme palette with the 10 colors you use the most in the old palette besides the colors that are already in the standard colors of the new palette. This will get you a palette with 20 of the 56 old colors. So 20/56 = your 36% workaround solution. :)

      You can use the Color Palette Conversion Tool to help you create the new theme palette.

      First, you can open some of your workbooks that contain the old colors, and use the “Get Colors From” drop-down menu in the Color Palette Conversion Tool to see which colors you use the most.

      Then, you can use the Previous Version Color Palette table in the Tool to help create the new theme color palette (10 colors). Click on a cell in the previous version palette and then click the Fill Color drop-down on the ribbon. Click the More Colors… button at the bottom of the drop-down. A box will pop-up with that color selected in the standard colors tab. You need to remember the location of this color in the palette (take a screenshot) and then select it when you are creating your new color theme. Repeat this for all 10 colors you want to use.

      I will write a separate blog post and create a video tutorial on this. I think it would help a lot of people that are looking to use the old palette in new versions.

      I believe there are some add-ins available that attempt to mimic the old 56 color palette. The major issue with an add-in controlling this, is that you typically lose the ability to undo. The add-in runs a macro and running a macro clears the undo stack in Excel.

      Let me know if you have any questions about this.

      Jon

  13. Edit: I should have mentioned that I am using Excel 2010 to convert from Excel 2003.

    I have used this tool successfully in the past (thank you!), but now when I open the file I get runtime-error-9-subscript-out-of-range. I am not a VB coder, I just “relocate” these tools from the internet. I am in a network environment. I select “enable editing” and then “enable macros” when the buttons appear, but when I try the drop-down menus to choose the files, I get the runtime error. When I select “debug,” the code highlighted is ” If Windows(wkb.Name).Visible Then”.

    What am I doing wrong?

    • Hi Angela,

      Thank you for providing details about the code that is producing the error.

      This could be due to a windows explorer setting that hides the file extensions. Try the following steps to show the file extensions in windows explorer, and then retry the color palette tool.

      To show file name extensions in Windows Explorer, follow these steps:
      For Windows Vista, Windows 7, and Windows Server 2008

      1. Start Windows Explorer, you can do this by opening up any folder.
      2. Click Organize.
      3. Click Folder and search options.
      4. Click the View tab.
      5. Scroll down until you notice Hide extensions for known file types, un-check this line by clicking the check box.
      Note To hide file name extensions, check this line.
      6. Click OK

      For Windows 2000, Windows XP, and Windows Server 2003

      1. Start Windows Explorer, you can do this by opening up any folder.
      2. Click Tools, and then click Folder Options.
      3. Scroll down and then click Folder and search options.
      4. Click the View tab.
      5. Scroll down until you notice Hide extensions for known file types, un-check this line by clicking the check box.

      Note To hide file name extensions, check this line.
      6. Click OK

      Please let me know if it still doesn’t work.

      Thanks!
      Jon

      • Thank you for your quick response! I followed your instructions, but that box was not checked. I am able to view the full file extensions in Windows Explorer, and even, sometimes, before I click the drop down, in the tool.

        I closed the 4-5 Excel files I had open, and restarted the program. When I closed these files, I received notices that the system had saved a recovery version of the files. Perhaps incidentally, I also broke a link from the file I wanted to convert to another file.

        I opened three files: the file I wanted to convert, a blank Excel 2010 file, and the conversion tool.

        RESULT: I was able to run the conversion tool.

        I am not certain which of the above issues disabled the conversion tool: 1) Too many open files, 2) an unnoticed crash and recovery, 3) a link to an unreachable file, 4) Something else or a combination of all three.

        I hope this experience helps someone else. Thank you, again.

        • I am glad you got it working. I am also not exactly sure which issue could have caused your error. I would lean towards the issue with the unnoticed crash. This error is likely due to file extensions, and the Excel crash will sometimes create backup versions of your previously open files and give them a different file extension.

          I really appreciate your detailed response and I’m sure it will help someone in the future.

          Thanks again!
          Jon

  14. I HATE the 2007-2013 scheme. I don’t think it’s appealing, I think it’s drab. Even using the custom color tool, I can’t get a green or blue I like. I want a took that works the OPPOSITE, makes the newer Excel colors match the old ones in 2003.

Please leave a comment

facebooktwittergoogle_pluslinkedinmail