Creating Custom Charts in Microsoft Excel

If you create lots of charts in Microsoft Excel at some point you'll have become bored with applying the same formatting options to each new chart that you create.  Fortunately, Excel allows you to save a custom chart type that you can use as a template for charts that you create in the future.

Creating the Basic Chart

Although charts work differently in different versions of Excel, the starting point for this technique is to create the basic chart that you want to save as a template.  The chart we're going to use is shown in the diagram below:

Not the prettiest chart you've ever seen, but it takes a long time to apply all of the formatting options.

Once you've perfected your chart you're ready to save it as a custom chart type.  The technique for doing this is different in different versions of Excel.

Creating Custom Charts in Excel 2003

In Excel 2003, to save a custom chart:

1. From the menu select: Chart -> Chart Type...
2. Complete the dialog box as shown in the diagram below:

3. Select the Custom Types tab.

4. Click the User-defined  option.

5. Click the Add... button.

6. On the next dialog box enter a name for your custom chart and then click OK.

Enter a name and optionally add a description for your custom chart.

7. Click OK once more to finish creating your custom chart type.

And that's it!  Once you've saved a custom chart type you can use it again in any other Excel file that you create.

Using a Custom Chart Type in Excel 2003

To use a custom chart type that you've previously saved, first create a new chart:

A new, basic column chart based on different data.

To change the new chart into one of your custom charts:

1. From the menu select: Chart -> Chart Type...
2. Complete the dialog box as shown below:

3. Select the Custom Types tab.

4. Click the User-defined option.

5. Select the custom chart you want to use.

6. Click OK.

When you click OK the new chart will take on all of the formatting properties that are saved in the custom chart type.

Notice that the custom chart type also applies the same title as the chart that was used to create the custom type.

Creating Custom Charts in Excel 2007 and 2010

To create a custom chart in Excel 2007 or 2010 you first need to create a chart and format it to your requirements.  Once you've done this, to save your chart template:

1. Select the chart and from the Ribbon select: Design -> Save As Template.
2. Complete the dialog box as shown below:

You can change the file location if you want to, but it's better to stick with the default Templates folder.

3. Type in a sensible name for your chart template.
4. Click the Save button.

And that's it!  You can now use your custom chart template in any new file that you create.

Using a Custom Chart in Excel 2007 or 2010

To use a chart template that you have previously saved, first select the chart whose formatting you want to change, then:

1. From the Ribbon select: Design -> Change Chart Type
2. Complete the dialog box as shown below:

Unfortunately, you have to hover the mouse cursor over an icon to actually see which chart template you are selecting!

3. Select the Templates folder to see all of the custom chart templates you have saved (Excel will look in your default Templates folder).
4. Find the specific template you want and click on it to select it - stupidly you have to hover the mouse cursor over the template in order to actually see what it is!
5. Click OK.

Unlike in Excel 2003, Excel 2007 and 2010 don't use the same text for the chart title, just the formatting.

Conclusion

And that's it!  You can create as many custom charts as you like in order to save you lots of time when formatting charts in future.

Comments on this blog

This blog has one comment: