The horizontal axis (x-axis) has four points 1,2,3, and 4. I've two sets of observations (data series) each for the four points in X-axis. I want to compare how the data series vary for each point on x-axis. The difference is in fraction and a line-graph is nearly superimposing one series with another. Modifying the major and minor units is of no help. Can anyone suggests how to plot them in MS excel (2010-2016) so that the difference between the data series for each x-axis point is visible. Put another way how to make the lines not to superimpose each other.

Default Line Chart by Excel 2016

The default Major and Minor units are: 20 and 4 for the above line-chart. If I edit them to be 0.10 and 0.01 respectively, the spacing between the lines remains the same (i.e. still overlapping) Any help will be much appreciated

  • The data is such that however much you fudge with the axis scale parameters, the points will be very close together. That is a characteristic of the data, and messing with the data to make the points further apart will obscure this characteristic. – Jon Peltier Feb 13 '17 at 15:16

The problem is that your maximum difference is less than 1% of the vertical axis scale, so you can't exaggerate the differences without distorting the underlying values.

The best you can do is plot the values, then in another plot, show either the differences or the ratios between the values. (Using a secondary axis shows these things, but not as clearly as separate charts).

Showing small differences which are much smaller than underlying values

  • The idea of ratio applies greatly to my case. While it normalize the scale, it also demonstrate the comparison of the data series as I would like to see. Thanks a lot. – KGhatak Feb 14 '17 at 6:25

What about plotting the difference of both graphs into the secondary axis.

This doesn't look straight forward so some sort of explanation is necessary, but the you can get the idea about the difference in values.

Graph with primary and secondary axis

  • Appreciate the innovative thoughts. But I'm afraid, it is not as visually appealing as it could be with original lines. – KGhatak Feb 9 '17 at 18:45
  • It's just that the difference is so small that it's very hard to show it – fernando.reyes Feb 9 '17 at 18:49
  • 1
    "..the difference is so small that it's very hard to show it." Isn't that enough? – Jon Peltier Feb 11 '17 at 22:58

Just select A and B and Insert column Chart

enter image description here

  • Thanks for the idea. However, the idea of ration (by Jon Peltier) gives a great normalization of the differences across large range of y-value. – KGhatak Feb 14 '17 at 6:28

To create separation, you can place trend B on the secondary axis and shift the secondary axis.

enter image description here

  1. Control-Click on trendline B.
  2. Select Format Data Series ...
  3. Under Series Options, select Plot Series On Secondary axis.
  4. Control-Click on the secondary axis (vertical axis on the left of the chart)
  5. Adjust the bounds to offset from the primary axis.

An alternate choice, without offsets, is to format the trend lines so each is visible.

enter image description here

In this case, I chose to:

  1. Increase the line thickness of trend line A.
  2. Make trend line B a dashed line, and changed the color to improve visibility of both lines.
  • Using secondary axis is going to be deceptive. The lines look parallel, and there is no visual (only numerical via the axis labels, if anyone can possibly juggle all that in their head while trying to read the data. – Jon Peltier Feb 11 '17 at 22:57
  • @Jon - I didn't say I thought it was a good way to visualize the data. I was answering "how to make the lines not to superimpose each other" - the OP question. I agree with the answers suggesting plotting difference as an appropriate visualization method. But I don't have enough context to say what is "the right answer" for visualizing it. – OldUgly Feb 11 '17 at 23:05
  • Sorry, didn't mean to be critical of your answer. I think the OP has a problem with the data being so close, and doesn't realize that's a characteristic of his data set. But fudging it to make it less close (which seems to be the request) obscures the data. – Jon Peltier Feb 13 '17 at 15:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.