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 help. Calendars are the good example. Who wants to look at a boring calendar all day? Choose a different color and font by just browsing the built-in themes, 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

Try out our new Color Palette Generator!

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).

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.
  • You can use structured references to reference specific columns.

Compatibility

If you are still using Excel 2003 or .xls files, it's time to update. Vertex42 is no longer creating or updating templates for Excel 2003.

Themes are compatible across Excel Desktop, Mobile Excel, and Online Excel, but Google Sheets uses a different setup for color themes. If you import a .xlsx file into Google Sheets, it will still preserve your colors, but don't expect the theme feature in Google Sheets to behave the same way that Excel does.

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]