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:
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:
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 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:
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.
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 Styles tutorial where I include some VBA code to easily exterminate them.
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.