Excel Chart: Use Multiple Secondary Axes to Put >=3 Kinds of Data in One Line Chart

Ad Office Tab

Tabbed View for Word, Excel, PowerPoint, Project & Visio

---

I my previous post, I practice how to use broken icon to stand for very large values in a bar chart. In fact, this method is only suitable for those data with the same data type. All data of the previous post is internet users and the percent value (Users/global internet users, you can think it as the other data type) is listed as data label at the left side. If we want to draw the percent and users count in one chart at the same time. What will happen? You will get the following chart. How to solve it or How to put data with different type (say >= 3) into one chart?

We usually use "primary axis" and "second axis" to solve two data types.  Or we can create multiple charts and overlap them one by one. 

Here is another solution. Use scatter chart to simulate a multiple secondary axes line chart. In fact, this solution is not complex; we just need create some auxiliary data and convert different type of data into the same type. For example, in the previous post, we can divide internet users by 1000, now all data is at the same level. Add an axis for that series to restore readers’ feeling of the real data. The following chart demos this solution.

Pre-request: Suppose we have the following data. A factory has 10 product lines and each line has different output, cost and product defective rate. And we want to draw 3 lines on one chart.

Step1: Create auxiliary data to simulate multiple Y axes.

In order to put 3 kinds of data into one chart, we need 3 axes. In scatter chart, we usually use one or multiple data series to simulate axes. The above auxiliary data will be used to create 3 Y axes. Select the range [F2-I12] and insert a scatter line chart into your workbook. Your chart should be.

Switch X & Y value for each series to make 3 data series vertical orientation. We'd also limit the primary axis to [-5-11]. Your chart will look like this.

Step2: Convert all raw data into the same data type.

Maybe you've found, if you divide column B by 100, column C by 1000 and multiply column D by 100, all data will be formatted to the same level.

Add all these data into the draft chart as real line chart. Your chart will look like this. Now, there are 6 series. 3 of them are axes.

Step 3: Prepare data label for axes

In fact, there are 3 secondary axes here. And they will stand for different data type. The most left is cost($) and the most right one is Defective Rate. Let's prepare the following data as data label for these axes. Use our "update data label tool" to change all data labels' content to different values. We also need enable error bars for them at the same time so we can get tick mark for each axis.

Step 4: Promote your chart

4.1 Change chart color and style
4.2 Add legend for all real data series
4.3 Add Legend data label, which is a special data label. You can add 3 new scatter points into draft chart and show data label for them
4.4 Add Title