Theme Options in Excel 2010Excel 2007 and Excel 2010 introduced the ability to easily choose different themes (colors, fonts, and graphic effects) for your spreadsheets.

All you do is go to Page Layout > Themes, and hover your mouse over the various built-in themes. As you do this, you'll see a live preview ... very slick.

If you don't want to change the entire theme, which includes the Fonts as well as Colors, you can instead choose to just select from the gallery of Colors.

Don't Want to Print in Color?

No worries ... just switch quickly to the Grayscale color scheme.

Why Can't I do This with ALL Templates?

An Excel 2003 file opened in Excel 2007/2010 doesn't use the new color palette or the theme fonts. This means that you can't just upload an old Excel 2003 file and expect to be able to select a new theme or color scheme.

The New Vertex42® Theme-Enabled Templates

Our new theme-enabled templates are designed specifically to take advantage of themes and other Excel 2010 features such as tables and conditional formatting, where appropriate.

Now, instead of sifting through a huge gallery of templates and finding a bunch of invoices that are exactly alike except for the formatting, you can just download Vertex42's theme-enabled template and then choose the look and feel that you like from the large gallery of themes already built into Excel.

Take a look at the 5 invoices below. These are not different .xls or .xlsx workbooks. They are the same Excel 2010 workbook (available for free via the Invoice Template page) with different themes chosen.

Invoices Created by Just Changing the Theme

Not all Excel spreadsheets need to look fancy or stylish, but there are times when a little finesse can bring a bit of pleasure and happiness. Calendars are the perfect example. Who wants to look at a boring calendar all day? Why not spruce it up a bit with a nice theme, like the Calendar Template shown below.

Theme-Enabled Excel Calendar Templates

Even though you CAN create templates with great colors, our studies have shown that most people prefer to print in black and white or a professional looking blue (depending on the type of document, of course). If you don't have a color printer, color documents print in grayscale, so no problem. However, getting a color printer to print a color document in black and white can by annoying because you have to remember to change the print settings. But, if you are using a theme-enabled template, you can switch the color scheme to grayscale in a matter of seconds.

What About Compatibility?

The problem with designing templates for Excel 2007 and Excel 2010 is that there are still plenty of Excel 2003 users that won't able to see all the cool stuff like sparklines, conditional formatting, custom table formats, pretty graphs, and other ... embellishments. Creating templates specifically for Excel 2010 means ignoring the compatibility issues and focusing on making templates that use the latest and greatest features. If you think it is still in your best interest to use .xls files for compatability reasons, you'll still be able to get them from

Poll of Vertex42 Fans, July 20, 2011

In July of 2011, I conducted a poll on the Vertex42 Facebook fan page asking users what version of Excel they use (see the results on the right). Turns out that almost 75% of those who responded said they use Excel 2007 or Excel 2010.

I will continue to design templates to provide the maximum amount of compatibility that I can. Meaning, I'll still create stuff for Excel 2003, OpenOffice, and Google Docs. But, in light of such a vast majority of users using the .xlsx file formats in Excel 2007 and Excel 2010, I will begin offering the XLSX versions as well.

Simple-to-Use Color Schemes

Vertex42 Theme Colors
The new XLSX color palette

Just thinking about the hideous charts and graphs of the past makes me ill. Don't get me wrong though ... I'm not in favor of using color to obscure, exaggerate, or misrepresent facts and figures. With the new focus on monochromatic color schemes, even the most color-blind among us can now make easy-on-the-eye documents by choosing colors from a single column of the palette.

Vertex42 spreadsheets have always used this technique, by customizing the Excel 2003 color palette. In fact, the very first template I published on used a macro for changing the color scheme of a template (see the original Excel Calendar Template).

Most of the new Vertex42 theme-enabled Excel 2010 templates use the colors in the 5th column (Accent 1) of the new palette. Therefore, if you don't like any of the predefined themes or color schemes, you can choose any color you want by going to Page Layout > Colors > Create New Theme Colors and selecting a new color for Accent 1.

You can even save your own theme or color set so that you can use that theme for all of the templates you download from The next section explains how to do that.

Use Theme Colors from Another Workbook

If you like a set of theme colors from another workbook, you can save and name that set of theme colors so that you can select it in other workbooks.

To save theme colors for use in another workbook (Excel 2010):

  1. Open the .XLSX workbook that contains the color scheme you really like.
  2. Go to Page Layout and select Colors in the Themes button group.
  3. Click on Create New Theme Colors
  4. Name the color scheme whatever you want in the Name field, then click Save.

After you do this, you will see your named set of theme colors as an option in the Page Layout > Colors drop down box.

Note: Theme colors used in a .xlsx workbook are saved as xml data within the workbook file.

New Table Designs

Vertex42 Theme ColorsExcel 2007/2010 introduced a new type of data Table ... a range of data neatly organized into rows and columns, usually with a header row and often with a footer row for totals. I'm not going to delve into how to use Tables. I just want to mention that some of the Vertex42 theme-enabled templates use Tables and that means that you can easily change the look and feel by selecting from some of many predefined table formats (see the image on the right). What fun, eh?

There are still some reasons to NOT use these new types of Tables (such as not being able to use formulas for the labels in the header row), so you won't see me using them ALL of the time. But, there ARE some good reasons why these new tables are great. From the point of view of template design and use, here are the big ones:

  • If a column contains formulas, inserting a new row will automatically copy the formula (for the most part).
  • You can insert and copy/paste rows within the table without messing up the column Totals.

More Theme-Enabled Templates

Here is a list of the some of the other theme-enabled XLSX templates we've created so far besides the invoice and calendar templates mentioned above:

Cite This Article

To reference this article from your website or blog, please use something similar to the following citation:

- Wittwer, J.W., "Theme-Enabled Templates for Excel" from, [date]


Follow Us On ...

Master Excel - Spreadsheet Tips Workbook