Data Points Overlap in Line Chart

[Karen615]

I have a line chart (see attached) and the data points are very close numerically. My boss wants to see more detail so the data points can be visible. I changed the chart to a clustered column chart and he didn't like that. Is there a way of keeping the line chart but being able to show more detail? HELP!

Your help would be greatly appreciated.

Thank you,
Karen

[MarvinP]

Hi Karen,

The file didn't attach, but it seems to me you could change the range of the axis values to "blow up" the section they want to see. That would be my first guess. You might also be able to put labels on the points to show numerical values on each line, that might be as good.

[Karen615]

Hi Marvin,

Sorry, it's attached now.
Thank you for the suggestions. I think that adding data labels could make it very busy. I just changed the range as much as I could and it did change it a bit, but I was looking for more detail. Any other suggestions?

Thank you in advance,
Karen

[MrShorty]

I'm not aware of any special tools or such unique to Excel that really help with this kind of chart scenario. You have four very similar data sets that range from 0 to 1 with relatively small differences between the four data at each plotted point.

This might be more for statisticians/mathematician types, but one strategy I have seen for this it to create a "deviation plot" (still a line chart in Excel, so don't be intimidated). The basic idea is to choose one set as a reference data set, calculate the difference between all of the (other) sets relative to the reference set, then plot those differences. In your sample file, this might look like:

1) Choose Q1 as the reference set.
2) Compute the difference between Q2-Q4 and Q1. In C7 =C3-C$2. Copy/paste/fill into C7:H9.
3) Insert a line chart using row 1 as the horizontal category x axis data and rows 7:9 as the y axis data. (I would probably make this a second chart in addition to the existing chart).

Now you have one chart that shows the general trend with time (the current chart) and a second chart that shows how Q2-Q4 changed relative to Q1. The deviation plot values range from -0.06 to +0.06, so it more easily shows the differences between the Q1-Q4.

Would something like that be useful?

[MarvinP]

Hi Karen,

I'm going with MrShorty on my answer. Your data is so close that differences don't show well. I'm not sure what your boss wants.