What can turn an ordinary chart into a good one can be another perspective. Rather than doing a whole different chart, consider using a secondary axis.
As a bonus in this lesson we also show how to add another series of data into a chart.
Our exercise and result are in the Second Axis file.
Here is a basic chart. We keep it simple for the example. It shows gross margins and costs by region, with the sum of the two representing sales.
Let's take it a step further. Let's add the Gross Margin Percent.
The first step is to add in the additional data to the chart. Right click on the chart and select Source Data.
In the Source Data dialog box, click on the Series Tab and then click the Add Series button on the left side.
You will then see sections for the Name and Values. To add each, we click in the box, then go to the worksheet and then highlight the range. First we do the name field.
Then we add the Values range.
Then we click OK. But what happens to the chart? In this case, nothing visible. The problem is that the percentage number is too small to show up.
What we need to do is move it to the secondary axis. But first we need to get it to appear. To do that, we temporarily modify the percentage formula in one cell, so the number is big enough to show.
Then we go to the chart. We see the blue bar on top for our temporary value.
Now we can move it to the secondary axis and change the chart type. Right click on the blue bar and select Format Data Series.
Then select the Axis tab and change the value to Secondary Axis.
It looks out of whack, but we can get it together quickly.
Right click on the blue bar and again and this time select Chart Type.
Now select Line for the chart type. You only will be affecting the percentage values on the secondary axis.
Now go back and change the formula.
Then go back to the chart. The secondary axis has reset itself. If not click on the axis and change the values so the line appears within the chart and not along the bottom or top.
Last we right click on the line, select Format Data Range and make the line bolder and change color so it shows up.
There you have it. The chart has some more meaning to it now.
To recap the steps to add values to a chart and plot them on the secondary axis:
Add in the additional series of data
If the value is too small to show for the new series (like percentages) then modify one calculation in the source data to make it large enough so that it appears on the chart
Right click on the new cell or series that appears in the chart and change it to the secondary axis
Right click on the new cell or series and change the chart type for the series that appears on the secondary axis
Reset the formula in the source data if you had to modify it temporarily
Modify the scaling on the secondary axis if needed
Format the new series on the chart if needed
While it seems like a lot of steps as we went through it, once you have the hang of it, it goes pretty quick.
Make use of the secondary axis to give insights that go beyond the primary axis data and take your charts to another level.