How to Create Custom Charts in Microsoft Excel
One of the most time-consuming things when creating charts in Excel is having to format them. This blog shows you how to save and reuse custom charts to ensure that you'll never have to format a chart again!

Posted by Andrew Gould on 10 November 2011 | 1 comment

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:

Formatted chart

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:
Saving a custom chart

3. Select the Custom Types tab.

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

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

 
  1. 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 standard 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:
Choosing a custom chart type

3. Select the Custom Types tab.

 

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

A chart using a 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:
Saving a custom chart template

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

  1. Type in a sensible name for your chart template.
  2. 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:
Choosing a custom chart template

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

  1. Select the Templates folder to see all of the custom chart templates you have saved (Excel will look in your default Templates folder).
  2. 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!
  3. Click OK.
The finished chart

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.

If you find yourself using the same custom chart type for most of the charts you create you can save yourself even more time by setting it as the default chart type.  See this blog for instructions on how to do this.

This blog has 1 comment

Comment added on 02 August 2012 at 18:38 GMT
Hi.. how did you made this chart..? its simple but its awesome.. how can i make exactly such charts in excel..
Reply from Andrew Gould

Glad you liked it!  There are quite a few steps involved in creating a chart like this - too many to list in a comment here.  I'll write another article soon which will explain how to create this type of chart and add a link to it in this article - check back next week!

A full-blown discussion forum is being built for this site, which will allow you once more to add comments and discussion threads.