How to shift lines along the x axis in a line graph in Excel

Question

I'm wondering how can I shift the lines plotted, in a line graph, along the x axis, just like in this

I have three different groups having different values for a certain category. When I plot the error bars some of them are overlapping and the graph is hard to read. I guess that simply changing the origin in the x axis for each line would solve the problem, but I can't figure out how to do it.

Any suggestion?

Thank you!

Answer 1

What you are trying to do is called "jitter" in the x-axis direction. This cannot be done with a line chart in Excel. However, it can be done with the free XY Chart Labeler from AppsPro (http://www.appspro.com/Utilities/ChartLabeler.htm), with the use of an X-Y Scatter Plot and a lot of careful adjustments, or by using small multiples instead of plotting the same series on one chart.

With some careful fiddling, you could create an x-y scatter plot with categorical labels.

1. Start by adding x-axis values as numbers (1 through the number of x-axis labels), one series for each group of data that you're plotting.
2. Leave one of the x-series as whole numbers, and add or subtract a small amount to each other x-data series. You should now have, for example, x1, x2 and x3 that look like (1, 2, 3), (1.2, 2.2, 2.3) and (0.8, 1.8, 2.8).
3. Create an x-y scatter plot for one x- and y-series, located "as an object in" an existing worksheet.
4. Copy the other series to the graph using "Paste Special...," using a different column of x data for each y series. From the "Paste Special..." options, check "New Series" and "Categories (x values) in first column" but not "Replace existing categories."
5. Set the chart so that it will not resize with cells (see https://superuser.com/a/126768 on how to do this).
6. Disable the display of axis tick mark labels (right-click on the axis, select "Format Axis..." then "Ticks" and choose "None" under "Axis Labels").

Now, with the XY Chart Labeler add-in, you continue

7. Create another y data series, all the same value so that a line will be plotted where you want your x axis to appear.
8. Copy and paste the all-zero data series and the whole-number x series as above (step 4).
9. Select the resulting plotted line, then from the the Tools menu select "XY Chart Lables"-->"Add Chart Labels...," set LabelPosition to "Below" and select the range of cells containing your categorical labels.
10. Drag the bottom edge of the Plot Area so that the new categorical labels look like axis labels.
11. Select the line that you just labeled, right-click and choose "Format Data Series..." Under "Marker Line" choose "No Line" and under "Marker Style" choose "No Marker."

Without the add-in, you continue from step 6, above:

7. Type your x-axis labels into the cells along the bottom of your chart, or use the offset() function to fill in from your existing column (see http://www.excelforum.com/excel-formulas-and-functions/528363-transposing-data-using-offset-function.html).
8. Adjust the size of cells, orientation of text and vertical and horizontal alignment of text to align the text labels with the axis ticks.
9. Right-click on the chart, select "Format Chart Area...," "Line" and choose "No line."
10. If you want a border around your chart, Adjust the cells around the chart and labels and then use cell borders to box it in.

Voila! You have your line graph with categorical labels jittered in the x- direction.

Probably a better option would be to create small multiples chart—multiple copies of the same graph, where each graph gets a different set of y data. This way, you compare the data side-by-side rather than overlapping. See Chandoo's advice on creating small multiples: http://chandoo.org/wp/2011/11/16/small-multiples-charts-in-excel/

I believe that you could also create this sort of chart in R (http://r-project.org) or PyPlot, if you're willing to step outside of Excel.