XL charting problem

[keith666]

Have a small problem, am trying to create a chart (please look at uploaded sheet) which will show a trendline (tab 1 "data") from the AFR column with the "period" and "year" on the bottom axis in the same style as the example graph i have created in (tab 2 example) on the sheet attached.

I can't seem to get the bottom axis to show the periods 1-13 with the years below, xl seems to be adding them up.
Sorry if this is a bit complex, any help appreciated.
Thanks

[Andy Pope]

If you remove the text from A2 and B2 then select the range A2:C67 the chart will come out as you expect.

With those 2 text in place Excel assumes you have 3 data series.

[keith666]

Thanks again Andy, just the job!!

If it's not too much trouble, how easy is it to insert a "target" line into this at a level of 0.2 dropping to 0.18

[Andy Pope]

Add another series, convert to xy-scatter.
Set the x and y values to 0,0.2 and 1,0.18
move the series to the secondary axis.
add secondary X axis and fixed scale to be 0 to 1. Format no Line, tick marks or labels.
delete secondary Y axis

attach as all but last couple of steps so you can see secondary axes.

[keith666]

Thanks very much Andy!
One of these days i will get the hang of it...

cheers