MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Proportioning a x y chart to prevent image distortion

Posted by ereit on October 13, 2000 10:54 PM

I have created an xy chart that automatically updates with data.

How do I force the chart to maintain a 1:1 scale regardless of the data.

ie. I want the same number of horizontal pixels as vertical pixels per unit on the chart. This is to prevent distortion of the plot image.

Thanks to anyone who can help

Posted by ereit on October 15, 2000 10:14 AM

I can't believe theres no one out there that knows how to solve this!




Posted by JAF on October 16, 2000 2:53 AM

Re: I can't believe theres no one out there that knows how to solve this!

Not sure if this is what you need, but try this:

Right click on the axis, select Format Axis. On the Scale tab, there will be by default a tick in the "Auto" column. If you uncheck this box and type in a value that will be a little higher than the maximum potential value, then the scale will not change when the values change.

Posted by ereit on October 16, 2000 8:02 PM

Re: I can't believe theres no one out there that knows how to solve this!

Thanks for the help, but that wont do it. It still will not guarentee a 1:1 scale.

Any other thoughts?


Posted by Tim Francis-Wright on October 19, 2000 8:19 AM

Re: I can't believe theres no one out there that knows how to solve this!

Couldn't you adopt Aaron Blood's suggestion
to add one additional data point to the
X and Y data?

See (broken link)

This spreadsheet adds a data point set so X
and Y are each equal to the maximum over all
X and Y. In the example, the extra point
has a different data point style (that could be
blank, of course).

I would think that you could set
it to some specified maximum point that was
independent of the data. If X=Y does not
yield a 1-to-1 pixel ratio, then you could set
X = Y *(hpixels/vpixels).

(I'm not sure how to determine the hpixels
and vpixels on the screen either with or
without VBA--perhaps someone else knows.)


Posted by Katrina on October 30, 2000 12:10 PM

This is the code fragment I use for a graph that is 2 units on the x-axis and 1.5 units on the y-axis. If you don't know the number of units on the axes, I think you can use Axes(xlCategory).MinimumScale and its kin to get them.

dWidth = 100 * 2 ' 100 pixels/unit * 2 unit/x-axis
dHeight = 100 * 1.5 ' 100 pixels/unit * 1.5 unit/y-axis

ActiveChart.PlotArea.Width = dWidth
ActiveChart.PlotArea.Height = dHeight

ActiveChart.PlotArea.Width = 2 * dWidth - ActiveChart.Axes(xlCategory).Width
ActiveChart.PlotArea.Height = 2 * dHeight - ActiveChart.Axes(xlValue).Height

Even though the help screens say that PlotArea does not include the axis labels, I think it does where height and width is concerned. That's why I change the width/height twice. If you don't have axis labels then it doesn't matter; just the first set of changes is sufficient. If you do have axis labels, then they stick out and take up some of the width/height you thought the axis was going to get so you have to determine how much space they take up and then add that to the size you want the axis to be.

I have autosizing turned off on the axis fonts. If Excel decides you need a different font size between the two height/width changes, then it messes up the scaling.