Apply Excel Chart Templates to Workbooks with Multiple Charts

<< Click to Display Table of Contents >>

Navigation:  Professional > Tools & Charts >

Apply Excel Chart Templates to Workbooks with Multiple Charts

An Excel chart template may be applied to a workbook after it has been exported from EQuIS using the following steps.


1.Export a test chart from EQuIS Professional.

2.Format the chart to your requirements.

3.Once the chart is formatted (in Excel), do the following:

a.Right-click on the chart.

b.Select Save as Template. This saves the chart as a *.crtx file by default to a user's %appdata%\Microsoft\Templates\Charts folder. Chart templates not in this folder will not appear in the Templates list.

4.Apply the template to a new chart in the same workbook by completing the steps below (procedure may be repeated for each chart in workbook).

a.Right-click on the chart and select Change Chart Type.

b.Click on the Templates tab at the top of the list.

c.Select the template to apply.

d.The templates will only be in this folder if saved to the default location when created. If the templates are saved to another template, click on Manage Templates on the Change Chart Type window; an Explorer window will pop up with the default chart template location, and other templates can be copied into here so that they appear..

5.Applying the template to all charts in the same workbook will involve the use of a macro as follows:

a.If not already visible, right-click on the ribbon and select Customize the Ribbon to check the Developer tab and press OK.

b.Select Record Macros on the Developer Tab.

c.Use COPY_CHART as the Macro name. Choose to store the macro in the Personal Macro Workbook if the macro should be available each time Excel loads; other options are to store it separately in a new workbook, or store it in the current workbook and resave it as an xlsm file. Press OK.

d.Stop recording the macro.

e.Within the Macros window, select Step Into with the COPY_CHART macro selected.

f.Paste the code shown below between Sub and End Sub.


Dim Cht As Chart

For Each Cht In ActiveWorkbook.Charts

Cht.ApplyChartTemplate ( _

        "{Enter the full path to your template here}" _


Next Cht


The macro will automatically loop through all of the charts in the workbook and apply the specified chart template. Change the file path above to your template name and remove the {} (squiggly brackets).