Excel Themes are a bundle of default colors, font styles and Shape effects. There are lots of built in Excel Themes available from the Page Layout menu:
Hovering over a theme will give you a preview of the effect on your workbook.
You’ll notice that different fonts will have an impact on your workbook layout.
Custom Excel Themes
When you create reports and dashboards you can give them a professional finish by using your company branding, including colours and font styles.
One way we can automate this is to customize our default workbook to always use your company font and colors, but you may not want every workbook to use these special fonts and colors.
This is when a custom Excel Theme is useful, because with one click we can transform any file to adopt our corporate branding.
Themes consist of colors, fonts and Shape effects and we can access them via the Page Layout tab:
Color Themes
In the image below you can see that clicking on Colors (2) reveals a list of the default color styles for the workbook under the Office group and you can choose from one of these.
You’ll also see any custom color themes at the top (3), I have one called ‘Excel’. And at the bottom of the list you can choose ‘Customize Colors’ (4) to create your own:
Clicking on Customize Colors brings up a dialog box where you can set your theme colors from the drop down lists and give it a name:
Font Themes
Similarly, we can set a custom Font theme (see image below) from the Page Layout menu (1) > Fonts (2).
At the top of the list I have a custom theme called ‘Excel’ (3), or I can choose from the default Office themes, or I can create my own custom font theme (4).
Clicking on Customize Fonts (4) opens the dialog box (see image below) where you can specify a font style for your workbook headings and body font. Don’t forget to give your font theme a name.
Note: you can’t set the font size. It will simply default to 11pt.
Effects Themes
Effects Themes include reflections, shadows, lines, fills and other effects you can apply to Shapes. You can’t customize an Effects Theme, but you can choose from one of the pre-set themes available via the Page Layout tab > Effects:
Custom Theme
Once you’ve setup your colors and fonts, and selected your effect theme you can bundle them together in a Theme for the workbook by clicking on Themes > Save Current Theme…:
This opens the Save dialog box and all you need to do is give the theme file a name. Excel will save it in your templates folder for Excel as a .thmx file:
Each time you open Excel you will have access to this theme and can apply it to your workbook by selecting the theme via the Page Layout > Themes menu:
With one click all of the colors, fonts and effects will be changed to reflect your chosen theme. This can save hours of fiddly formatting time and frustration.
Theme Portability
If you send an Excel file that contains a custom theme to a colleague, the theme itself will not be available under the Page Layout > Themes menu for them in their version of Excel, but the file will retain all the theme colors and fonts (assuming they have the fonts installed on their PC):
And the Styles menu will also retain them in the ‘Themed Cell Styles’ menu:
Caution: Styles can breed like mice when copied from workbook to workbook and can quickly multiply to plague levels. Click here for the Excel Styles tutorial where I include some VBA code to easily exterminate them.
More Tips for Debugging VBA
F Berfle
I know this post is a few years old, but it was the only relevant site that came up in a search on this issue. This description is very helpful, but your information on Theme Portability is incorrect. If by “colleague” you mean someone in your organization who also has access to the custom theme, then it is true that they will see the document with the colors and font selections you intended. However, recipients outside your organization will absolutely not see the same thing you formatted. Colors will change to something completely off, and even if the recipient has the fonts installed that are designated by your theme, Excel will substitute the default heading or body font of the default theme on the recipient’s system. I just verified this with Microsoft Support, to corroborate our own continuing experience. This effectively makes themes useless for documents that will be shared with outsiders, if your intent is to convey a customized brand image or even just a coordinated color palette.
Mynda Treacy
Thanks for sharing your experience for users outside your organisation.
Ron MVP
Good advice about themes.
There is one point about creating custom themes, ie “corporate” themes, I have only seen it mentioned in one place, in a free webinar about themes in Office 2010, from RedCapeCo.
In theory you can create a custom theme in any Office application to be used in any other application. The point is that if you are creating a corporate theme to be used in all of the Office applications you should create the new theme in PowerPoint. This is because PPT has a few objects/effects not used in the other applications. Apparently if they are not initialized in PPT at theme creation time they cannot be set/fixed later.
Mynda Treacy
Great tip, Ron. Do you know if that issue is still present in Excel 2013 and 2016?
Geralynn
This is off topic completely but how do you create your visual aids for your newsletter? I like the way you use the numbering scheme (orange tear drop) in the Font Themes section. Do you use Word or an application like Adobe Photoshop?
On topic, thank you for the tips. I will also be using Jon’s tips. 🙂
Thanks.
Mynda Treacy
Hi Geralynn,
Glad you liked the tips. 🙂
I use SnagIt for my static images.
Mynda
Jon Wittwer
I love using themes! Here’s a tip for making your own custom color theme based on your logo or company color:
1) Find the RGB value of your primary logo color and use that for the Accent 1 color. If this color is too light, you can switch to the HSL model after entering the RGB value and pick a darker Lum (luminosity) value.
2) Write down the HSL values for the Accent 1 color.
3) For the other Accent colors, set the Sat and Lum values to the same values that you used for Accent 1. Choose whatever Hue you want for these other colors (red, blue, green, yellow, orange, purple, etc.).
Using the same or similar Sat/Lum values allows you to create a decent color scheme without having formal graphic design training.
Mynda Treacy
Nice tips, thanks, Jon.
Isaac
How can you save this to your personal file so a custom theme is chosen by default for any new workbooks?
Mynda Treacy
Hi Isaac,
Here is the link on how to customize your default Excel workbook.
Mynda