Save Time and Work by Creating Default Chart Templates for Excel


I’m willing to bet that the Pareto principle, the 80/20 rule, applies to the charts you build. The majority of your charts use the same chart type with the same corporate colors, font size, layout, etc. I’m also willing to bet you a Snickers bar that after you’ve recreated this same chart three or four times you wish there was a way to do it quickly or automatically. There is! You can create custom chart templates so Excel opens the chart in the type and with the formatting you want.

The normal Excel chart default is the 2D column chart shown in this figure. If you select a cell within the data block and press Alt+F1 this chart will appear.

Set the Default Chart Template

The default chart type is a 2D column with legend.

Let’s assume that instead of a column chart you want your default chart to open as a line chart like the following. This chart is the 2D line chart with the legend deleted, the horizontal lines deleted, and the title in the color red.

You can create your own custom default type with formatting.

Your custom default can be any type with or without chart elements.

To make this line chart the default chart that initially opens you will first need to save this chart as a template. You can then set the template as the default chart type. If you do not select a template as a default chart type, then an unformatted version of the chart you have selected will become the default. So, in general you will want to create chart templates first.

To create the first template,
1. Select the chart.
2. On the ribbon in the Design tab, in the Type group, select Save as Template.
3. In the dialog that opens, enter a descriptive name to save your chart template with. In this case I’ve used “Line w Format.crtx”. Click Ok.

This saves your chart format into a folder where the templates for all Microsoft Office applications are stored.