1

Here is the data that I want to display in a column chart:

enter image description here

The desired outcome is a column chart with 2 Y axis. Set1 and Set2 should be plotted on the Secondary Axis, whilst Set3, Set4 and Set5 should be potted on the Primary Axis.

In order to do this I have created a Column chart after selecting the data, then right clicked on the Set1 and Set2 bars and selected Secondary Axis from the options. The result was a very confusing chart with bars stacked over each other as can be seen in the screenshot below.

enter image description here

Is there a trick to plot the data as described above but without causing the bars to stack over each other?

Improve this question

2 Answers 2

Reset to default
1

The easiest way to accomplish this is to create extra "helper" columns to make room for your data to non-overlap. Since Excel treats Column Chart X-axis as categorical data, you'll want the same number of columns for each axis (in your example). Then you have 0 (or #N/A) values in your helper columns to create visual gaps and your actual values can show through. So add 3 helper columns for your secondary axis with 0 values, and add 2 helper columns to your primary axis with 0 values. You'll end up with a total of 5 overlapped values on your chart, with the 0 values matching the position of the other Y-axis values.

Here's an example:

enter image description here

Improve this answer
1

In your case, it's irrelevant how the primary and secondary bars stack up along the vertical axis, so I suggest making a panel chart, which offsets the primary and secondary data in the chart.

Start by making your clustered column chart (top chart below).

Move the last three series to the secondary axis (middle chart below).

Adjust the primary axis so its minimum is unchanged but its new maximum is twice its old maximum: that is the scale changes from {0 to 6000} to {0 to 12000}. Change the secondary axis so its maximum is unchanged its new minimum is the negative of its old maximum: the scale changes from {0 to 10} to {-10 to 10}. (bottom chart below)

Panel Chart Steps 1, 2, 3

Use a custom number format of [<=6000]0;;; for the primary axis, which shows only values less than or equal to 6000. Use a custom number format of 0;;0; for the secondary axis, which shows only positive and zero values. (top chart below)

Finally stretch out the chart so it's easier to read (bottom chart below).

Panel Chart Steps 4 & 5

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .