Once you have mastered some of the tools in charts (secondary axis, change series chart type etc), you may encounter some problems that need to be addressed. One of the more common issues is how to make Excel charts primary and secondary axis the same scale.
So below we are showing the revenue (bill) and profit. The profit is shown on the secondary axis, but because some of the points are negative, the secondary axis starts at -5. This makes the chart hard to read. It is almost as if you need another line (red thick line below) to show where the secondary axis crosses at 0.
The manual way to fix this is to go into the Axis and manually change the minimum and maximum values. The problem is you need to go into the chart every time the data changes.
Create a common scale for the Primary and Secondary axis
The trick is to create a common scale so that the primary and secondary axis start and end at the same point. The only way this can happen is if the smallest and biggest number for both data series are the same. So to mimic this behaviour you can:
Determine a common axis scale that will handle the 2 data sets
First create 2 new columns and call then Primary and Secondary Scale. In the first cell create a MIN function that looks at ALL the original data points and finds the smallest number. In the last cell do the same but this time a MAX to find the biggest number out of all the data points. In E8 and E34 just equals to the adjacent cells. You now know what the scale needs to be.
Insert the new series into the chart
However you like to do it, insert the 2 new series into the chart. Below we just dragged the coloured boxes wider. You may not be able to see the new series but don’t worry about that for now.
Match the series to the correct axis
The next step is to make sure that there is one of the new series assigned to the primary axis and one to the secondary axis. Depending on your version of Excel you may use different methods but in Excel 2016 it is nice and easy to look at the screen for a combo chart. As shown below I can make sure that the one series (Bill) is on the same axis as the series called Primary Scale.
Hide the scale series
We are almost there. Below you can see that the scales are now the same, and this will always be the case, because the formula in cells D8, E8,D34 and E34 will make sure that both sides always start and end at the same place. But you can see that the new series are being shown. We need them to be there but we don’t want to see them.
To hide the series all you need to do is tell each series to have no fill, border and line (depending on how they are showing). These series may be hard to see so the easiest way to customise them is to click on the Chart, click on the Format tab, and find the series called Primary Scale. Just below this dropdown you can click on Format Selection.
On the resultant options box, change the fill to No Fill and the Border to No line.
You will do the same for the other new series (Secondary Scale).
Primary and secondary axis now have the same scale (automatically)
The end result is a chart that will (automatically) rescale the primary and secondary axis to always be the same. This will make the chart much easier to read and interpret.