How to Create Combination Charts with a Secondary Axis in Excel
When you have more than one data series that you want to plot on the same chart, you can create a combination chart in order to deliver a visual representation of each of the data series. Using this format, you can easily choose the type of chart, best suited to the respective data series.
There are some best practices to follow when creating combination charts with a secondary or dual axis. Let’s get started with a simple example, in order to illustrate when to create a combination dual axis chart and the best practices associated with combination charts.
There are four main types of tennis serves, namely the flat serve, the slice serve, the topspin serve and the kick serve.
The flat serve requires the most strength out of the four. It is the most common serve, utilized by tennis players and the easiest to learn. There is no spinning of the actual ball involved. The flat serve has the distinct advantage of being the easiest of the serve types to learn and the opposing player is not left with much time to react. The disadvantages associated with this type of serve is that it is harder for the ball to get over the net without a spin, and if the opposing player does manage to return the serve, it will come back stronger.
The kick serve is for more technically advanced players than the flat serve. It involves a combination of tennis techniques. The tennis player is required to combine a top spin and a side spin, with an aggressive swing and high racket head speed. The kick serve is the hardest to learn but it has the advantage of also being the hardest of the serves, for the opposing player to return.
The topspin serve is similar to a flat serve but the player is required to ensure, the ball bounces high. It is the most consistent and predictable of the four serves and allows a player to regain control of fluctuations. It has the disadvantage of being easy to see and thus easy for the opposing player to return. It allows for control and accuracy in positioning.
A slice serve is also similar to a flat serve, however, the way the tennis ball is hit differs. This serve does not require as much power as the flat serve and can result in the ball either moving to the outside of the tennis court or towards the opposing player’s body. It is a good serve when one wants to keep the opposing tennis player guessing, but it has a high chance of hitting the net, if not delivered properly.
An ace in tennis refers to a serve that is not touched by the opposing player, thus yielding additional points for the player that delivered the serve.
A hypothetical head coach running a tennis clinic would like to see the number of flat serves recorded for each month, on a chart as well as the % of flat serves that turned out to be aces.
An image of the source data is shown below:
1) In order to create a combination dual axis chart, firstly select the data as shown below.
2) Go to Insert > Charts > Insert Combo Charts.
3) Click on the drop down arrow and choose Clustered Column – Line on Secondary Axis.
4) Format the chart by adding a title, choosing a style, removing the grid lines and increasing the font size as shown below.
5) Add and format data labels if needed, in order to provide further clarity and differentiate between the two series, as shown below.
There are a few best practices to consider when creating combination charts with a dual axis. They are useful when the different data series have vastly differing scales. As in the above example, we had a percentage and a quantitative number series, so it was best to plot these two series on a different axis.
However, it is best to use combination dual axis charts with at most two data series only, or else they become too cluttered and it becomes difficult to differentiate between the different data series and pinpoint the axis, which is relevant to said data series. Creating a combination dual axis chart does not override good charting practices such as choosing the appropriate chart type for the data series. Do not choose a line chart for a data series that actually needs a XY scatter plot. Also if the data scales are vastly different, add data labels to the chart in order to ensure that clarity is conveyed. Don’t just put two random data series on a combination dual axis chart either, the two data series must relate to the same subject or theme.
Download working file
Dual axis, combination charts are useful for showcasing two different data series with different scales, on the same chart. There are certain best practices to follow, however when creating these charts, in order to not create confusion and actually convey meaning.
Please feel free to comment and tell us about your views on the different tennis serves as well as combination dual axis charts.