How to create a combined clustered bar chart with multiple lines

Question

I have three years of cost and quantity data for three program categories. I would like to combine them into one chart.

The cost data by program type looks like this:

The quantity data by program type looks like this:

I would like to create a clustered bar chart with the quantity of each program type clustered by year and three cost trend lines as a secondary axis.

I think I am going for a chart that looks like this:

I am not sure how to do this in Excel. I have tried different combination charts but doesn't seem to work. I would be grateful for any advice.

Thanks in advance

Answer 1

First, create a column chart based on the quantity table. I selected the range and used Insert>Charts>2-D Column:

Next, add the Cost data by copying the cost range, then right-click the chart (white-spaces, not a series) and use Paste from the context menu. This will paste the selected data as new series in the chart:

Next, right-click the chart again and select "Change Chart Type". Then select Combo at the bottom of the Change Chart Type dialog. If you expand the size of the dialog a little, you'll see you can select secondary axis for certain series. So, select Secondary Axis for the three cost series:

Now click OK and adjust the other chart elements however you like.