When you're creating charts in Excel, it can be useful to show two metrics side by side which are on different scales. For example, if you wanted to show charts for sales over time and profit margin over time, you'd normally need to create them on separate charts. But it is possible to show them all on the same chart using a little configuration.
For example, take this data set
If we were to create two charts, they'd look like this
But if we selected both metrics into the same chart we'd end up with the following, which tries to plot a % and $ value on the same chart - it just doesn't work well at all.
Luckily we can amend this chart so that one of the metrics is plotted on a second vertical axis on the right of the chart. Let's say we want to do this to the profit margin metric.
First, click on the line on the chart and the chart properties menu will pop up on the right hand side.
In the ‘Series Options’ Section select ‘Secondary Axis’. The chart will now change to something like this:
It's now possible to compare these metrics side by side on the same chart.
Turning one of the data series into a bar chart can add an extra dimension to the chart, this type of mixed chart is often used in publications like The Economist.
To change the chart type, simply select the metric (Revenue in this example) and right click, go to the change chart type option on the ‘Chart Options’ toolbar. Here you can select which chart to change and choose a chart type.
Here's the finished chart: