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.

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 Vertex42® Theme-Enabled Templates

Our 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 gray in a matter of seconds. Keep reading to learn how to create a gray theme that works with Vertex42 templates.

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.

Most of the 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.

For example, if you want to use a Dark Gray for the primary color of the template, you could create a set of theme colors that replaces the Accent 1 color with a dark gray (RGB value: 77, 77, 77).

You can save your theme or color set so that you can use that theme for all of the templates you download from Vertex42.com. 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. Take a look at our Yearly Calendar page for examples of various color schemes.

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 (or Customize 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. If you want to edit your color set later, find it in the list and right-click on it.

Note: Your custom themes are saved as xml files on your computer in the Application Data folder so if you switch computers you won't see your custom themes listed. Theme colors used in a .xlsx workbook are saved as xml data within the workbook file.

Create Your Own Set of Theme Colors

One of the common reasons for creating your own set of theme colors is so that documents you create can easily use your company's color scheme (perhaps matching a logo).

Creating a good color scheme is typically the job of a graphic designer, but if you follow the steps below, you can create a reasonable set of colors that is based on your primary Accent 1 color.

  1. For your Accent 1 color, enter the RGB values for the color you want.
  2. Switch to the HSL color mode and set the LUM value to about 100-110.
  3. For the other Accent colors, use the same SAT and LUM values as the Accent 1 color, but change the Hue.
  4. For the Dark 2 color, use the same Hue as Accent 1 but a lower LUM value (to make it darker). This will make the Heading styles the same hue as your Accent 1 color.

If you are creating your own themes, I'd recommend testing the colors in a variety of different files. Below is an example of a spreadsheet I use when I'm creating new color schemes.

Sample Blue Theme

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.

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 compatibility reasons, you'll still be able to get them from Vertex42.com.

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.

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 Vertex42.com, [date]