Welcome to the updated Slickdeals redesign beta. Learn more and give us feedback. Or, return to the classic view.

Search in
Forum Thread

Excel - fix plot area dimensions

Dr. J 2,655 October 21, 2011 at 01:59 PM
I am putting together a sales tool in Excel which consists of 3 charts vertically aligned where the x axis are all the same, so you can use a ruler/eye guide to see where the 3 independent parameters lie for a given x value.

The independent data is real, e.g. it has units like lb, kg or C, F etc. I have toggles built in to the worksheet to convert these units. One of the charts has 2 series with 2 y-axis and both axis are "convertible" between units.

When I change the units on either y axis the width of the plot area changes, which messes up my vertical alignment of the x axis.

Is there any way to fix the dimensions of the plot area so it won't change size when I change units?

7 Comments

1

Sign up for a Slickdeals account to remove this ad.

#2
Quote from Dr. J View Post :
I am putting together a sales tool in Excel which consists of 3 charts vertically aligned where the x axis are all the same, so you can use a ruler/eye guide to see where the 3 independent parameters lie for a given x value.

The independent data is real, e.g. it has units like lb, kg or C, F etc. I have toggles built in to the worksheet to convert these units. One of the charts has 2 series with 2 y-axis and both axis are "convertible" between units.

When I change the units on either y axis the width of the plot area changes, which messes up my vertical alignment of the x axis.

Is there any way to fix the dimensions of the plot area so it won't change size when I change units?
not sure if i 100% understand your question without seeing the actual data/charts, but a perhaps simple solution would be:

on your chart, right click on one of the numbers in one of your y-axis

go to "Format Axis"

Under "Axis Options", set your axis ranges to FIXED with your chosen parameters
Reply Helpful Comment? 0 0
L10: Grand Master
2,655 Reputation
Original Poster
#3
Quote from Jcaelum View Post :
not sure if i 100% understand your question without seeing the actual data/charts, but a perhaps simple solution would be:

on your chart, right click on one of the numbers in one of your y-axis

go to "Format Axis"

Under "Axis Options", set your axis ranges to FIXED with your chosen parameters
can't do that. the chart in question is toggling between PSI and BAR, a factor of ~ 14.
Reply Helpful Comment? 0 0
#4
Quote from Dr. J View Post :
can't do that. the chart in question is toggling between PSI and BAR, a factor of ~ 14.
so your y-axis is changing between PSI and BAR, and when it changes your x-axis intervals get bigger or smaller?

Is it because the numbers on the y-axis are getting bigger or smaller?

try this:
make your chart (the whole box) bigger.

then click somewhere IN the graph, but not on any lines. This will highlight the actual plot area.

make the plot area smaller (while keeping the actual chart box the same size)

now, when you change the y-axis numbers your x-axis intervals should not change width.

again, my apologies if i am misunderstanding your issue
Reply Helpful Comment? 0 0
L10: Grand Master
2,655 Reputation
Original Poster
#5
Quote from Jcaelum View Post :
so your y-axis is changing between PSI and BAR, and when it changes your x-axis intervals get bigger or smaller?

Is it because the numbers on the y-axis are getting bigger or smaller?

try this:
make your chart (the whole box) bigger.

then click somewhere IN the graph, but not on any lines. This will highlight the actual plot area.

make the plot area smaller (while keeping the actual chart box the same size)

now, when you change the y-axis numbers your x-axis intervals should not change width.

again, my apologies if i am misunderstanding your issue
Changing the y axis units changes the number of digits required to display ticks. Excel autosizes the x axis to accommodate this change. I did think of your idea as well but excel still resizes the x axis regardless of how big the plot is relative to the frame.
Reply Helpful Comment? 0 0
#6
Quote from Dr. J View Post :
Changing the y axis units changes the number of digits required to display ticks. Excel autosizes the x axis to accommodate this change. I did think of your idea as well but excel still resizes the x axis regardless of how big the plot is relative to the frame.
hmmm thats weird because i tried it in my excel (2007), starting with y-axis numbers that were 3 digits and then changing to be 4 digit numbers. By default excel made the x-axis widths change, but when i changed the plot area size it did not do it anymore. sorry.
Reply Helpful Comment? 0 0
#7
Try this out:

http://peltiertech.com/Excel/Char...Sheet.html

I tried to do it without a macro, but couldn't get excel to do what I want. With the macro on that web page you fix the axis value to a given cell in the worksheet (or formul, or anything else you want to). This way once you change anything in the sheet you can automatically set the x axis of each chart to scale to the exact same values (max/min). Good luck, I haven't tried this out personally, but this is the method I would take.
Reply Helpful Comment? 0 0
L10: Grand Master
2,655 Reputation
Original Poster
#8
I've done cell linking to axis SCALING via VBA before but this is a different issue.
Reply Helpful Comment? 0 0
Page 1 of 1
1
Join the Conversation
Add a Comment
 
Slickdeals Price Tracker
Saving money just got easier.
Start Tracking Today
Copyright 1999 - 2015. Slickdeals, LLC. All Rights Reserved. Copyright / DMCA Notice  •  Privacy Policy  •  Terms of Service  •  Acceptable Use Policy (Rules)