Chart Templates – Best Practices

Chart Templates allow you to capture and reproduce the appearance of sample charts. This avoids having to include sample charts for users to copy and paste. Here’s how to use them:


Installing

Chart templates must installed to a particular folder so Office can find them.

Windows Installation

  1. Open a File Explorer window.
  2. In the address field, paste in: %appdata%\Microsoft\Templates.
  3. If there isn’t a Charts folder there, create one.
  4. Copy the chart template(s) into it.

macOS Installation

  1. While holding down the Option key, click on the macOS menu bar. Choose Go, then Library. The hidden user Library folder opens.
  2. Open ~/Library/Group Containers/UBF8T346G9.Office/User Content/Chart Templates.
  3. Copy the chart template(s) into it.

Using Chart Templates

Chart templates can be used in Word, Excel and PowerPoint. Here’s how to access them:

Windows Usage

  1. In the Office program, choose Insert>Chart.
  2. In the Insert Chart dialog, click on the Templates icon.
  3. Select the chart template in the right-hand pane, then click on OK. A chart is inserted in your document.

macOS Usage

  1. On the Insert tab, choose Charts>Templates and select the template from the pop-up list. A chart is inserted in your document.

For more information about creating chart templates that work better then the Microsoft default, please see OOXML Hacking – Chart Template Colors

OOXML Hacking: Buy the ebook

The ebook version of OOXML Hacking has been released. The last three years have gone into adding the equivalent of 40 pages of new information. This is in addition to the trove of unique techniques that already appeared in the print version. Here are screen shots of the table of contents, to give you an idea of the topics covered in this publication:

Table of Contents 1
Table of Contents 2
Table of Contents 3

Barnes & Noble distributes only in the U.S., so use Kobo.com if you’re from another country.

As always, all techniques are covered in both Windows and Mac, where possible. The book contains a link to a downloadable text file of all listings in the book, so you don’t have to re-key text from the screen. The book is currently available on Kobo.com and Barnes & Noble. Barnes & Noble distributes only in the U.S., so use Kobo.com if you’re from another country.

Please note, this e-book has digital rights management applied. All code listings are available in a downloadable text file, so you don’t have to re-key anything. If you have any problems, please contact us at this address

Your Office Questions Answered – Best Practices

Brandwares provides top-quality, bulletproof templates to a world-wide clientele. We have the Client List to prove it. But you can get your Office questions answered for free (or cheap)!

I answer questions online about PowerPoint and Word for both Windows and macOS. For general questions about formatting and using both programs, visit the Answers.Microsoft.com Word or Answers.Microsoft.com PowerPoint forums. If you’re a VBA programmer working with Word or PowerPoint, you can get my help at the Stack Overflow Word or Stack Overflow PowerPoint pages.

I also answer questions at Experts Exchange, where I’ve been awarded a Distinguished Expert award for 2021.

Office Questions Answered - Experts Exchange Distinguished Expert 2021

This site isn’t free, but it is jam-packed with expertise. There’s a 7-day free trial, if you want to check it out. Tag your post with Microsoft PowerPoint, Microsoft Word, VBA and/or Fonts Typography to ensure your Office questions are answered.

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.

Office Automation with AppleScript Reference – Cool Code

For Office for Mac users, AppleScript remains a useful tool in scripting Office documents. VBA for Mac has poor links to the operating suystem. In Windows, a programmer can easily make system calls to the OS. In macOS, the analogous system calls are not documented by either Apple or Microsoft. A few intrepid programmers have posted about macOS equivalents, but the information is sporadic and very hard to find. macOS’s built-in AppleScript can solve many of these issues, so I’m posting the best available Office AppleScript Reference.

This is old Microsoft documentation from 2008 that they’ve removed from their site. I don’t guarantee this, or offer support, I’m just posting it to make it available to interested programmers.

Office AppleScript Reference: MacScript vs AppleScriptTask

Ron de Bruin is a European programmer and MVP (Microsoft Valued Professional) who has written lots of good information about programming Office on a Mac. While he writes mostly about Excel, VBA is so similar across Office that most of the information also applies to Word and PowerPoint. Here’s his page on using the AppleScriptTask command in Office 2016 and later to call AppleScripts from a VBA macro: AppleScriptTask in Mac Office 2016 or higher.

Office AppleScript Reference: Conditional Compilation

To create a macro that can run on different versions of Office for Mac, or on both Windows and Mac, you’ll need to use conditional compilation. This uses #If – #Else – #End if statements to run a set of commands only in certain conditions. Here is Microsoft’s page on conditional statements for different Mac versions: Differentiate between Office for Mac versions at compile time. This MS reference page shows all available conditional compiler constants: Compiler constants.

Click here to download: Office AppleScript Reference.

Another useful reference for AppleScript automation of Office is the online article Moving from Microsoft Office VBA to AppleScript by Paul Berkowitz. This is a full-length book with comparisons between VBA and AppleScript, plus many real-world example scripts. It was originally published in 2007, in anticipation of Office 2008. Unfortunately, the downloadable PDF they promise is no longer available.

Text Effects? Don’t! – Best Practices

The Best Practice is to NOT use Text Effects in Office. Ever.

That could have been my shortest article ever, but I guess I should explain the reasons. I’m referring to the graduated fills and lines, the glows, reflections, shadows and 3-D effects you can add to text. In the past these effects have caused some problems with ordinary shapes, but with text, they’re a disaster.

Text Effects: Bad in PDFS

Clearly, it’s not enough for most users that these effects are visually hideous. That just a natural result of the low value we assign to arts education. In many years of working with competent graphic artists, I’ve never been asked to create any template that uses these effects. Designers understand the need for restraint, users don’t. And so we get the appalling appearance of most Word and PowerPoint documents.

But the functional problem with these effects is how they affect PDFs created from Word, PowerPoint and Excel. Microsoft has no clue how to export true PostScript of the fancy effects. So they adopt a simplistic approach: flatten them to graphics. Unfortunately, this means the text vanishes, leaving behind only a pretty picture. Well, not even that. All kinds of PDF functions are impaired: Text to speech is impossible, accessibility goes right out the window, reimporting the PDF to Office is brain-dead.

I tested PDFs created in 3 ways: saving to PDF in Office, printing to Acrobat and printing to the Microsoft Print to PDF print driver that comes with Windows 10. When saving to PDF, all text with applied effects was flattened. When printing to either Acrobat or Microsoft Print to PDF, Gradient Fills, Gradient Lines and 3D Effects were flattened, while Shadows, Reflections and Glows remained as live text.

The moral is clear: when the client asks for Text Effects, just say NO!

Every AutoShape – Cool Code

There are more AutoShapes in Office than appear in the user interface. Over time, Microsoft has quietly added to the collection stored in Office. Many of the more recent shapes are used in SmartArt files, while others have no current use that I can detect. But for anyone who hacks XML or codes VBA will find this week’s download or every autoshape a useful reference.

I’m preparing to start a series on custom SmartArt files. This is a deep topic: SmartArt XML is a programming language with a Frankenstein syntax. But the starting point for any language is to get the names right. SmartArt and VBA both reference the AutoShapes collection in Office, but they use different names for the same objects. Many of the same shapes are seen in the Shapes dropdown of Office programs, but those names are also different. The main source of the VBA names are from this page: MsoAutoShapeType enumeration (Office), while the XML names come from this out-of-date listing: SmartArt AutoShapes.

So, for my own sanity, I created a Word document showing an example of each shape, along with it’s name in XML, in VBA and in the user interface. This has smoothed out my workflow, and it may help you as well.

Listing of every AutoShape

For any non-interface shapes, you can insert them into a document with code analogous to this. For PowerPoint:

Sub MakeShape()
  ActivePresentation.Slides(1).Shapes.AddShape msoShapeTrapezoid, 24, 24, 144, 144
End Sub

Change the bolded word to the shape name in the VBA MsoShapeType Name column.

Some shapes have specialized VBA commands, like callouts:

Sub MakeShape()
  ActivePresentation.Slides(1).Shapes.AddCallout msoCalloutTwo, 24, 24, 144, 144
End Sub

Callouts led me to a discovery about legacy versions. msoCalloutOne gives exactly the same result as msoCalloutTwo in current versions of Office.That seemed odd, so I ran the same macro in PowerPoint 2003 (I still have it installed for its macro recorder). In that version, msoCalloutOne creates a callout with a vertical leading line that can be moved up and down, but not at an angle. Presumably, MS found that useless and deprecated it.

Download the Word document showing every autoshape here.

OOXML Hacking: Open XML Explained

We’re proud to be hosting a free e-book Open XML Explained. Finding readable explanations of how Office Open XML works isn’t easy. Outside of the very dense published ECMA standards, most of the information exists only on the web in help pages and blog posts. Back in 2007, a developer in the Netherlands, Wouter van Vugt, published a well written PDF that goes into details about the XML formats used in Word, Excel and PowerPoint. This was originally posted on the openxmldeveloper.org website run by OOXML genius Eric White.

Eric’s site closed down some years ago and much of the content disappeared. But I’ve been able to track down a copy of Wouter’s book and have his permission to give it a new home here at Brandwares. Download Open XML Explained.

The e-book covers Excel, Word and PowerPoint in depth, covering otherwise obscure topics like table formatting, bullets and charts. It’s an excellent companion to my book, covering the theory behind my practical techniques. OOXML hackers will appreciate the explanations of all the major XML parts in an Office file, while coders will find this a useful resource in learning how to programmatically create and modify documents, workbooks and presentations.

Open XML Explained

I should note that Open XML Explained was written in 2007, so while it covers all the OOXML basics, it doesn’t have any information about newer features like the Backstage or SuperThemes. Reader David Wiggins was kind enough to find the sample files that accomapied the book. The download page has a link to them as well. Enjoy!

OOXML Hacking: Buy the Book

SOLD OUT! The paper copies of the book are all gone, but the ebook version (with an additional 40 pages of new content) is available here.

After years of original research, you can finally buy the book! Filled with unique information not found anywhere else, online or in print, this manual shows you how to build SuperThemes 3 different ways, how to create custom Effects Themes, how to edit the Ribbon in macOS, and much more!

OOXML Hacking: buy the book

The book expands on many of the brief articles on this site, putting them in logical order and expanding the number of examples. Author John Korchok explains how Office Open XML files work, shows you where to find each XML part and how each part can be modified. With these tools, you can provide unique services to your clients or users that you can’t find at the average Office template service bureau. To give you a better idea of what it covers, here is the Table of Contents:

Table of Contents 1
Table of Contents 2
Table of Contents 3

All techniques are covered in both Windows and macOS. The book includes a link to a downloadable text file with all the hyperlinks, XML and VBA listings, so you don’t have to retype anything from the printed page. At this time, only print copies are available, ebook versions will be here in several months. To buy the book, click here.