Charts With Different Data – Secondary Axis

We have a simple chart with two sets of data, one with a significantly higher scale than the other: 

We’ve seen how to create a chart in a single keystroke (Alt + F1 or F11), which results in this: 

Technically it’s correct, but the disparity in the values makes it difficult to see the variations for Tom.  The alternative is to put the values for Tom’s data on a secondary axis.  To do this, right click on Tom’s data and choose Format Data Series.  From there you will see the option to select Plot Series On Secondary Axis:

Good and bad!  The good part is that it set the values for Tom off the right axis, which makes the variances more visible, however now the data overlaps!  Fixing that is easy.  Right click on the data for Tom and choose Change Series Chart Type: 

From here, choose a different type of series, possibly Line with Markers, and click OK: 

Much better!  To improve the looks of this graph, you may want to take two more steps.  One would be to right click on the Legend, choose Format Legend and move it to the bottom.  Finally, click on the chart which will cause the dynamic Chart Tools menu to appear, and from the Design tab, choose a more elegant option:

And there you have it!  Nice job!  But wait….what if there are THREE sets of data that are significantly different?  Check back tomorrow and we’ll see how to address that.



Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

No comments yet.

Leave a Reply