Creating a Custom Excel Chart Type

Excel Dashboards by ExcelUser.com - Instructional eBook and Ready-to-Use Workbooks.

Suppose you've made a chart with custom formatting, and you'd like to use this formatting with other charts. You can create and save a custom chart type so that it can be applied to any chart. Start with your optimally formatted chart.

Select the chart and select Chart Type... from the Edit menu, or right click on the chart and select Chart Type... from the pop up menu. Click on the Custom Types tab, and choose the User-Defined option below the list of chart types.

Click the Add button, then fill in a name and description for your chart.

After you press OK, notice that the chart's name appears in the list of user-defined custom chart types, and when this type is selected, the description you entered is displayed below the thumbnail. To make this the default chart type, click on the Set as default chart button. Use the Delete button to remove a user-defined chart type.

Drawback to Applying a Chart Type.

When you define a chart as a custom chart type, all the formats from the chart are stored. But the chart type also stores the chart title and axis titles, text as well as font formats. When the chart type is applied to another chart, the font formats are applied to your titles, but the text in the chart type overwrites the text of your titles. This also occurs when one chart's formats are pasted onto another chart. The actual text of the title is part of the properties of the title in VBA, but it would have made more sense to me to treat the text of the titles as data of the chart, not formatting of the chart type.

Another problem with a custom chart type, or any chart with customized formatting. If a chart element with custom formatting is removed from the chart, then added again, the custom formatting is lost, and the element takes on the default formatting for that element in the default chart. The custom chart type can be reapplied to the chart to reformat the errant chart element.

This is an excerpt from Microsoft Excel Chart Types, which originally appeared in TechTrax Ezine.

Peltier Technical Services, Inc.
We can customize or automate any example from this site, or build a new one for you. To discuss your project or obtain a quote, please contact me at jonxlmvp@peltiertech.com

PTS Blog: Charts and Things

Jon's Excel and Charting Pages
- Jon's Charts - Tutorials - Excel - Site Index and Search - RSS

Peltier Technical Services, Inc., Copyright © 2008. All rights reserved.
You may link to this article or portions of it on your site, but copying is prohibited without permission of Peltier Technical Services.

Microsoft Most Valuable Professional

My MVP Profile