Excel 2010 Column Chart Widths Using 2nd Y Axis

[PWD]

Good afternoon I have been given a workbook that contains a chart of 7 items each for a series of 6 years, 2 of which are in the ranges 1000 - 1600 and the other 5 are in the range 10 - 300. This is how it looks in Sheet1 of the attached workbook.I've tried just putting some of the latter 5 on a 2nd Y Axis but have encountered the problem of the columns ballooning in width and then overlapping the first 2 columns. I've tried making the X Axis 'Text' and then increasing the separation and experimenting with the Gap Width for the "5" but they are still sitting in front of the "2" and just generally moving horizontally. This is on Sheet2.

So, is it possible to format these columns, in one chart, to make them more readable and meaningful?

I'm not sure if the 'add file' process was working so I've pasted it in here as well.

http://files.engineering.com/getfil...4290-bc64-e6c186c4a4da&file=Column_Chart.xlsx

Many thanks,
D€$

 

[SkipVought]

Hi,

I’d use a primary and secondary axes.

There’s a way to shift Column series left and right. So maybe a combination of
1) shifting the primary axis series right and the secondary axis series to the left,
2) placing the 4 series behind the 2 series and
3) making the 4 column series fatter and the 2 column series skinnier.

Also check out John Walkenbach’s Excel Charts book, a great resource.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

Here's the panel chart solution for similar data structure:

https://peltiertech.com/broken-y-axis-in-excel-chart/

combo

 

[SkipVought]

I was replying on my iPad.

Now I'm on a PC.

On the Secondary Axis SERIES, simply changed the Format Data Series > Series Options > Series Overlap -- SET TO -100% (Separated)

See if that might be something that helps with separation of the column bars.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[PWD]

Thanks guys.

My boss has decided that as we're dealing with highly educated medics...........

we should make it as simple as possible and just split these up on the same page

Many thanks,
D€$