Excel – Switching Primary and Secondary Y Axis

chartsmicrosoft excelmicrosoft-excel-2007microsoft-excel-2010

I have a dataset that includes the number of High/Medium/Low issues in a project, as well as the percentage of how complete it is. I'm trying to get the % complete to be the primary y axis, and the secondary to be the number of issues. I am using a stacked bar graph for the High/Medium/Low issues.

What I'm trying to achieve is the bar graphs rising to the correct percentages according to the primary y axis, and then having a label to distinguish the number of issues in each bar graph segment (ultimately hiding the secondary y axis).

Sample image Ignore the line graph representing the %, I'd ultimately want to not have a line at all, and just use the stacked bar graphs.

Thanks in advance.

Best Answer

(note: I assumed you table was in A1:E7)

  1. Add new columns with the following formula in F2, populate over and down and format the cells as %:

    =(B2/SUM($B2:$D2)*$E2)

enter image description here

  1. Insert a stacked column chart:

  2. Right-click on the chart and choose "Select Data" from the drop-down menu

  3. Make sure your row labels are on the right-hand side (click on "Switch Row/Column" if need be)

enter image description here

  1. remove the first four columns from the chart

enter image description here

  1. Click "OK". your chart should now look like this:

enter image description here

Related Question