Microsoft Excel’s chart functions help readers quickly visualise information and see insights buried within sheets of endless data. They can include bar charts, line and scatter graphs and many more.
Normally a line or bar chart is sufficient enough to show off the data being analysed, helping to show trends, year on year differences, spikes and depressions. Sometimes however, there is need to create one single chart for two different, yet related, pieces of data. These graphs are called double axis or secondary axis graphs.
You may need this secondary axis graph for a number of reasons; however using the sample data below we are going to create a graph to show the quantity of impressions to a website over time, compared with the revenue over that same period of time.
If we were to create a normal singular line graph for the impressions vs revenue the graph would look like the below. As you can see the amount of impressions is unidentifiable as the numbers are so low compared with the revenue.
So now we will show you how to create a graph that looks like this…
As can clearly be seen, there is quite a strong correlation between the amount of impressions and the revenue, with revenue being generated slightly after the initial impressions.
How to create this double axis graph.
- At the top of you toolbar select Insert, Line and click on the first line graph.
- Right click on the box that comes up and click on Select Data.
- Click in the Chart Data Range and whilst holding CTRL highlight the data that you want to include in the chart. In this case the impressions and Revenue columns. Take your finger off CTRL between selecting each column.
- Name your series by clicking on Series 1, Edit and changing Series Name.
- Select your horizontal Axis Labels by clicking edit.
- Then highlight the data you want as you horizontal axis. In this case it is the date in our Period column.
- You will then end up with a chart that looks like this…
- Now to add a secondary axis. Click on Layout and then the drop down menu to the left of the tool bar. Click on one of your series in this case “Revenue”.
- Over to the left underneath the drop down menu select Format Selection.
- Select Secondary Axis on the box that comes up.
- You will now have a much clearer graph that looks something like this.
