Contextures

Home > Charts > Basic > Combo

Excel Line Column Chart 2 Axes

If you use two different chart types in a single chart, it's called a combination chart, like the line-column chart shown below. This example is on 2 axes, and the steps are shown below

coloured labels on axes for line column chart

Author: Debra Dalgleish

What Is a Combination Chart?

In Microsoft Excel, you can select data on a worksheet, and insert a chart based on that data. You select a specific chart type, such as a column chart, and all of the series are created with that chart type.

For some charts, you might prefer to see one or more of the series in a different chart type, such as a line chart. If you use two different chart types in a single chart, it's called a combination chart (or combo chart), like the line-column chart shown below.

combination chart

Video: Make a Combination Chart

To see the steps for creating a line-column chart, please watch this short video. There are step-by-step written instructions below this video.

Video Timeline

  • 0:00 Introduction
  • 0:31 Create a Column Chart
  • 0:49 Change Chart Series to Line
  • 1:17 Put Line on Secondary Axis

Set Up the Source Data

First, set up your data for the chart, on an Excel worksheet.

In the example shown below, the product name, number of cases sold, and sales amount are in adjacent columns

  • Headings are in cells B4: D4
  • Data is in cells B5: D8

At the bottom of the list, in cells B9:D9, the total amounts are calculated, using the SUM function.

  • The total could be included in the chart, but I've left it out in this example.

There is a worksheet heading in cell B2 - Product Sales.

chart data

Create a Column Chart

First, we'll create a column chart from all of the data, and later we'll change one series in the Excel graph to a line chart.

To create a column chart:

  • Select any cell in the chart data range - B4:D8.
  • Next, on the Excel Ribbon, click the Insert tab
  • Then, in the Chart group, click the Column button
  • In the drop-down menu, in the 2-D Column section, click the first chart type -- Clustered Column chart

insert column chart

Column Chart on Worksheet

This creates a chart that is embedded on the active worksheet, with two series - Cases and Amounts.

  • Both the series are shown as columns.
    • Cases, the first series, are in the blue column
    • Amounts, the second series, are in the red column
  • Product names are shown in the axis labels on the horizontal axis (x-axis)
  • Amounts are shown on the vertical axis (y-axis)
  • Each series name is shown in as a legend entry, with a colour code beside it

Because the Cases numbers are much smaller than the Amounts, it's hard to see the Cases series in the chart.

column chart

Change Series Chart Type

To make the Cases series stand out, we'll change it to a line series, and later we'll plot that line graph on a separate axis.

In this example, the Cases series is difficult to see, so you can use the Ribbon commands to select it.

To select a specific series:

  1. On the worksheet, click on the chart to select it.
  2. On the Ribbon, click the Layout tab, under Chart Tools
  3. At the left end of the Ribbon, in the Current Selection group, click the drop down arrow
  4. Click Series "Cases" to select that series.

    layout tab on ribbon

To change a series chart type:

  1. In the chart, right-click on one of the selected Cases columns.
  2. In the shortcut menu that appears, click Change Series Chart Type
  3. In the Change Chart Type dialog box, click on the Line category
  4. Next, click on the first Line chart type

    change chart type

  5. Click OK to apply the change, and to close the Change Chart Type window.

The Cases series changes to a Line chart type, but it is still hard to see, because the numbers are so small.

In the next section, we'll change its axis.

line column chart

Create a Secondary Axis

In the steps below, we'll plot the Cases on a secondary axis, so the line is easier to see.

  1. On the worksheet, click on the chart to select it.
  2. On the Ribbon, click the Layout tab, under Chart Tools
  3. At the left end of the Ribbon, in the Current Selection group, click the drop down arrow
  4. Click Series "Cases" to select that series.
  5. In the Current Selection group, click Format Selection

    format selection

  6. In the Format Data Series window, click the Series Options category, then click Secondary Axis.

    format data series

  7. Click OK to close the window.

Now the Cases series is easier to see, because it is a solid line, plotted on the secondary axis, which goes from 0 to 250, instead of the Primary vertical axis, which goes from 0 to 18,000..

line column chart 2 axes

Format the Axes

Thanks to Michael Gizzi for this tip

After you add a secondary axis, you can use axis titles or colours to identify which axis is used by each series. In this example, there are only two series, so the series colour can be used for its axis.

The Amount is on the primary axis, at the left, so you can colour its labels red:

  1. Click on the primary axis, to select it
  2. On the Excel Ribbon, click the Home tab
  3. Click the drop down arrow for Font Color, and click on a red that matches the series colour.

The Cases are on the secondary axis, at the right, so follow the same steps, to colour its labels blue.

In the completed chart, shown below, you can see the coloured labels on each axis. This makes it easier to spot which axis a series is plotted on.

coloured labels on axes for line column chart

Video Transcript: Create Line Column Chart

Here is the transcript for the video shown above.

Create a Line Column Chart

Introduction

In Excel 2003, and earlier versions, when you inserted a chart, the chart wizard
opened.

You could see standard chart types, and there were also custom types, and some of these were combinations, like a Column - Area or Line - Column or Line - Column on 2 Axes

In newer versions of Excel, these combinations aren't available when you create a chart, but
you can easily turn any chart into a combination chart.

Create a Column Chart

Here is the same data, in Excel 2010, and I'll insert a chart.

On the Ribbon, go to Insert, and I'll start with a Column chart and I'll use a 2-D column.

And here we can see cases and amount.

The cases are a much lower number than the amount, so we can barely see them here.

Change Chart Series to Line

But with the chart selected, I can go to the Layout tab, and select cases.

Now all those columns are selected, even though we can barely see them.

I will right click on one of those, and click Change Series Chart Type.

And now, I can, instead of having that as column, I'll change that to a line

You can select any one of these lines.

I'll select the first one, without markers.

Click OK.

And now we can see that we do have columns and line together, in a single chart.

Put Line on Secondary Axis

One of the old combination chart types was a Line-Column on 2 Axes, and that would be useful in this situation, where we can barely see one of the series.

So again, I will select cases series, and format the selection.

Right now it's on the primary axis, and I'll put it on the secondary axis.

And click Close.

Now we can see the cases much more clearly.

They're plotted on this secondary axis, which goes from 0 to 250, and the amounts are still on the primary axis, which goes from 0 to 18,000

So in newer versions of Excel, you can create your own combination charts, by right-clicking on a series, and changing its chart type.

Get the Sample File

Click here to get a zipped sample file for this tutorial. The Excel file is in xlsx format, and does not contain any macros.

Related Links

Cluster Stack Chart Utility

Pie Charts

Waterfall Chart

In-Cell Charts

 

 

Pie Charts

 

About Debra

 

Last updated: December 26, 2022 8:01 PM