Secondary axis

When we make charts that contain multiple data series, it happens that the data in them varies greatly, in terms of value ranges, or they are set in different units of measure. Also, we will sometimes want to show different series by using different chart types. For this purpose, we choose whether the series is displayed on the primary or secondary axis, and which type of chart we want to use to display the data on it.

In order to add a series of data to the secondary axis it is necessary to have a chart that consists of at least two series. Right clicking on the series opens the context menu in which it is necessary to select the Format Data Series option. After this procedure, on the right side of the chart, a menu will appear where it is possible to set which axis we want to display the selected series (Plot Series On). In order for the application of the secondary axis to make sense, one should choose a series that is significantly different from other series per data range or represents data in another unit of measure. If we have selected such a series, we further choose the option to show the series on the primary or secondary axis. By adding a series to the secondary axis, the chart will change immediately, and the series display (eg the size of the columns) will be proportional to the scale on the secondary axis. Below these options, there are two sliders, whose movement on the left or right side can be fine-tuned how this series will be displayed. The Series Overlap is used to determine the degree of overlap between the series, and Gap Width serves to set how wide the indicator of the series will be.

As already mentioned, the secondary axis can be horizontal or vertical. This depends on the type of chart used to represent one or more data series displayed on the secondary axis. For example, if at the beginning all the data series are displayed in Columns, by moving some of the series to the secondary axis, we obtain a vertical secondary axis, as the value columns are displayed on the vertical axis. If you changed the type of chart for this series and chose, for example, Bar would automatically instead of vertically obtain a horizontal secondary axis, as with these charts the values ​​are displayed on the horizontal axis.

In order to change the type of chart representing the series on the secondary axis, select the Change Chart Type option in the Design ribbon, and in the dialog box that opens to select the combined chart (Combo). At the bottom of the window, we can see which series is represented by a certain type of chart, the type of the type in the drop-down menu (for example, Clustered Column). Also, on the right side of the menu there is an optional field (Secondary Axis), based on which we can conclude which of the series of data is displayed on the secondary axis. By changing the chart type for the series on the secondary axis (for example, from the Clustered Column in Clustered Bar), we will change how these data are displayed, and depending on the newly selected chart type, we will also change the axis position.

In this dialog box, by changing the value of the optional field (Secondary Axis) it is easy to manipulate the series and move them from the primary to the secondary axis. Also, by changing the type of chart, it is easy to define how the data are visually displayed on the primary, and on which on the secondary axis. By combining such various types we can get to impressive graphical reports.