Chart with two Y Axes

[pbdurham9121]

Hello,

I would like some assistance in creating a chart with two y-axes with the desired end result looking like this: 

Progress thus far:

I've created a combo clustered bar chart with my two data series.

Data series 1 (green):

• Far too large in scale and I must reduce this.
• The purpose of data series 1 is to visualize a comparison. Current year data - prior year = difference (data in series 1).
  • When the difference is positive, the data would show on the right of the Y-axis and be shown in green (see first picture)
  • When the difference is negative, data would show to the left of the y-axis and be shown in red.
  • Quadrants 1 and 2 respectively if we're thinking of a typical coordinate plane.

Honestly, I am at a bit of a loss and would appreciate any assistance.

Thank you,

Pat

[#2 · Accepted solution]

@pbdurham9121 

I worked this out with a smaller data set, but it will work fine for a larger one like yours.

Column B as vertical axis labels. Your data is in columns C and F ("One" and "Two"). Formulas in D3 and E3 are:
=MIN(F3,0)
=MAX(F3,0)

Select the shaded region and insert a bar chart, Chart 1.

Right-click on any set of bars and choose Change Series Chart Type. Don't change chart types, but check the Secondary Axis box for Minus and Plus (Chart 2). Click the Plus icon next to the chart, click the triangle next to Axes, and check the Secondary Vertical Axis box (Chart 3).

Double click the primary vertical axis, and in the task pane, check the Categories in Reverse Order box; repeat for the secondary vertical axis (Chart 4). Double click the primary horizontal axis (top) and change the maximum to 1600 so the data fits in the left half of the chart; double click the secondary horizontal axis (bottom) and change the minimum to -180 so the data fits in the right half of the chart (Chart 5).

Double click the secondary horizontal axis (bottom) and change Vertical Axis Crosses to Automatic (Chart 6). Format the blue bars to have a gap width of 50; format Minus and Plus bars to be red and green, and format one or the other to have a gap width of 50 and an overlap of 100 (Chart 7).

Double click the top horizontal axis and apply a custom number format of 

[<=800]#,##0;;;

Double click the bottom horizontal axis and apply a custom number format of

[>=-60]#,##0;""

(Chart 8)

Format your axes:

Secondary vertical axis labels: None

Vertical axes: Color = gray (darker than default but not too dark) with no tickmarks

Horizontal axes: Color = same gray as vertical axes with tickmarks outside

(Chart 9).

I readjusted the axis scales so the blue bars take up 2/3 of the chart (max = 1200) and the plus and minus bars take up 1/3 (min=-300). This gives priority to the blue bars and also lines up the primary and secondary tickmarks with the primary gridlines. See Chart 10.