I have a bar chart showing monthly activity, which let's say is generally somewhere between 40% and 120%. The target is 90%. I have created a column barchart, with an activity series (Y1) and a dummy series (Y2) where every month the value for Y2 is 90%. Then I have put the 90% onto a second y axis as a line graph so that I have a flat horizontal line across the graph left to right and can see which months the activity exceeds the target. I've adjusted both the y-axis scales to start and finish at the same value.
The problem will come if one month in the future the activity goes a lot higher say 200%. The y1 axis automatically compensates and the scale changes. But I need a way to force the scale for y2 to automatically adjust too - at the moment it just stays the same and makes it look like the red horizontal target line has dropped to something like 50% for every month.
I came across this:
http://peltiertech.com/Excel/Charts/AlignXon2Ys.html
which I suppose might help, but I'm creating a dashboard with loads of graphs and want them to update automatically so I really don't want to have to use macros if at all possible. Is there any other way of achieving what I need, ie forcing y1 and y2 scales to be the same? Many thanks.
The problem will come if one month in the future the activity goes a lot higher say 200%. The y1 axis automatically compensates and the scale changes. But I need a way to force the scale for y2 to automatically adjust too - at the moment it just stays the same and makes it look like the red horizontal target line has dropped to something like 50% for every month.
I came across this:
http://peltiertech.com/Excel/Charts/AlignXon2Ys.html
which I suppose might help, but I'm creating a dashboard with loads of graphs and want them to update automatically so I really don't want to have to use macros if at all possible. Is there any other way of achieving what I need, ie forcing y1 and y2 scales to be the same? Many thanks.
