If you find yourself repeatedly spending a few minutes customising your chart colours, formatting the title, legend, axis labels etc. every time you insert a new chart, then you might consider setting up your own chart templates.
Think of them like man’s best friend, the dog; always at your side.
Chart templates are on hand via the Charts menu at the click of a button….well a few buttons, but it’ll still be quicker than faffing about with all those formatting menus.
Save a Custom Chart Template
It’s dead easy to create your own chart template:
- For Excel 2007 and 2010 select the chart you want to save as a template.
- On the Chart Tools: Design tab of the ribbon click on the ‘Save As Template’ button:
- This will open the ‘Save Chart Template’ save as dialog box. You’ll notice the file type is .crtx. Give your template a name then click ‘Save’:
- Next time you’re ready to insert a chart you’ll find your templates in the ‘Insert Chart’ dialog box. To access this go to the Chart tab of the ribbon and click on the small arrow in the bottom of the ‘Charts’ group:
- Select the ‘Templates’ from the list on the left and your custom chart templates list will appear (in 2007 & 2010 the icons aren’t very helpful as you can see in the image below):
Note: You won’t find this menu in Excel 2013, instead right-click the chart and select ‘Save as Template’.
Thankfully in 2013 (image below) they’re much better as you can actually see the file name of your templates and a preview of the chart. Note however that you need to go to the ‘All Charts’ tab to find your Templates:
This works for fairly basic charts but if you’ve done some unconventional modifications to the standard charts (like those I teach in my Excel Dashboard course) then you might find that Excel doesn’t know how to use your Custom Template, or worse it crashes (as it did for me in Excel 2013). It seemed to be fine with regular chart types though.
Managing Chart Templates
You can delete and rename templates via the ‘Manage Templates’ button as seen in the bottom left of the ‘Insert Chart’ dialog boxes above.
When you click on the ‘Manage Templates’ button Excel opens the folder where your templates are stored. You can edit the files just like any other:
- Delete: Select the file and press the DELETE key
- Rename: Select the file and press F2 to rename the template
You can even set one of your templates as the ‘Default Chart’:
Excel 2007 & 2010: select your chart from the list of template charts > click the ‘Set as Default Chart’ button in the ‘Insert Chart’ dialog box.
Excel 2013: right click the template in the ‘All Charts’ tab of the ‘Insert Chart’ dialog box and select ‘Set as Default Chart’:
Now with all that spare time you have on your hands you can take an extended lunch break 🙂