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:
Warnings
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
Default Charts
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 🙂
Create Hyperlinked List of Files in a Folder Using VBA
Vladimir
Good time of day. Please tell me how to build a diagram with an area in excel so that the border is rounded. Thanks
Catalin Bombea
Hi Vladimir,
Right click the chart, select Format Chart Area, then click Border Styles tab in the Format Chart Area dialog, and check Rounded corners option.
Steve B
Hello Mynda!
I’m new here, just signed up today. But old, 73 now. I’m trying to understand dashboards and have a problem formatting line charts.
for example, we have many clients, each client has revenue, personnel costs, material costs, motor pool costs, etc., etc.
My idea is to have, for each client, every revenue line green, every personnel lagine red, every motor pool line blue, etc. and I would like every line to be, say, 4 pts. wide.
Does one HAVE to format each line separately, or is there a tick to accomplish what I would like?
Maybe you have a video that I haven’t found yet!!ag
Thanks,ag
Steve
Steve
Mynda Treacy
Hi Steve,
Yes, you have to format every line separately, but once you do one chart you can copy the chart for the other clients and the formatting will stick.
Mynda
Saskia
This way of making your own chart templates is personal. Is there a way to distribute it (like normal templates) for a whole company?
Mynda Treacy
Hi Saskia,
Yes, you can share the .crtx files with your colleagues just as you might share any other Excel file. They then need to save it in their templates folder for use in their version of Excel.
Mynda
Bryan
But… messing with the formatting every time is half the fun!
Mynda Treacy
🙂 Bryan, I think you’ve got too much time on your hands!
Jon Acampora
Great tutorial Mynda! I provide templates in some of my tutorials and now I have a great resource to point readers too if they want to learn how to make their own. Thanks for sharing!
Mynda Treacy
Cheers, Jon 🙂
EL
Really nice suggestions there. A lot of users don’t have the idea how to use chart templates in Excel. This explains it very clearly. Thanks for sharing.
Mynda Treacy
Thanks, EL 🙂