How to Trend Data with a Secondary Axis in Excel Reports
Comparative trending, commonly used in Excel dashboards and reports, is where you chart two or more data series on the same chart so that the trends from those series can be visually compared. In some trending components, you have series that trend two very different units of measure. For instance, the table in this figure shows a trend for People Count and a trend for % of Labor Cost.
These are two very different units of measure that, when charted, produce the unimpressive chart you see in this figure.
Because Excel builds the vertical axis to accommodate the largest number, the percentage of labor cost trending gets lost at the bottom of the chart. Even a logarithmic scale doesn’t help in this scenario.
Because the default vertical axis (or primary axis) doesn’t work for both series, the solution is to create another axis to accommodate the series that doesn’t fit into the primary axis. This other axis is the secondary axis.
To place a data series on the secondary axis, follow these steps:
Right-click the data series and select Format Data Series.
The Format Data Series dialog box appears, as shown in this figure.
In the Format Data Series dialog box, expand the Series Options section and then click the Secondary Axis radio button.
This figure illustrates the newly added axis to the right of the chart. Any data series on the secondary axis has its vertical axis labels shown on the right.
Again, changing the chart type of any one of the data series can help in comparing the two trends. In this figure, the chart type for the People Count trend has been changed to a column. Now you can easily see that although the number of people has gone down in November and December, the percentage of labor cost continues to rise.
Technically, it doesn’t matter which data series you place on the secondary axis. A general rule is to place the problem data series on the secondary axis.