Back
Excel

Chart Templates

Today’s author, Ben Rampson, a Program Manager on the Excel team.

Customers who author charts in presentations and reports often spend a significant amount of effort modifying their charts to get them looking just the way they want. While the new Chart Style and Chart Layout features in Office 2007 provide some great one-click options to quickly alter the appearance of a chart, there are still times when one needs to modify a chart even further. A company policy may require that a chart use particular colors, a project may demand a necessary chart layout, or a complex chart may necessitate the setting of many specific element properties. While there is always a onetime cost required to set up a chart for the first time, there is an easy way avoid these costs in the future when you need to re-create a similar chart.

One frequently underutilized feature in charting is the Chart Template. A chart template allows a user to quickly save and reapply settings from a previously created chart. Chart templates are standalone .crtx files that can be applied in a similar manner as chart types. In addition to saving the chart type, template files also contain chart element setting and formatting information. This information includes settings such as line and fill properties, axis settings, and element placement.

It also should be noted that chart templates do not save customer data, preventing customer information from being copied and saved into template files (customer data includes: data point and series values, text on titles, etc.). Although data is not saved, the existence, formatting, and layout of all elements is stored in the template. Assume a template is created from a chart that contains a title. When the template is applied the resulting chart will have a title in same location and with the same formatting as the chart used to generate the template, however, the default text of “Title” will appear in the element since the title’s data has not been stored in the template.

Steps to Create a Chart Template

  1. Insert a chart to use as a starting point for your template. Note that chart templates can be created and used in any of the three Office applications that have the shared charting engine: Excel, PowerPoint, and Word.
  2. Format the chart the way you normally would until the chart has the desired appearance. In my example I have made the following modifications to the chart:
    1. Applied a custom fill color to the series
    2. Deleted the legend
    3. Removed the horizontal axis line
    4. Set the horizontal axis maximum to 10
    5. Changed the line color of the gridlines and vertical axis line
    6. Reversed the categories order on the vertical axis
    7. Set the horizontal axis crosses at setting to maximum value
    8. Added a chart title and entered the title text
    9. Changed the chart titles font to be the same color as my series
      The resulting chart:
  3. Save the chart template.
    1. With the chart selected, click on the Design tab in the Ribbon
    2. Click on the “Save As Template” button located in the Type chunk
    3. Enter a template name and click Save

      Note: Saving chart templates as standalone .crtx files is a new feature for Office 2007. By using standalone files, chart templates can easily be shared amongst different users. The saved template location for Windows Vista users is C:Users<user_name>AppDataRoamingMicrosoftTemplatesCharts (C:Documents and Settings<user_name>Application DataMicrosoftTemplatesCharts for Windows XP users). This folder location can also be found using the “Manage Templates…” button in the Chart Type dialog. Once a template file is saved in this folder it can be accessed and applied in any of the three charting applications.

Steps to Use a chart template

  1. Select the data for your chart
  2. Click on the Insert tab in the Ribbon
  3. Open the Insert Chart dialog
    1. If you are using Excel then select the “All Chart Types” option from the bottom of any dropdown in the Charts chunk
    2. If you are using PowerPoint and Word then select the “Chart” button.
    3. Tip: if you have an existing chart you can select the “Change Chart Type” option to launch the same dialog
  4. Select the Template tab from the top of the left navigation menu
  5. Select a chart template from those listed on the right side of the dialog
  6. Select “OK” to apply the template

    The resulting inserted chart created from a different data set. While not completely finished it comes very close:

  7. Complete the chart by adding back the text elements that were not saved in the chart template
    1. In my example all I need to do to complete my chart is to modify the chart title’s text. In other cases it may be necessary to make additional minor changes such as tweaking axis settings stored in the template that do not work for the current data set.

Creating this chart was simple and only required two steps, inserting a chart using the desired template and entering the title’s text. This is a significantly easier process when compared to the list of steps I completed to build this chart for the first time. The finished chart:

Note that the above chart has fairly complex formatting and takes great advantage of the template feature; however, even templates with limited formatting can still save significant time in the chart creation process.

In summary, when creating a complex chart that you may use again in the future, remember to take advantage of chart templates. This powerful tool in charting can help you avoid duplicating effort and allows you to easily generate consistently formatted charts.

If you have any ideas on how we can improve chart templates in the future please feel free to leave your suggestions in the comments section.