Save Time Customizing Excel Formats

Learn How to Save and Reuse Custom Chart Formats in Excel

Several customers have said: I'd like to use a different format for some of the charts I produce. I don't want to have to redo it all manually every time. GOOD NEWS!

  • QI Macros have this feature built into our Chart menu under Remember and Apply Chart Formats
  • If you don't have QI Macros, but have Excel 2007 and later versions, you can save a chart format to a template and then apply it to any future chart.

Here's How to Save and Reuse a Chart Format

Once you've drawn a chart, you can change the formatting of the lines, fonts, colors, whatever you want. In this example, I've changed a c Chart to use Broadway font and other colors.

c chart broadway font

If I then click on Chart Tools - Design - Save as Template and give it a name like c Chart.ctrx, I can then access this format later.

save as template

Then I can reuse this template everytime I draw a c Chart.

Applying the Template to a New Chart

So, if I draw another c Chart...

original c chart

And then click on Chart Tools - Design - Change Chart Type and select Templates-c Chart:

change chart type

Excel will reapply all of the formating changes in the template:

c chart after changes

Pretty slick huh?

Warnings: This should work for most of the charts except those that use Error Bars. QI Macros charts that use error bars are:

  • XbarR, XbarS, XMedianR
  • p and u charts
  • Box and Whisker Plot

Workaround for QI Macros charts that use error bars:

If you switch the control chart format to "Wave", the X, p and u charts will use normal formatting that can be modified by this technique. Click on Control Charts (SPC) -> Control Chart Rules -> p/u UCL/LCL Format to get the "Wave" format:

    control chart rules menu

Tarpits: I found out the hard way that Excel will remember EVERYTHING about the chart format! It will remember red out-of-control points and lines on control charts and change those points red anytime you apply the format. So you might want to use QI Macros Chart Menu -> Clear Stability Analysis before saving the template.

Also, make sure the axis options are set to auto, not a fixed value. Otherwise, when you apply the format your chart may vanish because the data is below the fixed limit (22.2):

Set axis options to default before saving template

Stop using old technology!

Upgrade Your Excel and Data Analysis Skills to Smart Charts Using QI Macros.

Track Data Over Time

Primitive Chart
line graph
Line Graph
Smart Chart
control chart
Control Chart

Compare Categories

Primitive Chart
pie chart
Pie Chart
Smart Chart
pareto chart
Pareto Chart

Analyze Variation

Primitive Chart
bar or column chart
Bar or Column Chart
Smart Chart

QI Macros add-in for Excel makes creating smart charts a snap.