Silly Scattergraph Question

[Hannah365]

This is probably a really simple question but I've been away from using excel for a while and just cant figure it out. So apologies

I have a spreadsheet with 3 columns, column 1 is a name, column 2 is a value from last year, column 3 is a value from this year (example table below). I want a scattergraph that has column 2 as the horizontal axis and 3 as the vertical axis and uses column 1 to label the points in the graph.

TIA for any help
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Year 1 result[/TD]
[TD]Year 2's result[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]14.5[/TD]
[TD]2.1[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]12.3[/TD]
[TD]-1.7[/TD]
[/TR]
</tbody>[/TABLE]

 

[RoryA]

Depending on your version of Excel, you may need to plot each row as a separate series, or plot one series and use a tool like the XY labeller add-in to label the points if your version doesn't support using a range for the markers.

 

[thisoldman]

Relatively easy to do in Excel 2013 and later.

Simply select the Year_1 and Year_2 data, and from the ribbon select Insert >> Scatter chart >> Scatter with no lines. Then click on the plus sign icon next to the chart and check the box for "Data Labels".

Click on one of the data labels so that all the labels are selected. Bring up the format sidebar, and choose the far right icon, the one that looks like a bar chart. Under "Label Options", uncheck "Y Value" and check "Value from Cells". A pop-up box then appears where you enter the cell range of the labels. That's it.

It's many more steps to do something similar with earlier versions of Excel. Please tell me you're using a version of Excel newer than Excel 2010.

 

[Hannah365]

It's Excel 2010 that I'm using unfortunately

 

[thisoldman]

Jon Peltier covers the options you have.

For labeling:

https://peltiertech.com/apply-custom-data-labels-to-charted-points/
​

Other types of charts could be considered.

A slope graph:

https://peltiertech.com/slope-graphs-in-excel/
​

A dot plot, or lollipop chart:

https://peltiertech.com/dot-plots-microsoft-excel/
​

Or even an arrow chart:

https://peltiertech.com/arrow-charts-in-excel/
​

 

[RoryA]

You could try this: http://www.appspro.com/Utilities/ChartLabeler.htm

 

[Hannah365]

Thanks for all the replies guys, I couldn't use the Chart Labellers as my work blocked the download, but luckily I've been upgraded and have Excel 2013 now so happy days I've managed to make the scattergraphs

 

[thisoldman]

Glad to hear your problem had a happy solution.