Apr 28 2021 10:24 AM
Apr 28 2021 10:24 AM
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):
Honestly, I am at a bit of a loss and would appreciate any assistance.
May 04 2021 11:12 AM
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:
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
Double click the bottom horizontal axis and apply a custom number format of
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
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.