Present your data in a combination chart

To emphasize different types of information in a chart, you can combine two or more chart types in that chart. For example, you can combine a column chart with a line chart for an instant visual effect that might make the chart easier to understand.

When the range of values for different data series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern and is represented in the chart legend. You can plot one or more data series in a chart. Pie charts have only one data series.) in your chart varies widely, or when you have mixed types of data, you can plot one or more data series from a different chart type on a secondary vertical (value) axis.

Formatted combination chart

In this article


Create a combination chart

The following procedure will help you create a combination chart with results that are similar to those shown in our example combination chart graphic. For this chart, we used the example worksheet data. You can copy this data to your worksheet, or you can use your own data, as long as you use the same column headers and worksheet structure.

  1. Copy the example worksheet data into a blank worksheet, or open the worksheet that contains the data that you want to plot in a combination chart.

ShowHow to copy the example worksheet data

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note   Do not select the row or column headers.

Selecting an example from Help

Selecting an example from Help
  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.

 
1
2
3
4
5
6
7
A B C
Homes Sold Average Price
Jan 280 410
Feb 150 450
Mar 220 430
Apr 275 425
May 155 410
Jun 255 400
  1. Select the data that you want to plot in the combination chart.
  2. On the Insert tab, in the Charts group, click Column.

Charts group on Excel Ribbon

  1. Under 2-D Column, click Clustered Column.
  2. In the chart, click the data series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern and is represented in the chart legend. You can plot one or more data series in a chart. Pie charts have only one data series.) that you want to display in a different chart type, or select it from a list of chart elements (Layout tab, Current Selection group, Chart Elements box).

 Tip   For our combination chart, we selected the data series for Average Price.

This displays the Chart Tools, adding the Design, Layout, and Format tabs.

  1. On the Design tab, in the Type group, click Change Chart Type.

Type group on Excel Ribbon

 Note   If the whole chart changes to a line chart, you need to make sure that only one data series is selected before you change the chart type.

  1. Under Line, click Line with Markers, and then click OK.
  2. To plot the line on a secondary axis, do the following:
  1. In the chart, click the line that represents Average Price once to select the data series, or select it from a list of chart elements (Layout tab, Current Selection group, Chart Elements box).
  2. On the Layout tab, in the Current Selection group, click Format Selection.

Current Selection group on Excel Ribbon

  1. In the Series Options category, under Plot Series On, click Secondary Axis, and then click Close.
  1. Click the chart area (chart area: The entire chart and all its elements.) of the chart.
  2. On the Design tab, in the Chart Styles group, click the chart style that you want to use.

Chart Styles group on Excel Ribbon

 Tip   For our combination chart, we used Style 42.

  1. To change the size of the chart, on the Format tab, in the Size group, select the shape size that you want in the Shape Height and Shape Width boxes, and then press ENTER.
  2.  Tip   For our combination chart, we used 5 for the shape height and 5 for the shape width.

Size group on Excel Ribbon

 Tip   You can also resize a chart by dragging one of the corners of the chart until the chart reaches the size that you want.

  1. To add, format, and position a chart title on the chart, click the chart area (chart area: The entire chart and all its elements.), and then do the following:
  1. On the Layout tab, in the Labels group, click Chart Title, and then click Above Chart.

Labels group on Excel Ribbon

  1. In the chart, click the chart title, and then type the text that you want.

 Tip   For our combination chart, we typed Recent Home Sales.

  1. To reduce the size of the chart title, right-click the title, and then enter the size that you want in the Size box on the shortcut menu.

 Tip   For our combination chart, we used 18.

  1. To move the legend, do the following:
  1. Click the legend to select it.
  2. On the Layout tab, in the Labels group, click Legend, and then click the position that you want.

 Tip   For our combination chart, we clicked Show Legend at Bottom.

  1. To add vertical axis titles, do the following:
  1. On the Layout tab, in the Labels group, click Axis Titles, and then do the following:
    • Click Primary Vertical Axis Title, and then click the title option that you want.
    • Click Secondary Vertical Axis Title, and then click the title option that you want.

 Tip   For our combination chart, we clicked Rotated Title for both axes.

  1. Click each axis title, and then type the text that you want for that title.

 Tip   For our combination chart, we typed Number of homes for the primary vertical axis title and Average price per home in thousands for the secondary axis title.

  1. To change the font size of the axis titles, click each axis title, and then click the size that you want in the Font Size box.

 Tip   For our combination chart, we used 14 for the font size.

  1. To change the appearance of the markers that are displayed on the Average Price line, do the following:
  1. Right-click a marker, and then click Format Data Series on the shortcut menu.
  2. Click Marker Options, and then under Marker Type, click Built-in.
  3. In the Type box, click the marker type that you want to use.

 Tip   For our combination chart, we used a round marker type.

  1. Click the chart area of the chart.
  2. On the Format tab, in the Shape Styles group, click the More button Button image, and then click the effect that you want to use.

Shape Styles group on Excel Ribbon

 Tip   For our combination chart, we used the Subtle Effect - Dark 1 for the chart area.

  1. If you want to use theme colors that are different from the default theme that is applied to your workbook, do the following:
  1. On the Page Layout tab, in the Themes group, click Themes.

Themes group on Excel Ribbon

  1. Under Built-in, click the theme that you want to use.

 Tip   For our combination chart, we used the Office theme.

Top of Page Top of Page

Save a chart as a template

If you want to create another chart like the one that you just created, you can save the chart as a template that you can use as the basis for other similar charts.

  1. Click the chart that you want to save as a template.
  2. On the Design tab, in the Type group, click Save as Template.

Type group on Excel Ribbon

  1. In the File name box, type a name for the template.

 Tip   Unless you specify a different folder, the template file (.crtx) will be saved in the Charts folder, and the template becomes available under Templates in both the Insert Chart dialog box (Insert tab, Charts group, Dialog Box Launcher Button image ) and the Change Chart Type dialog box (Design tab, Type group, Change Chart Type).

For more information about how to apply a chart template, see Reuse a favorite chart by using a chart template.

 Note   A chart template contains chart formatting and stores the colors that are in use when you save the chart as a template. When you use a chart template to create a chart in another workbook, the new chart uses the colors of the chart template — not the colors of the document theme that is currently applied to the workbook. To use the document theme colors instead of the chart template colors, right-click the chart area, and then click Reset to Match Style on the shortcut menu.

Top of Page Top of Page

 
 
Applies to:
Excel 2007, Outlook 2007