Lining up axes from one plot with another

[ali_excelforum]

In the screenshot below, you can see a blue bordered plot and a purple bordered plot. I would like to line-up their axes so that the parts with the matching colors line up, as indicated with the green and red lines. I realize that the blue bordered plot is a line chart, that uses a value axis, while the purple bordered plot is a bar chart, that uses a category axis, which is the cause of my issue.

AfQxkXH.png

Worksheet:

[Richard Buttrey]

Hi,

It seems to be a scaling question.

I managed to line them up with

1. Change B2 on the Hist Data to

Please Login or Register  to view this content.

2. On the Scattergraph change the vertical axis Minimum and Maximum to 92.2 & 102.2 respectively.

That will line them up. It's not clear to me yet how to generalise this so that the Max & Min can be altered automatically when the data changes.
The task then is to work out the scaling value, in this case of 2.2 to add to the Min & Max values in the Bar chart.

[MrShorty]

Part of the problem that comes out of the "category axis" for the bar chart is that the distance between categories is the same no matter what the numeric value is. So the distance between 99.5 and100 at the top of the axis is the same as the distance between 91.25 and 92.5 at the bottom. Excel does not have a built in way to mimic that kind of non-linear scaling on its chart axes. The hardest part of building these charts might be the spreadsheet part where you figure out how to numerically "linearize" this non-linear scale. The dot plot option suggested below might be the easiest way around this major difficulty.

These are not the kinds of charts I create, so I am not good with the details. However, I do see this possibly working out using a "dot plot" or "vertical category axis" technique (tutorial here http://peltiertech.com/Excel/Charts/Y_CategoryAxis.html link to dot plot tutorial at the end).

If you are ambitious enough to pursue this to the end, I can see a nice, easy to maintain chart, resulting from combining the dot plot technique with a panel chart. This one (http://peltiertech.com/Excel/Charts/StackedCharts.html ) is focused on making it from a column chart, but turning it into a panel bar chart should be straightforward.

I won't immediately go to the effort to make myself an expert on creating these dot plots (Jon has already done that), but I would suggest that you spend some time with the dot plot tutorial. Once you understand how he is using the combination bar+XY scatter combination to create the dot plot, then you should be in a good position to create the left hand plot in a way that will make it line up with the right hand plot. Once that is well understood, you can then decide if you want to extend this into a panel chart where you can combine both the left and right charts into one, single chart.

Let us know where you get stuck, and we'll see if we can help.

[MrShorty]

If you didn't find it on your own, I corrected the link to the vertical category axis: http://peltiertech.com/Excel/Charts/Y_CategoryAxis.html