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
In 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, like the line-column chart shown below.
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.
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.
At the bottom of the list, the total amount is calculated, using the SUM function. The total can be included in the chart, or left out.
First, we'll create a column chart from all of the data, and later we'll change one series to a line chart.
This creates a chart that is embedded on the active worksheet, with both the series shown as columns. Because the Cases numbers are much smaller than the Amounts, it is hard to see the Cases series.
To make the Cases series stand out, we'll change it to a line series, and later we'll plot it on a separate axis.
In this example, the Cases series is difficult to see, so you can use the Ribbon commands to select it.
The Cases series changes to a Line chart type, but it is still hard to see, because the numbers are so small. Next, we'll change its axis.
We'll plot the Cases on a secondary axis, so the line is easier to see.
Now the Cases series is easier to see, because it is on the secondary axis, which goes from 0 to 250, instead of the Primary axis, which goes from 0 to 18,000..
Thanks to Michael Gizzi for this tip
After you add a secondary axis, you can use 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:
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.
Click here to get a zipped sample file for this tutorial.
The file is in xlsx format, and does not contain any macros.
Last updated: July 15, 2021 1:37 PM