Moving a Series to a Secondary Axis in Excel 2007:-

You might want to communicate data on a chart where the series are different orders of magnitude. In the top chart in Figure (given below), the first two series represent sales and profit and show numbers in the hundreds of thousands. The third series is a profit percentage where the values are between 20% and 80%. When you plot these three series on a column chart, the columns for the profit percentage series will be so small that they will not be seen.



(In the top chart, the third series is too small to be seen. In the bottom chart, a secondary axis, a new chart type, and an axis font color solves many problems.)

The solution is to plot the third series on a secondary axis. The left axis will continue to show hundreds of thousands, but the right axis scales to show percentages from 0% to 100%. To move a series to the secondary axis, you use the AxisGroup property:

ActiveChart.SeriesCollection(3).AxisGroup = xlSecondary

Using a secondary axis solves one problem but introduces a new problem: How will the reader know that it is the profit percentage series which is plotted against the secondary axis? You can change the chart type of the third series from a column to a line. You can change the ChartType property for an individual series as follows:

ActiveChart.SeriesCollection(3).ChartType = xlLineMarkers

To further help the chart reader, you can change the font color of the tick labels for the secondary axis to match the line color of the third series. In the following macro, the font color for the secondary axis is changed to match the fill color of the third series (see the bottom chart in the above Figure):

Sub MoveToSecondaryAxis()

Dim cht As Chart

Dim ser As Series

Dim ax As Axis

Set cht = ActiveChart

Set ser = cht.SeriesCollection(3)

ser.AxisGroup = xlSecondary

ser.ChartType = xlLineMarkers

Set ax = cht.Axes(xlValue, xlSecondary)

ax.TickLabels.Font.Color = ser.Format.Fill.ForeColor

End Sub

Unfortunately, this is a snapshot type of change. If you later change the theme or the color scheme of the chart, you have to change the tick label color to match the new Series 3 color.