Excel 2007 chart query

[DMMGREEN]

Hello,
Hoping someone may be able to help me with a charting query I have. I will be quite specific with the data labels I am using as I think it will help illustrate my issue.
I have several locations and for each location I have 4 years of total yearly water consumption and a related KPI measure.
I want my chart to show by location the yearly water consumption for the 4 years in a bar chart (primary y axis) and the related KPI measure for each location as a line chart (secondary y axis). I want the locations to appear on the graph as quite discrete.
I can't seem to work out how to do this as I have been unable to separately group the KPI information per location and then apply the secondary axis option. When I apply secondary axis and line chart format it joins the KPI information across all the locations.
I have attached a file which shows a sample of the data and the chart generated.
Any assistance would be very appreciated.
Thanks

[Don]

Hi and welcome to the forum!

Are you wanting to combine the F09-F11 data into one line on a line chart or to have three separate lines in that chart?

Also, if you want to avoid some of the confusion of this, one trick you can do is to make multiple charts and layer them one on top of another. Then, just make the charts on top have transparent backgrounds and it will look like one chart. If you do this though, make sure that the scale on the Y-axis is the same for each chart.

[Don]

If you just want to have the CAU values in a line chart on the secondary axis, do this (excel 2007 or later):

• Select the chart.
• Go to the Layout tab.
• Go to the Current Selection box on the left of the ribbon menu, click the drop-down menu, select Series "F09 CAU".
• Click the Format Selection button, which is under the drop-down menu.
• On the Series Options tab, select "Secondary Axis" and hit Close.
• Go to the Design tab and click the Change Chart Type button, located on the far left of the ribbon menu.
• Select a Line Chart type and hit OK.

Repeat these steps for each of the data points.

[DMMGREEN]

Thank you Don.