I am trying to create a clustered column graph with an average line running horizontally through it, displaying the average value of the totals of column D. As my spreadsheet shows, I am able to create the date vs totals ok, but I can’t get the average line. In theory the average line (currently 68 as per cell H3), so there should be a horizontal line running through this graph at 68. I think to do this I need to insert a clustered column-line template, but I can’t find this option in the insert-recommended charts-all charts (see screen snapshot). I am using excel 2013 and as far as I know, I am supposed to have the clustered column-line template, but I can’t find it! Am I overlooking something here? Please advise. Many thanks

Attached Images

insert chart display.jpg (136.7 KB, 15 views)

Attached Files

practise sheet.xlsx (16.2 KB, 10 views)

Okay it might be cheating and i am sure there is a proper way of doing it, but....

Attached Images

I cant find my clustered column line template_purfleet.JPG (67.5 KB, 14 views)

Attached Files

practise sheet)_Purfleet.xlsx (17.1 KB, 8 views)

I don't know why it would be cheating, it does seem to work, but I need to create extra columns on the worksheet for average, and if I hide the column the graph won't work. What if i wanted to modify my graph slightly, and show a goal line along with the average. Lets say the goal was 100, how can I have the graph display a horizontal line across it at the totals 100 value, along with my average marker, the horizontal line at about 68? Thanks

I assumme there is an offical way of doing it, but i only know this way and while it works there are some limitations.

so a further cheat...

Put the data on sheet2, base your graph with a target on sheet 2 then just link the bits you want to show on sheet1

see attached

let me know if its what you were after

Attached Files

practise sheet)_Purfleet2.xlsx (18.8 KB, 7 views)

Ok, well the graph you created is precisely what I wanted my graph to look like, but how did you select the cells on 2 different tabs to create this graph? If I select a4 to a15,b4-b15, e4-e15 on tab 1, and also select a1 to a13, b1-b13, and f1-f13 on tab 2, and then try and insert a graph, I am not given the option to create a clustered column and line graph, it only lets me make a clustered column graph,so maybe I am selecting the wrong cell ranges for my graph? Thanks

I think I got it: If I right click on the graph and then click on select data, and then use the add function, I can add a range of cells to a new axis and give it a title. You certainly know your excel Purfleet, I hope you are getting paid for all this free advice.

thas one way, but if you want to cheat...



I just cut your data from sheet1 and pasted in sheet 2!


Glad it helped