MS Excel Charts: Compare two or more different data lines/sets on the same chart

You have an Excel spreadsheet, and you want to make a chart.
But the data is not comparing the way you want it to.
One line looks fine on your chart, and the other one barely registers.

What you want to do is lay one line on top of the other to find a correlation, if one exists.

ex. Comparing number of website Visitors to Pageviews over time.

Jan = 100
Feb = 120
Mar = 205
Apr = 135

Jan = 5000
Feb = 4500
Mar = 7350
Apr = 3700

Your chart might look like this:

It's not wrong, but it's not what you wanted.

The Visitors line is too flat to be visually useful.
The problem is that each visitor looks at about 50 pages, and that makes a huge disparity between the two figures -- 100 visitors looked at 5000 pages.

For a more visually significant comparison, you would want one data line layered on top of the other.

You might be thinking that you need to do some kind of percentage conversion. Or maybe change the values to something more similar.... Nope.

What you need to do is split the vertical / y-axis data series.

To do this:
  • Decide which numbers you want on the left side column, and which you want on the right.
I'll put Visitors on the left, and Pageviews on the right.
  • Right-click on the line you want on the right side data series -- in this case, the red Pageviews line.
  • Choose [Format Data Series] from the pop-up menu.
  • The menu should default to [Series Options].
  • There will be two options: 1) Primary Axis, 2) Secondary Axis.
  • Click on [x] Secondary axis.
  • The chart will change, and you need to hit [Close].
The new version of your chart should look like this:

Unfortunately, if you make any changes to your type of chart, you may have to do these steps over again.

There only seems to be room for two data series on a chart, so, if you've got 3 or more different, you'll to find a better way.

(Note: If you find a way to color-coordinate the numbers to match the line, or to add a label at the top of each column to specify which is which, please do let me know.)

No comments :

Post a Comment