Software

Work more efficiently by changing Excel's chart defaults

If you make the same changes to every chart, STOP! Make them once and save your altered chart as the default.

If you consistently change Excel's chart defaults, you're wasting time. Instead, change the default chart type and its attributes to fit your needs. First, you create a chart that fulfills your charting requirements and save it as a template. Then, save the template as your default chart.

To illustrate this quick and easy switch-a-roo, let's base a stacked bar chart on the data shown below and make a few changes, as follows:

  1. Select the data range, A2:E14.
  2. Press [Alt]+[F1]. That's the shortcut for creating a chart in the current worksheet. Press [F11] to create a shortcut in a new worksheet. By default, Excel created a column chart.
  3. To change the chart's type, right-click the chart and choose Change Chart Type. Or, click the Design tab and select Change Chart Type in the Type group.
  4. Select Stacked Column (the second thumbnail in the first line) and click OK.
  5. Next, select the legend and press [Delete].
  6. Right-click the plot area, choose Format Plot Area, and then choose Format Gridlines.
  7. In the resulting dialog, choose Line Style in the left pane and then select Round Dot (second option) from the Dash Type dropdown.
  8. Click OK.

At this point, we've made a few basic changes, so let's save this chart as a template, as follows:

  1. With the chart selected, click the contextual Design tab.
  2. In the Type group, click Save As Template.
  3. Enter DefaultStackedColumn as the chart template's name, and click Save.

Now we're ready to save this template as the default chart type, as follows:

  1. Click the Insert tab and click the Charts group dialog launcher.
  2. Click Templates in the left pane.
  3. Select DefaultStackedColumn and then click Set As Default Chart.
  4. Click OK.

If you're using Excel 2003, it's much easier. Right-click the altered chart and choose Chart Type. In the resulting dialog box, click Set As Default Type. (Not everything's better in 2010!)

Everything's set. The next time you want to create a chart, just click [F11] or [Alt]+[F1], and Excel will generate a stacked column with no legend and dashed gridlines. Try it!

Editor's note: An example Excel document is provided as an aide to understanding this technique.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

2 comments
ToonV
ToonV

Very useful post, thanks!

I applied this to set a scatter plot as default (instead of a bar chart) and the following problem has come up.

Suppose your data (in cells A1:B4) looks like this:
 

A   B

10  100

11  99

12  98


If you select cells A2:B4 and insert a scatter plot, then Excel will give you a scatter plot with series A on the X-axis and series B on the Y-axis.
 

I go on to change the appearance of the scatter plot (deleting gridlines, series name and changing the size of the markers), I save this as a template and set this template as default.

Now, when I select cells A2:B4 again and press [ALT]+[F1], then Excel inserts a scatter with both the A and B series on the Y-axis and 1 2 3 on the X-axis. However, I wanted the A series on the X-axis and the B series on the Y-axis.

If you use the basic scatter plot as a default, this problem does not occur.

Any simple solutions?

Thanks

dan
dan

I do a lot of x-y scatter charts (Time on the X-axis) non smoothed lines for the data sets. The 2.25 pt default line widths are problematic. I have to manually change the width of each line. So I created a chart with 6 data sets. Set each line thickness to 1 pt. Saved it as a default chart as you described. Then selected 8 columns as well as the time column created the "default" chart and 6 of the lines were 1pt but the two additional data sates reverted back to the 2.25pt line thickness. So I really didn't change the "default" line thickness. Try it!