OOXML Hacking: Editing in macOS

Posted on November 14, 2015 by jkorchok

Note: I’ve included the original article text to describe the background issues about XML editing in macOS, but to retain your sanity, be sure to follow the May 2016 and July 2018 updates at the end and use a text editor that doesn’t require unzipping and rezipping the files

When you’re hand-editing Office files in Windows, it’s pretty straight-forward: unzip file > edit > rezip, you’re done. Editing in macOS requires a couple of extra precautions. This is because the graphical user interface adds Mac attributes to files and plants hidden files in folders. Office will not tolerate either of these:

Editing in macOS - The Open XML file cannot be opened because there are problems with the contents. Details The file is corrupt and cannot be opened.

XML error message in 2008

$Editing in macOS - The Open XML file cannot be opened because there are problems with the contents or the file name might contain invalid characters (for example, \/). Details The file is corrupt and cannot be opened.$

XML error message in 2011

XML error message in 2016

If you use macOS’s Archive Utility to unzip or zip the files, Word will refuse to open the resulting file. On top of that, if you look in any of the folders using the Finder, a hidden .DS_Store file will be created in the folder. When re-zipped, Word will not accept the extra file and again report an XML error. The solution to these issues is to use the command line, like the Unix warrior you want to be! Remember to run each Terminal command by pressing the Return key after typing the command.

A valuable utility for this is OpenTerminalHere. Open any Finder window, click on OpenTerminalHere and a terminal window opens pointed to the Finder window. So download and install it, then follow these steps to open, edit and re-zip Office files:

Move a copy of the Office document (let’s call it TestDoc.docx) to a separate folder and open that folder in the Finder.
Click on OpenTerminalHere to open a copy of Terminal aimed at the folder.
In the Terminal, type
```
unzip TestDoc.docx
```
then press Return. The file is unzipped into several folders plus a file called [Content_Types].xml.
Do not look in any of the folders using the Finder, or you’ll have to start over. To examine a folder’s contents, use the Terminal to change the folder, then list the contents:
```
cd word

ls -l
```
To go back up to the previous folder, type:
```
cd ..
```
To edit the files, open your text editor, then navigate using the File>Open dialog to find the file. Edit the file, then save and close.
When you’re all done, double-check that terminal is pointing at the original folder holding the documents and the expanded folders. If you’re unsure, close terminal, then click on OpenTerminalHere to reopen in the right spot.
In Terminal, re-zip the files with this style of command:
```
zip -r RevisedDoc.dotx [Content_Types].xml _rels docProps word
```
This example is for Word, but the correct syntax after zip -r is to type the name of the final document, followed by the file and folders, each separated by a space. The file is reassembled into an Office file.
Test that you can open it. If you get an XML error notice, re-read the above steps and try again.

Please note: these editing techniques are required when editing in macOS with Word, PowerPoint and Excel documents and templates, plus Office Theme files (the kind exported from PowerPoint that combine all Theme elements.

If, on the other hand, you are editing a Font Theme or a Color Theme, those are simple XML files. They don’t need to be unzipped or re-zipped and Office doesn’t seem to care about macOS attributes attached to them. These plain XML files don’t need to be handled through the terminal, just use the Finder.

Next time, we’ll be looking at managing Word styles in macOS. Finally, a way to get rid of the zombie styles automatically created by Word! Happy hacking!

March 2016 Update

An alternative to working entirely in Terminal is to work on a network or USB disk where creation od .DS_Store files has been turned off. On a network disk, open Terminal in your choice of folder and run the command:

defaults write com.apple.desktopservices DSDontWriteNetworkStores true

To use a USB disk, run this command instead:

defaults write com.apple.desktopservices DSDontWriteUSBStores true

While this will prevent future generation of the .DS_Store files in that folder and any subfolders, it’s very likely you already have such files, since they’re created almost as soon as you view a folder’s contents in the Finder. In addition, some important XML parts are hidden and need to be revealed. So while Terminal is open, run:

defaults write com.apple.finder AppleShowAllFiles YES

followed by:

killall Finder

The second line restarts the finder to force a refresh of the view. Now you can see any .DS_Store files and delete them before re-zipping the files into an Office document. You’ll have still have to do the zipping in Terminal. Also, no .DS_Store files means OpenTerminalHere doesn’t work, so you’ll have to navigate manually via Terminal commands. Now you know why this is a lame alternative.

If you try this technique, you can always restore the clean file view by running:

defaults write com.apple.finder AppleShowAllFiles NO

killall Finder

Once you’ve created this OOXML editing drive, you can use the command-line zip utility to unzip the files. But there’s also a very useful GUI utility that works better than Archive Utility with Office files. Visit the App Store and get The Unarchiver. Then use it to unzip and expand the Office file.

Editing in macOS – May 2016 Update

BBEdit 11 and better has the ability to open and edit Office files directly, avoiding all of the above hassle when editing in macOS. BBEdit has a 30-day free trial with all features included. While older versions of BBEdit used Tidy to format text, that utility has been retired. The BBEdit programmers have written a script to format XML in human-readable form. You can download it from here, please be sure to read the installation instructions first: Click to download XML Tidy Script for BBEdit

Here’s your working procedure:

Open your Office file in BBEdit 11 or later. In the left-hand pane, you’ll see a folder tree of the files contained within, so no unzipping is required
Select the file you want to edit. The file opens in the main BBEdit window, displaying two lines. The first is the XML header, the second is the actual content.
Click at the left end of the second line.
Choose Text>Apply Text Filter>run_tidy.

Make your edits and save. It’s not necessary to linearize the XML. The Office program will do that anyway the first time you save it. However, if you like to leave things exactly the way you found them, click in from of the first line of content (after the header line), choose Markup>Utilities>Format…, change the Mode to Compact and click on the Format button. Save the file and test your editing in macOS.

Editing in macOS – July 2018 Update

Technology marches on! If you use the Chrome browser, there is a free XML editing alternative that avoids unzipping and rezipping files. Open this link in Chrome: OOXML Tools and download the free plugin. After installation, click on the OOXML icon to the right of the browser address bar. Drag your Office files onto the browser window to begin editing. When you’re finished, click on the Save button, then the Download button in the upper left corner and give the new file an appropriate name. Chrome will place the new file in your Downloads folder and leave the original file untouched. OOXML’s EMF/WMF bug has been fixed, so download the most recent version. Thanks to Bram Alkema of the Netherlands for informing us about OOXML Tools.

Please note, for any OOXML Hacking that requires adding new XML parts (Ribbon mods, creating SuperThemes), BBEdit and OOXML Tools will not work. You’ll have to use the March 2016 update solution and create a network or USB disk set up for XML editing.

We’re experts in XML hacking, so you don’t have to be. Contact me at production@brandwares.com with the details of what you need hacked.

OOXML Hacking: Font Themes

Posted on October 16, 2015 by jkorchok

Font themes are one of the simpler theme elements in Open Office XML, but for some baffling reason, Mac Office users can’t create one. It’s odd enough that the only Mac program that can create a color theme is PowerPoint, but even it can’t provide an escape from Calibri and Arial! So I’m going to show you how to do it on your own.

Let’s start with a dead-simple font theme. Here’s the minimal file that Office will read:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<a:fontScheme xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" name="Test">
  <a:majorFont>
    <a:latin typeface="Arial"/>
    <a:ea typeface=""/>
    <a:cs typeface=""/>
  </a:majorFont>
  <a:minorFont>
    <a:latin typeface="Arial"/>
    <a:ea typeface=""/>
    <a:cs typeface=""/>
  </a:minorFont>
</a:fontScheme>

Important Note: If you copy and paste this sample, you must change the non-breaking space characters to ordinary spaces. I need to use non-breaking spaces to format an HTML page, but Office will refuse to display your font theme if you don’t search and replace them with regular spaces.

You can create this in any text editor, including TextEdit in plain text mode (don’t try this with an rtf file). However, by default TextEdit will change the necessary straight quotes to smart quotes, producing a file that Office will not recognize. If you’re using TextEdit, make sure you visit both TextEdit>Preferences and Edit>Substitutions and turn off Smart Quotes in both locations. A better alternative is the free version of BBEdit. When you visit this link, click on the Download link to get the free version. If you do any significant amount of XML editing, the paid version of BBEdit is well worth the $50 price tag.

The most common font theme problem is using smart quotes (Hex 201C + 201D, Decimal 8220 + 8221) other than plain straight quotes (Hex 22, Decimal 34). But you can also ruin a font theme by using non-breaking spaces (Hex A0, Decimal 160) instead of regular spaces (Hex 20, Decimal 32). Even though a font theme is encoded in UTF-8, you should only use plain ASCII characters for the text. XML has a low tolerance for non-standard characters.

Now that you’re set up to edit, copy and paste the font theme file. The <a:latin> tag is the standard font for your theme. <a:majorFont> is for headings and <a:minorFont> for text. Fill in <a:ea> with a font that supports Chinese or Japanese (ea stands for East Asian), if you want to support those languages. The <a:cs> tag stands for complex scripts: Arabic, Thai, Hebrew and many more. For more detail on non-European language support in font themes, please see my article XML Hacking: Font Themes Complete. Or you can just leave those tags blank if you have a predictable user base that won’t require them.

A common mistake is to get too specific with the font name in font themes. The name is only the base font name as displayed in Powerpoint’s font menu. “Open Sans” will work, but “Open Sans Extrabold” will cause Word 2011 to display a blank space where the font theme should be, while Word 2016 will simply ignore the entire file.

Save the file as a text file with a .xml ending and give it the name you want to appear in the user interface. “Brandwares.xml” will appear in the Font Theme menu as Brandwares.

For Office 2016 or 2019, save this file to Users/YourUserName/Library/Group Containers/UBF8T346G9.Office/User Content/Themes/Theme Fonts. For Office 2011, save it to Users/YourUserName/Library/Application Support/Microsoft/Office/User Templates/My Themes/Theme Fonts. In current versions of OS X, the user Library is hidden by default. To open it, hold down the Alt key, while clicking on the Go menu and choosing Library.

Once it’s correctly installed, it will show in PowerPoint’s Slide Master view under the Fonts dropdown. A new Custom group will appear at the top of the list, with your font theme in it. Once you apply it and a color theme to a presentation, you can save as a theme file and distribute that to your users, it will contain the font theme you just created. Happy hacking!

Font Themes – An Alternate Method

March 2017 edit: If you have any problems creating a font theme from scratch, here’s a workaround. Open an existing font theme that come with Office and edit the font names to the ones you want to use. These files are the verbose style discussed in this article: XML Hacking: Font Themes Complete. For most uses, you only need to set the a:latin font in the a:majorfont and a:minorfont sections. Here’s where you can find the Microsoft Font Themes:

Office 2011 for Mac – Open Applications/Microsoft Office 2011/Office/Media/Office Themes/Theme Fonts and copy any of the XML files.

Office 2016 or 2019 for Mac – Open Applications, then right-click on Microsoft PowerPoint and choose Show Package Contents. Open Contents/Resources/Office Themes/Theme Colors and copy any of the XML files in there.

Here are the locations for 32-bit versions of Windows. If you’re using a 64-bit version of Windows, check the same path inside C:\Program Files (x86).

Office 2007 for Windows – Open C:\Program Files\Microsoft Office\Document Themes 12\Theme Fonts.

Office 2010 for Windows – Open C:\Program Files\Microsoft Office\Document Themes 14\Theme Fonts.

Office 2013 for Windows – Open C:\Program Files\Microsoft Office\root\Document Themes 15\Theme Fonts.

Office 2016 or 2019 for Windows – Open C:\Program Files\Microsoft Office\root\Document Themes 16\Theme Fonts.

Too complicated? We can help! Brandwares is a full service template creation service for all Office programs. Contact me at production@brandwares.com

OOXML Hacking: Table Styles Complete

Posted on August 31, 2015 by jkorchok

Custom Table Styles are probably one of the more detailed hacks you’ll have to write. See the constructions details in my previous post. Besides the basic table format, there are 6 optional format layers you need to at least consider. In a minimal table style, you’ll need to include at least the Header Row, First Column and Banded Rows. Most users will expect to see these options. Total Rows, Last Columns and Banded Columns are less requested, you only need to include them if a design or client specifically requires them.

As mentioned in part 1, if you haven’t hacked XML before, please read XML Hacking: An Introduction. If you’re using a Mac, you should also read XML Hacking: Editing in OS X. In addition, an essential companion to this pair of articles is the post on setting Default Table Text, which is set in a different XML component..

Let’s take a look at how our work appears in the PowerPoint interface. First, we’ll insert a plain vanilla table. By default this takes on colors and fonts from the current PowerPoint theme:

Next, we choose the Table Tools>Design tab, open the Table Styles gallery. Up at the top a new Custom section has appeared with our new custom table style:

Select the custom table style and the default table changes to match our design. This screen shot has all formatting options turned off, so effectively we are seeing the Whole Table formatting only.

Table Style Options: Banded Rows and Header

Using the options panel in the upper left corner, we can add some of optional formatting layers we created in XML. First, let’s turn on banded rows. If you remember, we only formatted odd-numbered rows, so the banding only changes rows 1 and 3 in our example:

Next, we’ll leave banded rows on and also add the Header row. This row doesn’t count as part of the table body, so the banding moves down 1 row:

Table Style Options: First and Last Columns

Next, we’ll turn off banded rows, leave the Header as is and add the first column:

Here’s the table with First and Last Columns checked:

Table Style Options: Header and Total

And finally, Header and Total Rows:

As you can see, with some pre-planning, one table style can cover quite a few related table looks. The layer options for different features make the table useful for many different purposes and the options panel makes it fast and easy for users to try different combinations. This feature is a major advance over tables in PowerPoint 2003 and earlier, which were quite crude by comparison.Table styles a similar way in Word, PowerPoint and Excel. While Word and Excel include table style editors in their interface, PowerPoint needs to be hacked to create them. Sadly, the table style specs for the different parograms are dissferent, so it’s not possible to trasfer tables style OOXML among Word, Excel and PowerPoint.

Table Styles: What You Can’t Do

You cannot set vertical cell/row alignment or cell margins in default taxt table text or a table style. It would have been possible given the OOXML spec, but Microsoft just didn’t bother.

Of course, if the process is too complex, we’re here to help. The current price on a custom table style is US$120. Just email me production@brandwares.com

OOXML Hacking: Custom Colors

Posted on June 15, 2015 by jkorchok

In my last post, I looked at adding extra color themes to PowerPoint masters. But any color theme can only hold a maximum of 10 colors (not counting the hyperlink colors, which are not available in the color picker). The solution is to add Custom Colors to the theme.

Fortunately, most versions of Office programs beginning with 2007 have a hidden capacity to display up to 50 additional colors. These form a new row below the theme colors and above the stock Microsoft row. These extra colors are not supported in PowerPoint 2008 or 2011 for OS X. While these versions will open a file with more colors, the colors do not get added to the color picker. Here’s what the color picker in every other variety of Word or PowerPoint looks like after adding additional colors:

Custom Colors in PowerPoint — Custom Colors in Office

OOXML Basics

If you’re new to XML hacking, be sure to read my Introduction to the subject first. If you’re editing on a Mac, there are some other considerations covered in this article. I should also clarify: a Color Theme or a Font Theme are only sub-parts of a Theme as saved by PowerPoint. A PowerPoint Theme file contains the entire presentation, including masters, layouts, and full theme files. It’s this full theme file that we need to edit. Custom colors never appear in a Color Theme file.

As with the extra color themes we looked at last time, custom colors are added to the XML theme files found in ppt>theme. I mentioned in the last article, presentations almost always include a default Office theme as a fallback and this theme is always last in the list. So in a presentation with 1 color theme, there will be a theme1.xml and a theme2.xml inside the theme folder. Ignore theme2.xml and customize only theme1.xml.

Setting Custom Colors

These colors are the last category in a theme file, so it’s easy to find. As before, we expand the PowerPoint file (really a zip archive with a different file ending), open ppt/theme, open theme1.xml in a text editor and reflow the text to be humanly readable. Then scroll all the way down to the end. An unmodified file will only display the <a:extraClrSchemeLst/> self-closing tag. To add more colors, we add a new branch to the tree. Below <a:extraClrSchemeLst/>, we type a new entry: <a:custClrLst>. This is followed by our additional color definitions and the closing tag </a:custClrLst>. Here’s what a list of custom colors as seen in the screenshot above would look like in XML:

A nice feature here is that, unlike color themes, you can actually supply a name for the color. This is followed a hexadecimal RGB color value, as used in web design. This list has only 8 colors, but there is room for up to 50 in the color picker. If you read the last article about color themes, you’ll soon realize that each theme file can have a different set of additional colors that goes with it as well. The combination of themes and custom colors gives you 18 colors per theme to play with, almost enough for the most color-crazed designer!

Custom colors can be added to the theme1.xml file in Word and Excel using exactly the same syntax. The colors appear in the color picker as expected in Word but are partially ignored in Excel. In Excel, these colors display for almost all objects on the Insert tab: Text Boxes, Shapes, SmartArt, Charts, WordArt, etc. But if you’re seeking to format worksheet text, cell fills or borders, they do not show. (Thanks to reader Kaz for pointing this out.)

Custom Colors in Columns

By default, the colors appear in 5 rows of 10 colors each. Some designers prefer to display the colors in columns instead of rows. To work around this, add your colors to form the top row of your columns. Then complete the row of 10 with dummy colors that have a value of FFFFFF and blank as a name:

<a:custClrLst>
  <a:custClr name="Grey">
    <a:srgbClr val="797472"/>
  </a:custClr>
  <a:custClr name="Very Light Grey">
    <a:srgbClr val="F9F8F5"/>
  </a:custClr>
  <a:custClr name="Red">
    <a:srgbClr val="E33126"/>
  </a:custClr>
  <a:custClr name="Light Grey">
    <a:srgbClr val="D0CECE"/>
  </a:custClr>
  <a:custClr name="Dark Red">
    <a:srgbClr val="A01B14"/>
  </a:custClr>
  <a:custClr name="Light Red">
    <a:srgbClr val="F29A96"/>
  </a:custClr>
  <a:custClr name="blank">
    <a:srgbClr val="FFFFFF"/>
  </a:custClr>
  <a:custClr name="blank">
    <a:srgbClr val="FFFFFF"/>
  </a:custClr>
  <a:custClr name="blank">
    <a:srgbClr val="FFFFFF"/>
  </a:custClr>
  <a:custClr name="blank">
    <a:srgbClr val="FFFFFF"/>
  </a:custClr>
</a:custClrLst>

Here’s the effect in the program interface:

Another limitation is that if you save a presentation as a Theme (*.thmx) file, custom colors in the presentation don’t get added to the Theme file. You’ll have edit a saved Theme file to add the custom colors to it, then they can be applied to another file. Or you can save the original presentation as a Presentation (*.pptx, *.pptm) or Template (*.potx, *.potm) to preserve the Custom Colors.

By extension, this also means that to add custom colors to Word by applying a PowerPoint Theme file, you must edit the Theme file to add the custom colors. Or course, you can hack the Word document or template to get them. The file name in Word will be the same: theme1.xml, but it is to be found in the word/themes folder instead of ppt/themes. As with PowerPoint, custom colors are added to the very end of the theme.xml part.

If you have added custom colors, then duplicate the slide master, the custom colors will also be duplicated (each slide master has a separate theme file as well). But if your first slide master has custom colors and you simply create a second slide master, the second master will not include the custom colors in it’s theme.

Unlike theme colors, custom colors are not automatically applied to charts. Automatic chart coloring in PowerPoint, Excel and Word is limited to 6 settable colors (see this post for chart coloring details). After that, the host program starts generating automatic variants based on the theme. However, for the detail-oriented among us, manual recoloring of data series with custom colors is very convenient and miles above the previous technique, inputting RGB values from text.

I’ve written a lot about PowerPoint, but the additional colors can be used anywhere in Word as well. In Excel, you get partial access. Most objects on the Insert tab can have custom colors applied, like AutoShapes and SmartArt. Custom colors cannot be used for worksheet formatting. Cell and font coloring won’t use use them, they just don’t appear in the color picker for those objects.

We’re here to do this for you, if you prefer! Please contact me at production@brandwares.com. Next time, I’ll tackle formatting custom table formats in XML. This is a complex subject, so it will take at least 2 posts. Stay tuned!

OOXML Hacking: An Introduction

Posted on February 20, 2015 by jkorchok

With the introduction of Office 2007, Microsoft changed the basic file format that underlies Word, PowerPoint and Excel. Instead of the proprietary and mostly undocumented format that ruled from Office 97 to Office 2003, Microsoft made a smart decision and switched to XML. This is tagged text, similar in structure and concept to HTML code with which you may already be familiar.

XML opens up a world of possibilities for automated document construction, but that’s a topic for another day. The everyday relevance for you and I is that if a Word or PowerPoint file isn’t doing what you need it to do and there are no tools in the program for the job, we can now dive in a edit the file ourselves. If you’re a point-and-click user, this is probably not thrilling. But if you’re a hacker at heart, a midnight coder or just a curious tinkerer, you can do some cool stuff.

The main tool you’re going to need is a text editor. While you can get away for a while with Notepad or TextEdit, those simple text editors don’t quite have the tools that get the job done efficiently. On Mac, I use BBEdit and on Windows I reach for Notepad++. BBEdit is reasonably-priced shareware and Notepad++ is freeware. They have a similar style of operation, so if you’re a cross-platform hacker it’s easy to switch between them. Notepad++ uses a plugin system, so you can add tools. For this job, you’re definitely going to want the free XML plugin. To install that, choose Plugins>Plugins Admin, scroll down the list to XML Tools, select it and click on Next. While you’re installing, aAnother very useful NotePad++ plugin is Compare.

The macOS requires somewhat more care with handling expanded Office files, or they won’t open after being rezipped. Please see this article for the best procedure on a Mac. The rest of this article mentions Windows methods, but the XML file structure is the same on both platforms.

Word, Excel and PowerPoint files in the new format are actually simple Zip files with a different file ending. Getting into them couldn’t be easier: if you’re using Windows, add .zip to the end of the file (a copy of the file, if it’s anything important). You’ll get a warning from your OS, but you know what you’re doing! Now unzip it. Out pop several folders of XML, plus a top-level file or two.

Inside a Word File — Inside a simple Word file. The document text is stored in document.xml

Select one of the files and open it in your text editor. All the files have been linearized to minimize file size. This is where your XML tools come into play. In Notepad++, choose Plugins>XML Tools>Pretty Print (XML Only – with line breaks). Now you have a nicely indented, easy-to-read page to edit. When you’re done, it’s not necessary to re-linearize. Word, PowerPoint or Excel will do that for you later.

For people using Window’s built-in zip utility, there is an easy mistake to watch out for. By default, unzipping a file in Windows creates a new folder named for the file being expanded. If, when you’re re-assembling the file, you include this top-level folder, PowerPoint will raise an error about unreadable content in the presentation. To avoid this, first open the folder that Windows created. Select the _rels, docProps and ppt folders, plus the [Content_Types].xml file, then create a zip file from them.

As an alternative to unzipping/rezipping files in Windows, download the free 7-Zip utility. After installing, set your text editor as the 7-Zip editor. Then right-click on the Office file you want to edit and choose 7-Zip>Open Archive. A window opens showing the OOXML folders and files. Find the file you want to edit, right-click and choose Edit. Edit only 1 file at a time in 7-Zip, closing your text editor and updating the file each time. Otherwise, some or your changes may be lost.

XML hacking is useful for Excel or Word when you want to add additional color themes, lock graphics, or when you need to rescue a corrupt document. But it really shines with PowerPoint, allowing you to create custom table formats, extra custom colors that don’t fit into a theme, setting the default text size for tables and text boxes, and more. This technique separates the PowerPoint pros from the wannabes.

In my next post, I’ll get into the specifics of some cool XML hacking Office tricks. In the mean time, check out text editors and XML tools so you’re ready to hack!

Inside a PowerPoint File — A plain vanilla PowerPoint file: more complex than Word.

If code editing isn’t your thing, we can do it for you! Email me at production@brandwares.com.

Office Charts: 6 Colors Maximum! – Best Practices

Posted on July 5, 2013 by jkorchok

Love it or hate it, but Microsoft has made decisions on how you should work with office software. Working within their limitations, your users can have productive careers and get things done. Ignore the limits and you are sentencing your users to hours of formatting hell. A prime example is Office charts

Office charts should have 6 colors maximum. The is a rule made by Microsoft. The exception to this rule is charts in Excel 2003 and earlier, which can have a maximum of 56 colors (see my previous posts for the details on Excel 2003). The following applies to Microsoft Word, but it’s more often a problem in PowerPoint.

If you design charts with more than 6 colors, you are forcing your users to either:

copy, paste and reformat a sample chart into the one they want, or
manually enter the colors for the series above 6 from a reference table of RGB values.

Neither of these could be considered a PowerPoint “Standard Operating Procedures”. Both workarounds are slow and error-prone.

Office Charts in Office 2003 for Windows or 2004 for Mac

PowerPoint 2003 Some color palette slots are used for as many as 3 functions. This makes it tricky to design a color sequence that works for all purposes.

PowerPoint only has code in place to automatically assign 6 palette colors to charts. In PowerPoint 2007 and higher, these are 6 distinct colors that are only used for charts and object fills. In PowerPoint 2003 and earlier, the chart colors are taken from the 8-color palette that is used for other elements as well. This has consequences you have not considered. Here’s how it works:

PowerPoint 2003 and earlier have color palettes with 8 colors maximum. In order, the colors are used for Backgrounds, Text and lines, Shadows, Title text, Fills, Accent, Accent and Hyperlink and finally Accent and followed hyperlink. Applying the recommended color position to a presentation element will mean that that element can be automatically updated if the palette is changed. This is a handy way to create color-coded sections in a presentation.

However, the same colors are used for Office charts. The colors are assigned in this order: Chart Color #1 is always taken from the Fill position. CC#2 is Accent, CC#3 is Accent and hyperlink, CC#4 is Accent and followed hyperlink, CC#5 is Shadows and CC#6 is Title text. Therefore, if you have designed a series of chart colors that is different from the code colors used the the presentation, you must give your users one of two headaches:

User headache number 1: You assign the color palette positions to create an automatically-filled chart, then manually assign all other presentation elements with RGB colors. This means new charts look swell, but color-coded sections cannot be easily updated. In fact, the colors of the entire presentation are must be updated manually element by element. Slow!

User headache number 2: You assign the color palette positions to the correct elements i.e., Title text is filled from the Title text palette position. New charts come in with goofy colors and each series must be manually reassigned from RGB values.

For PowerPoint 2003 and earlier, Microsoft has decided you only need 8 colors for everything. Designing with more colors does not make it a better design. It just makes it harder to use.

Office Charts in Office 2007/2010/2013/2016 for Windows or 2008/2011/2016 for Mac

For PowerPoint 2007 and later, Microsoft has decided you only need 12 colors for the presentation. Fortunately, now there is separation between color functions, with 2 colors for backgrounds, 2 for text, 6 for charts and fills and 2 specifically for hyperlinks. But you still only get 6 automatic chart colors. And in Office charts as in so many Office features, if it isn’t automatic, it’s a time-waster. So the advice still holds that speccing more colors is really imposing lower productivity on your users.

Charts apply this sequence in a predictable way. In a standard column chart, the leftmost column is color #1, with each extra column getting the next color in the sequence. Stacked columns display color #1 as the bottom layer and pie charts apply color #1 to the first pie segment, and by default this pie segment has its left border at 0 degrees, pointing straight up. If you design the presentation with this in mind, implementation is easy for the user.

In Office 2007 and later, there is one potential workaround that gives users access to 10 additional colors. It’s the Custom Color XML hack. This hack adds colors to the color picker in Windows and in Office 2016 for Mac. These colors cannot fill charts automatically and they will not show in the Office 2008 or 2011 for Mac color picker. However, it’s still a less cumbersome workaround than the 2 kludges I mentioned at the start of this article.

Are these limitation fair? No. Are Microsoft’s choices well considered? Not really. Are you going to change the way PowerPoint works by ignoring its limitations? Sadly, the answer is also no.

My recommendation is to incorporate a little Zen into your attitude, accept that PowerPoint (particularly the 2003 and earlier versions) is a deeply flawed and limited program. Then go and create some great Office chart designs with 6 colors or less that are easy to use.

Report Charts: Simple Excel Templates – Best Practices

Posted on June 4, 2013 by jkorchok

Users create charts in Excel and import them to Word and PowerPoint. Reports frequently require charts. Put these together and you’ll see that when a client asks for a report template, you should also be giving them a compatible Excel template for the report charts they will need.

These charting templates do not have to be complex. Defining a default font and color palette is usually sufficient. The actual steps differ between Excel 2003 and Excel 2007/2010/2013/2016, so make sure you ask your client what version they are using.

The first step with any program is to design the color palette. Excel uses the colors in the order they are entered. In a bar chart, the colors are used left to right as each data series is added. Pie and doughnut charts always start with left edge of the first segment at the 12 o’clock position and proceed clockwise from there. Go with the flow and enter palette colors in the order you want them to appear by default. There is only 1 palette sequence active, so all report charts in your design should use the same color order, or you’ll drive your users crazy.

Report Charts in Excel 2003

Excel Palette Color Order for report charts

The numbers show the order used in Excel 2003 charts.

If your client is using Excel 2003 or earlier, open a spreadsheet in that version, use the Tools>Options command and choose the Color tab. For each color in the “Chart fills” row, start at the left, click on a color, then click on the Modify button. Choose the Custom tab and enter the RGB values. Click on OK, then choose the next Color fill patch to the right and repeat.

Occasionally Office 2003 clients will insist on more than 6 chart colors. In that case, keep filling palette spaces with custom colors using the order shown in the illustration. This will give you up to 56 custom data series that will automatically be filled correctly. Be forewarned, when your client upgrades to a later version of Office, they will, in effect, be back to 6 chart colors. Later versions of Excel do not allow this extended palette.

As a final step, choose Format>Style, ensure the Normal style is selected and click on the Modify button. On the Font tab, set the font to the desired typeface, size and weight to match the Word templates into which these will be inserted. OK out and save the file as a template with an “.xlt” file ending. You’re done!

Report Charts in Excel 2007/2010/2013/2016

Excel 2007 Theme Palette for Report Charts

Circled: the 6 colors that will automatically color Excel 2007-2016 charts.

For clients on Excel 2007 or later, the palette system works differently, and not for the better. Users have lost the capability to set 56 chart colors, now you can only preset 6! The Office 2007/10/13/16 Theme Color dialog displays slots for 6 Accent Colors. Set these with RGB values in the sequence they are t appear in the report charts. If report charts use more than 6 colors, Excel’s default behavior is to color the additional series with automatically generated tints of these 6. To get more custom colors, users must enter them manually. In real life, they’re probably not going to do this. First they have to turn off the automatic series fill, then they have to look up the colors they are supposed to use (where is that tutorial again?), then they have to enter the RGB values. It’s just not a realistic workflow for end users on a deadline.

One alternative is to create a sample chart with manually filled extra data series. This looks good for a client sign-off, but in actual production, the user has to find the sample chart, copy and paste it to their presentation and then change the chart type and data to match what they are trying to show. Again, most users are unlikely to go to this trouble, especially if time is tight.

Finally, if you’re up to a little XML hacking, you can add up to 10 custom colors to the Windows color picker by using my tutorial XML Hacking: Custom Colors. While these colors are not used automatically in chart fills, it’s a simpler process to fill a series by clicking on a custom color than looking up an RGB value.

If possible, it may be best to try talking your client out of using more than 6 colors for charts. The lower expectations will make everyone happier and the end-users would thank you, if they knew who you were.

To wrap up this template, click on the Page Layout tab of the Ribbon, look for the Themes group and click on the down-pointing arrowhead beside Fonts. Click on Create new theme fonts… at the bottom of the list. Set the fonts for both Heading and Body and click on OK. Save the file as an Excel Template with an “.xltx” ending and distribute with the report templates.

An alternative workflow is to save the themes from the Word or PowerPoint file that accompanies the Excel template, then apply those themes to the Excel worksheet. Or course, you still have to apply the same thought process in arranging the colors to create charts that automatically have the right color sequence. It helps that charts created directly in Word or PowerPoint follow the same logic and color order.

Report Charts in Excel 2008, 2011 or 2016 for Mac

In Mac versions of Office, you’ll have to create the Color Theme in PowerPoint, then import it to Excel. Unfortunately, while you can use the Custom Color XML hack to add custom colors to a color theme, you can’t preview them in Office 2008 or 2011.

For the font theme, you can use my tutorial XML Hacking: Font Themes and apply the resulting theme to your Excel template.

Calculating RGB Tints – Best Practices

Posted on April 18, 2013 by jkorchok

Theoretically, there is no such thing as RGB tints. Color theory uses “tint” as any lighter hue of a color. Often this will be explained as what happens if you mix a color with white, though anyone who has spent any time mixing colors has noticed that adding white pigment changes colors in other ways than simply making them lighter.

In the commercial art world, tint is nearly synonymous with screen, because the traditional way of creating lighter shades of a base color is to print it with a halftone screen.

Because of this tradition, brand guidelines often augment a base set of colors with a subset of pastels referred to as tints or screens. This all makes perfect sense in a print-oriented world. However, when you move into digital display color, there are no screens any more. So how do we interpret brand guidelines for RGB-only software like Word, PowerPoint, Excel and the web?

Brand guidelines now normally include RGB numbers for the base colors of a brand, but tints rarely have an RGB equivalent. The designer just specs 20% of PMS 286 and lets you figure it out.

A Calculator for RGB Tints

You can do it with Illustrator, but it’s a little roundabout. You create an RGB color, save it as a swatch, being sure to over-ride Illustrator’s efforts to keep turning it into CMYK. Then you can spec a percentage of the swatch and finally find out the RGB value of that percentage.

Or you can just use the handy dandy RGB Tint calculator on this very web site. Simply enter an RGB or hexadecimal color value, then the tint percentage, and you get an instant readout of the new color values along with a preview of the color appearance. In addition, it makes a useful RGB-hexadecimal convertor, though there are plenty of other ways to do that operation.

This is a tool we developed for our own template creation work, but you may as well get some use of it too. Enjoy!

Logo Production Secrets – Best Practices

Posted on April 10, 2013 by jkorchok

In my last post, I covered some basics on logo production for use in Word, PowerPoint and Excel. This post will continue that topic with some trade secrets, tips and tricks to make logo production easier.

Let’s start at the beginning with the file that’s to be turned into a graphic. Is there a black element in the logo? If there is, you need to do some special handling. In most design programs, black is not necessarily black. Most artists, coming from a print production background, assume that applying C0 M0 Y0 K100 will give them black. If this was a logo you were actually printing, it would. But you’re not.

You’re going to translate this logo into RGB, which is the only color model used by Word, PowerPoint and Excel. (If you use Office on the Mac, the color picker makes it appear you are choosing other color models, but behind the scenes your choices are all translated to RGB.)

If you watch the color values in Illustrator, when you change C0 M0 Y0 K100 to RGB, you’ll see that you do not get black (R0 G0 B0). You get dark grey, something like R35 G31 B32. Shake off that print mentality! The rule of thumb is: on all designs destined for Office, change all color values to RGB, preferably using the Pantone Color Bridge values.

Logo Production Trade Secret

The trade secret for this piece is a shortcut that will save you lots of time at the cost of some system setup. For most logos, we do not use the Illustrator file that is linked to the final art. Instead, we use a PDF of the final art, since the logo has been placed at its final size here. To get our logo file, we resize the PDF to get the total number of pixels we want, make a screen grab of the logo, then paste it into Photoshop.

Here’s the theory behind what we’re doing. The screen grab is at screen resolution, but our final file must be print resolution at 600 d.p.i. (For PowerPoint, we use 384 d.p.i., which is exactly 4 times the default Windows screen resolution. This displays beautifully and still prints well.) So we’re enlarging the PDF to get the number of pixels we need on the display. Then when the screen grab is pasted into Photoshop, we tell it that the resolution is 600 d.p.i. If our math is right, the final logo will be exactly the same physical size as the final design, but at a high resolution.

Here’s the system setup part. The calculations are a LOT easier if both your screen and Acrobat are set to 100d.p.i. If you’re using Windows, setting your screen to 100 d.p.i. means opening your Display control panel, clicking on the Setting tab and the Advanced button. On the General tab, change DPI setting to Custom setting and try 104%. On most systems, that will get you 100 d.p.i.

Non-Retina Macs should already be running at 100 d.p.i. Now open Acrobat and choose Edit>Preferences>Page Display. Under Resolution, choose Use system setting, which should say 100 pixels/inch. OK out.

You should be good to go. Open the design PDF with logo, set the zoom to 600% and choose the Edit>Take a Snapshot command. Draw a marquee around the logo. Here’s a cool tip: even if the logo does not fit on your monitor, drag the marquee over to the side of the window, forcing the graphic to scroll. Acrobat captures all parts of the selection even after it has scrolled off to the side! When you let go of the mouse, the screen will blink, indicating that the image is on the clipboard.

Now switch to PhotoShop. Choose File>New and the suggested size should match the area you just grabbed. Change the resolution to 600 d.p.i. Paste the logo.

Now crop it. Our practice is to do the initial crop leaving one pixel of background clear on the top and left sides of the image. This compensates for Word 2003’s propensity to display the logo on-screen looking like the top or left is “clipped”, even though it prints fine. Crop the right and bottom edge to contain the faintest shading of the logo and no more.

Left: raw screen grab
Right: initial crop – 1 pixel top and left, no clearance right and bottom.

Minimal File Size with Precise Sizing in Office

Word, PowerPoint and Excel can only measure to 1/100″. So if your logo is big or small by a few thousandths, Word will resize it vertically or horizontally to make it fit. Your logo will be slightly distorted. So now we’re going to add back some of the background. In PhotoShop, choose Image>Canvas Size and change the units of measurement to inches. Round the numbers up to the next highest 1/100 of an inch, so 1.999 becomes 2.0, 12.742 becomes 12.75, etc. Set the canvas addition to flow to the right and bottom, as in this screen shot:

Dimensions rounded up to next 1/100″, canvas set to increase right and bottom size.

Click on OK. In the Layers palette, choose Flatten Image. Now follow the steps from last week’s post: choose Image>Mode>Indexed Color, set Palette to Local (Adaptive), reduce the colors to 8 for each color in the original logo (so a 2-color logo will use 16 colors), set Forced and Dither to None, click on OK. Save as PNG for most uses.

There you have it, your first professional Office-ready logo file! It will look good and print sharp, it will be size-accurate and the file size is tiny. Congratulations!

March 2017 edit: This article is still relevant if you must use bitmap-format logos. Vector-based logos offer significant advantages, like not getting blurry when image compression is used on the file. Here’s the state of the art: Best Quality Logos for Office

JPEG Logos? Fail! – Best Practices

Posted on April 4, 2013 by jkorchok

JPEG logos are the automatic choice for most artists when a client needs a wordmark in Office. It’s the worst possible format.

JPEG seems to be the all-purpose file format where the designer realizes they need to place an image with a smaller file size than a full-scale EPS or TIFF. The JPEG format is fine for photos and photo-like graphics, such as graduated tone areas. But it has 2 things going against it for flat color line art: lossy compression and the RGB color space.

The Disadvantages of JPEG Logos

Lossy compression renders flat color as a pixelated and sharpened hodge-podge where every pixel can be a different variation on the original color. You’ve all seen this on blowups of low-quality JPEGs where weird textures are introduced to areas that were formerly smoothly shaded. Lossy compression does reduce the size of the file, but at a visual cost. When you’re dealing with flat color, this visual cost is all too noticeable.

The other down-side to JPEG is the RGB color space. While admirably suited to photos, it’s simply not needed for line art logos. The RGB color space means thousands of color definitions are stored in the file, even for a logo that started as 1 color when it was a happy piece of vector art. This bloats the file size

The net result is a fuzzy, textured logo that is just too big. Then the client wonders why their letterhead template is over a megabyte before they type one word. Instead, let’s go back to first principles and figure out how to do this right.

The Alternative to JPEG Logos

The average logo starts life as an Illustrator file colored with one or two colors. How can we keep it as close to that original as possible?

Let’s start by throwing out the RGB color space. We don’t need millions of colors, we only need a few. Let’s use Indexed Color instead. With indexed color, only a few color definitions need to be stored in the file, so the file size goes way down. It goes down so much, that we can now increase the resolution of the image to get better sharpness.

Let’s go through it step by step. If you’re using Adobe products, it takes 2 steps to get to indexed color. First you have to export the logo from Illustrator as RGB, but to a file format that is not lossy, like TIFF. Then you open the RGB file in PhotoShop and reduce the image to indexed color.

Open a test logo in Illustrator. Start with a one-color version to make life simple. Now use the File>Export command with the following settings:

Save as type: TIFF
(Click on Save)

Color Model: RGB

Resolution: Other 600dpi

Anti-aliasing: Art Optimized (Supersampling) – assuming logo text has been converted to curves.

Check LZW Compression and click on OK

Now open the TIFF in Photoshop. Choose Image>Mode>Indexed Color and the following settings:

Palette: Local (Adaptive)

Colors: 8

Forced: None

Dither: None and click on OK

The format to save in depends on which version of Office your client is using (you did ask, didn’t you?). GIF format stores more compactly in Office 2003 and earlier. PNG has a smaller file size in Office 2007 and above. The difference is not huge and PNG has the advantage of remembering its resolution, so you could safely just stick with it if you’re not sure. GIFs always import into Office under the assumption that they are 72 d.p.i., which means your logo have to be reduced to the correct size after placing in Word or PowerPoint.

The Proof That JPEG Logos Suck

Are you skeptical that it’s worth the trouble? Here’s an enlargment of a typical artist’s result at left versus a Brandwares logo on the right:

Enlargement of a typical artist-supplied 300dpi RGB JPEG.

OK, it’s obvious the indexed color image looks way better. But the other benefit is that it’s also much a much smaller file size. Typically, the crappy-looking JPEG will be 8 (yes, eight) times larger! I know, file size may not matter to you, but it does to your client. They have to pay for storage for thousands of documents based on your template. The rule of thumb is: if your letterhead is bigger than 100kb, you’re doing something wrong. Ours range between 40kb and 90kb.

High-res Indexed Color, the alternative to JPEG Logos

Enlargement of a Brandwares 600dpi indexed color PNG.

It’s not a lot more work to give your client a superior product. Forget JPEG as a logo format, just use it for photos. Indexed color PNGs are the way to go for line art.