High-low lines on scatterplot

[Niltiac88]

I have two data series, showing the ranking of a number of factors before and after a vote.

I want to show how the position of each factor changes after the vote. So far, I have put on high-low lines, which links the relevant points clearly. However, I want them to be arrows, always pointing from one series to the other, regardless of whether it moves higher or lower.
At the moment, I can only add arrow heads in one direction (up or down).

Does anyone know a way of adding appropriate arrows?

My table is set out:

[TABLE="width: 30"]
<tbody>[TR]
[TD]Factor[/TD]
[TD]Pre-vote rank position[/TD]
[TD]Post-vote rank position[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]5[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]7[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]10[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

I want the arrows to always point from the pre-vote data set to the post-vote data set, regardless of whether it's an increase or decrease.

Any help very much appreciated.

Caitlin

 

[Worf]

Hello
What Excel version are you using? Anyway, the page below may prove useful; let me know if you need further help.

Connect Two XY Series with Arrows (VBA) | Peltier Tech Blog | Excel Charts

 

[Niltiac88]

Hi,

Thanks very much for the reply.

I'm using Excel 2010.

However, my issue is made a bit more complicated because i'm actually using a line plot with the lines removed rather than a real scatterplot, because the x-axis categories are labels rather than numerical values. Therefore, the joining lines are high-low lines. Because the datasets don't have a numerical x value, i can't use this approach: Connect Two XY Series | Peltier Tech Blog | Excel Charts

If you have any further ideas about how I might do this, please do let me know. Otherwise, I will just have to do some explicit labelling of the figure.

Many thanks.

 

[Worf]

Here is a possible solution:

• Create a scatter plot with numeric values in place of your labels.
• Insert an arbitrary x axis as described here:

Arbitrary Axis Scale

This will allow you to get rid of the temporary numeric values and go back to the original text labels, while still having a XY chart.

• Run the VBA code mentioned at post #2

Completing the steps above will generate a chart like the one below. If you have some trouble with the procedure, feel free to ask.

 

[Jon Peltier]

I know it's an old post, but someone may find this solution useful.

Here's some simple data, alpha and beta as before and after, plus a column labeled "change", which is beta (after) minus alpha (before).

Plot the data as a line chart, and format to hide the lines (top left chart). Select series alpha and add error bars (top right chart). Format the error bar values to use custom values, click Specify Value, then select the change values for Positive Error Value and change the ={1} to ={0} for Negative Error Value (dialog image bottom left, bottom left chart). Finally, format the error bars to use no end cap, then format its line to have an end arrow of the desired type and size.

Coincidentally, I just published a new blog post, Connect Two XY Series with Arrows, which has a non-VBA approach to connecting plotted points with arrows, and updated code if you still need that. It includes detailed instructions for formatting line segments with arrowheads.