How to Create Excel Graphs Templates

We might not normally think of creating templates for Excel charts and graphs. But if you’re a frequent user of the charts feature in Excel, you may spend quite a bit of time getting the colors, appearance and elements of a chart just right. Who wants to keep doing that over and over?! You can create a template for each customized chart and graph you have and do all that hard work in a couple of clicks. Here’s how.

Download Excelchartsgraphstemplates.xlsx and work along.

Steps

1. Select the data you wish to chart and press F11, to create a chart in the default style on its own worksheet, or click the Insert tab and choose your starting chart type.

NOTE: If your data is contiguous (no blank rows or columns), click into one cell in the chart data. Excel will automatically select the rest of the data when you insert your chart.

2. Make your customizations to:
   1. Series format
   2. Data labels
   3. Data table
   4. Axis labels
   5. Chart title
   6. Legend placement

NOTE: In Excel 2010 and prior, use the Design tab. In Excel 2013 and later, access the Chart Elements tool by selecting your chart and clicking the plus sign icon to the upper right of the chart area.

Keep in mind that you may select a single series or single data point (column, bar, pie wedge, etc.) and format it as you would any other shape, choosing a specific color, rather than relying on the active theme to choose it for you. So you can practice this skill in the exercise file, change the color of a data series to a different color you’ll recognize in a new chart.

3. The next steps vary by version.
   1. In Excel 2010 and earlier: On the Design tab, click the second button from the left, Save as Template.
   2. In Excel 2013 and later: Right-click on the chart itself and choose Save as Template.

Name it and click Save.

NOTE: Allow the .crtx file to save at its default location. This will make it easier to locate it when you want to apply it to a new chart.

4. Now, when you want to apply it, click the dialog box launcher on the in the Charts group on the Insert tab and choose Templates. In 2013 and later versions, you will need to click on the All Charts tab to see Templates. In 2010 and 2007, Templates will be the first entry on the left in the dialog box.

NOTE: The dialog box launcher is the small down and right facing arrow in the lower right corner of a button group on the ribbon.

You can also choose the Change Chart Type button after creating a chart in a different style. You’ll see the Templates choice in the same place.

Success Factors

If your organization is moving to a new version of Excel, it’s a good idea to save your often used charts as templates to preserve the color and format choices. The defaults tend to change from version to version and you may want to preserve your hard work and give yourself time to make a decision on any format changes.

Though you may have chosen a different format and a different name for your sample, to see how the same chart template was applied to the Invoice data as was to the Sales data, download this file Excelchartsgraphstemplates-Completed Sample.xlsx