OOXML Hacking – Chart Template Colors

Chart templates solve the old problem of having to send out copy-and-paste samples of graphs. But they only display the first 6 of the chart template colors you designed.

Adding More Colors

The most common application of chart templates is to overcome the design limitation of having only a 6-color palette to work with. I wrote about this problem years ago: Office Charts: 6 Colors Maximum! – Best Practices. After a chart uses Accents 1 to 6, it starts recycling those same 6 colors with automatically generated darker and lighter variations. Banks and wealth management clients often need many more than 6 data series in their charts, but still want to have colors that are on-brand and designed.

To create a wider range of data series colors, create a chart with the maximum number of data series that the client requires. Our record is 25 colors! Then right-click on the chart edge and choose Save as Template. This captures the formatting of the sample that you’ve created.

Your template will work just fine on charts that have already been created. Select the chart, choose Change Chart Type (yes, even if it’s the right type already), click on the Templates icon, select the chart template and click on Apply. Easy peasy, job done!

Insert Chart Templates folder


The New Chart Problem

But this process breaks down if you try to create a new chart from that template. Choose Insert>Chart, click on Templates, and select the chart type. Then start inputting data. As soon as you get past the 6th color, Office starts using those damn Microsoft default darker and lighter variations instead of the colors you set! This is Authoritarian Helpfulness at its worst!

What You Designed
Chart template colors as designed
What Office Gives You
Chart template colors as created by Office

Yes, you can fix this. Reapply the template by choosing Change Chart Type>Templates and reselecting the template. The colors are corrected to the design specs. But you shouldn’t have to do this! And now that you’ve found this article, you don’t have to.


Understanding Chart Template Colors

Let me back up a bit, and explain how chart templates are structured. The top level of the XML consists of [Content_Types].xml, a _rels folder for documenting the relationships of XML parts and a chart folder holding the good bits.

Inside the chart folder, we find chart.xml, which holds most of the formatting that we created when we made the custom chart. Chart.xml contains definitions that contain the intended series color. The colors in chart.xml get set when you save the file as a chart template, and they get used when you apply the template to an existing table. But they are not used when you create a new chart.

Each series is numbered starting with 0, so this is the series for the 7th color. For the first six, the color is set to an accent color. Starting with the 7th, the color fills are in RGB/hexadecimal. Here’s a sample:

<c:ser>
  <c:idx val="6"/>
  <c:order val="6"/>
  <c:spPr xmlns:c="http://schemas.openxmlformats.org/drawingml/2006/chart">
    <a:solidFill xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main">
      <a:srgbClr val="9AA4AF"/>
    </a:solidFill>
    <a:ln xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" w="19050">
      <a:noFill/>
    </a:ln>
    <a:effectLst xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"/>
  </c:spPr>
  <c:cat>
    <c:numRef/>
  </c:cat>
  <c:val>
    <c:numRef/>
  </c:val>
  <c:extLst>
    <c:ext uri="{C3380CC4-5D6E-409C-BE32-E72D297353CC}" xmlns:c16="http://schemas.microsoft.com/office/drawing/2014/chart">
      <c16:uniqueId val="{00000006-910C-4CE6-8943-18D1A951583A}"/>
    </c:ext>
  </c:extLst>
</c:ser>
 

In addition, there are subfolders for charts, media, theme, and _rels. The media folder only holds a BMP file that is used for thumbnail display in Office 2007 and 2010, but not in later versions. The _rels folder is for relationships of XML parts. The theme folder contains themeOverride1.xml. As the name suggests, this is a theme that overrides the theme of an Office file that hosts it. This allows you to use a chart template in any Office document while retaining the original design appearance. Finally, we have the charts folder, which has the XML part we’re looking for.

This folder contains 2 files: style1.xml, containing the formatting for all the chart parts and colors1.xml, with a list of default fill colors and transforms fo apply to those colors. Colors1.xml is the file that supplies the colors when a new chart is created from the template. Here’s what colors1.xml looks like:

<cs:colorStyle xmlns:cs="http://schemas.microsoft.com/office/drawing/2012/chartStyle" xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" meth="cycle" id="10">
  <a:schemeClr val="accent1"/>
  <a:schemeClr val="accent2"/>
  <a:schemeClr val="accent3"/>
  <a:schemeClr val="accent4"/>
  <a:schemeClr val="accent5"/>
  <a:schemeClr val="accent6"/>
  <cs:variation/>
  <cs:variation>
    <a:lumMod val="60000"/>
  </cs:variation>
  <cs:variation>
    <a:lumMod val="80000"/>
    <a:lumOff val="20000"/>
  </cs:variation>
  <cs:variation>
    <a:lumMod val="80000"/>
  </cs:variation>
  <cs:variation>
    <a:lumMod val="60000"/>
    <a:lumOff val="40000"/>
  </cs:variation>
  <cs:variation>
    <a:lumMod val="50000"/>
  </cs:variation>
  <cs:variation>
    <a:lumMod val="70000"/>
    <a:lumOff val="30000"/>
  </cs:variation>
  <cs:variation>
    <a:lumMod val="70000"/>
  </cs:variation>
  <cs:variation>
    <a:lumMod val="50000"/>
    <a:lumOff val="50000"/>
  </cs:variation>
</cs:colorStyle>

On a newly inserted chart, an Office program will use the 6 colors in turn. Then it will cycle through them again, applying the a:lumMod val=”60000″ transform. LumMod modifies the luminance, turning each accent color to a dark version. The chart will follow this pattern instead of using the colors you designed for the extra data series. If you reapply the chart type to the chart, then it will update with your designed colors from chart.xml


Fixing Chart Template Colors

My first step was to read the Microsoft specs for cs:colorStyle. Under section 2.8.3.2 CT_ColorStyle, it reads: “The total set of colors is all contained colors repeated each time with each variation applied. A color style can contain 6 colors and 7 variations. This yields a total of 42 colors with the first 6 having the first variation applied, the second 6 having the second variation applied and so on.” Not promising, we really need more than 6 colors here.

After many fruitless experiments, I decided to see if I could add extra colors anyway:

<cs:colorStyle xmlns:cs="http://schemas.microsoft.com/office/drawing/2012/chartStyle" xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" meth="cycle" id="10">
  <a:schemeClr val="accent1"/>
  <a:schemeClr val="accent2"/>
  <a:schemeClr val="accent3"/>
  <a:schemeClr val="accent4"/>
  <a:schemeClr val="accent5"/>
  <a:schemeClr val="accent6"/>
  <a:srgbClr val="9AA4AF"/>
  <a:srgbClr val="C2C6C9"/>
  <a:srgbClr val="B76646"/>
  <a:srgbClr val="E2C2B5"/>

To my astonishment, it worked! I could create a new chart and insert 10 dataseries with each displaying the designed color. No need to reapply the template! Who would ever have dreamt that Microsoft documentation has a mistake in it! ;-D

The takeaway is that to create a many-colored chart template that works as expected under all circumstances, you need to set the extra color values manually in colors1.xml.

5:05 pm

One thought on “OOXML Hacking – Chart Template Colors

Leave a Reply

*Required fields. Your email address will not be published.

Posting XML? To enter XML code, please replace all less than signs "<" with "&lt;" and greater than signs ">" with "&gt;". Otherwise, Wordpress will strip them out and you will see only a blank area where your code would have appeared.