How to Save Time Formatting Microsoft Excel Charts with Chart Templates

How much time are you spending formatting the charts you add to an Excel worksheet or PivotTable? Do you frequently create the same type of Excel chart? If you’re often applying custom formatting to charts, then working with  Excel chart templates might be a huge time saver for your next chart project.

Microsoft Excel chart templates save all the formatting options and settings you want to use for repetitive charts, such as a monthly report or annual budget. Then, anytime you want to use the same formatting, you can apply the chart template to any new or existing chart. And working with chart templates is a timesaving way to apply consistent design and branding to charts created in your organization.

In this post, I’ll share the steps to create, save, and apply Excel chart templates. Or, watch the video below:

Creating an Excel Chart Template

To create a chart template:

1. Open an existing chart that includes the formatting you want to reuse in an Excel chart,
   -OR-
   Insert a new chart and change the formatting to prepare it for presentation.
2. Next, select the formatted chart.
3. Then right-click the chart and choose Save as Template…
4. The Save Chart Template window will open. Name the chart template file.
5. Finally, Click Save.

Modifying Default Chart Formatting

The default chart formatting in Excel typically includes many extra elements that aren’t always needed for our charts. A nicely formatted chart should consist of only the minimal elements (legend, axis, labels, gridlines, etc.). Simplifying your chart formatting makes the chart easier to read and allows the data to tell the story. This is especially important when Excel charts are used in PowerPoint presentations, where simplicity is key to effective communication.

Some examples of formatting changes you might apply include:

• Adding a different chart style or color scheme
• Deleting gridlines
• Deleting the legend (when only displaying one series)
• Adding data labels
• Reducing the gap width between series in a bar or column chart
• Showing categories in reverse order
• Hiding field buttons (in PivotCharts)

Naming and Saving Chart Templates

When saving a chart template, be descriptive and consistent with your naming options so you can easily choose from saved chart templates such as Type of Chart-Purpose-Color or Formatting. For instance, Column Chart-One Series-Quarterly Reports-Blue.

Chart templates are saved with a .crtx file extension and saved locally on your computer in a reserved templates folder. For example (this may vary on different systems and setups),
C:\Users\[Username]\AppData\Roaming\Microsoft\Templates\Chart
By default, this folder is a hidden item, so you may need to select this option from the View Ribbon tab to display it in Windows Explorer.

To share chart templates that might be useful to other team members, copy the templates to a shared location. Each person would then need to copy the templates to their own local templates folder, as described above.

Applying a Chart Template

Once you have a chart template, you can apply it to any new or existing chart, including Pivot Charts.

To create a new chart from a chart template:

1. Select the data you want to use for the chart.
2. Click a Chart button on the Insert tab of the ribbon to open the Insert Chart window.
3. Click the Templates tab on the left sidebar.
4. You will see a gallery of your Chart Templates. Select the one you want to use to create the chart. Click OK.
5. A new chart will be inserted on the sheet with all the template’s formatting options.

To apply a chart template to an existing chart:

1. Right-click the existing chart and choose Change Chart Type…
2. The Change Chart Type window will open. Click the Templates tab on the left sidebar.
3. Select the chart template you want to apply. Choose OK.
4. The chart type and formatting will be applied to the current chart.

Any changes you make to the chart after applying a template do not affect the chart template. You can, however, save the chart template again to update the template with new formatting.

Managing Chart Templates

Use the Manage Templates option to view, delete, and add chart templates in Excel.

To manage chart templates:

1. Right-click an existing chart and choose Change Chart Type…
2. The Change Chart Type window will open. Next, click the Templates tab on the left sidebar.
3. Select the Manage Templates option in the bottom left corner, which opens the Chart Templates folder in Windows Explorer.
4. From this location, you can add or delete chart template files. Any template files you copy into this folder will appear the next time you open the Template Gallery.

How will you leverage Excel chart templates to save time creating and formatting charts?

For more ways to be productive with Microsoft Excel, check out these additional shortcuts, tips, and techniques at TheSoftwarePro.com/Excel.

© Dawn Bjork, MCT, MOSM, CSP®, The Software Pro®
Microsoft Certified Trainer, Productivity Speaker, Certified Speaking Professional