adding target lines into graphs without adding an extra column of data

[ea223]

Hi,
in the attached sheet i have a results table with a graph. i would like to be able to add dotted lines at 80 and 100 to indicate 'good' and 'excellent' however i don't want to add extra columns of data and i don't want to simply paste lines on the graph because the sheet will not be used by me and so needs to automatically generate these based on the cell values.
is there a way to do this so that the graph looks at what is 'excellent' and what is 'good' and graphs these lines across the whole graph?

thank you!

[coreytroy]

I think you will need to have the good and excellent data on the sheet.

I would suggest using a formula based on the actual data to set the good and excellent. Say Max(B5:B11)+(Max(B5:B11)*0.10) for Good and Max(B5:B11)+(Max(B5:B11)*0.20).

If you do not want the formulated data visible place them on a separate sheet and hide the sheet.

[Andy Pope]

You will need to add extra series if you want the lines to be dynamic.
In the example I have used 2 xy-scatter series.

If you want them to be fully dynamic you will need to include information about the number of categories in the chart.

[ea223]

Hi Andy Pope,
thanks so much, this is exactly what i want but i am not sure how to apply it to other data, would you mind briefly explaining how you did it or pointing me in the direction of some help?
thank you so much!

[Andy Pope]

I added 2 new series via select data dialog.
then selected each in turn and change chart type to xy-scatter.
then went back to the select data dialog and selected the appropriate range and entered data values for the series.
finally format each of the series to have no data marker and coloured dashed line

[ea223]

thank you so much, i am pretty sure i will be able to apply this to new data. thank you!