Create a Line-Column Pivot Chart in Excel 2007

When you create a chart from a pivot table, the default chart type is a column chart, unless you have changed the settings on your computer.

Shown below is an example of a default chart from a pivot table.

You might want to change it so the chart has lines and columns -- a combination chart type. You’d like to keep some of the columns, and change one column to a line.

In the chart below, the Cookies series has been changed to a Line chart type, and it stands out from the rest of the series.

In Excel 2003, there are Custom chart types available in the Chart Wizard, and some of those are Combination chart types, such as Line - Column, or Line - Column on 2 Axes, as you can see in the picture below.

In Excel 2007 there's no Chart Wizard, and no Combination Chart types that you can choose, as there were in the Excel 2003.

However, in Excel 2007, or any earlier version of Excel, you can create your own combination charts.

In this example, we'll change the Cookies series to a line, so it stands out from the other series.

1. In the pivot chart, right-click on one of the Cookies columns.
2. In the popup menu that appears, click Change Series Chart Type

3. In the Change Chart Type dialog box, click the Line chart type

4. Click one of the Line subtypes, then click OK.

The chart is now a combination chart, with columns for Bars, Crackers and Snacks, and a line for Cookies.

In some charts, there might be a big difference between the numbers in the chart's series. For example, your chart might show the quantity of cartons ordered, which ranges between 50-100 per day, and the daily sales totals, which are between 5000 and 10000 daily.

In the Pivot Chart shown below, the blue quantity columns are barely visible at the bottom of the chart, because they represent amounts that are much smaller than the red Sales columns.

To make the Quantity series easy to see, we'll change it to a Line chart type, as described above.

We'll also add a Secondary Axis, which will create another set of numbers along the right side of the chart. The Quantity will be plotted on that axis, using a smaller range of numbers.

1. Because the Quantity series is so hard to see, we'll select it by clicking on the Pivot Chart.
2. Then, on the Ribbon, click the Format tab
3. In the Current Selection group, click the drop down arrow, and click on the Qty Series name.
   

With the Quantity series selected:

1. On the Ribbon, click the Design tab, then click Change Chart Type
2. Click on the Line Chart type and a Line Subtype
3. Click OK

Then, to add the Secondary Axis:

1. Select the Quantity Series
2. On the Ribbon, click the Layout tab, and in the Current Select group, click Format Selection.
3. In the Format Data Series dialog box, click Series Options.
4. Under 'Plot Series On', click Secondary Axis, then click Close
   

The Quantity series is now easy to see, and its axis is on the right, showing its smaller range of numbers. The Sales series is plotted on the axis at the left, with a larger set of numbers.

For more information on pivot tables, save please see the Pivot Table Topics on my Contextures web site.

For answers to frequently asked questions about pivot tables and pivot charts, you can refer to the Pivot Table FAQ page.

If you're using Excel 2003, you can follow these instructions to use the Chart Wizard to create a Pivot Chart. The Chart Wizard has been removed from Excel 2007.