OK, I think understand what you are after, in effect to size the inner part
of the plot-area to the ratio of the axis lengths - right?
Excel charts do not automatically resize that way and for 99.9% of scenarios
it'd be wrong for them to attempt to do so. Charts resize the other way
round: the plot area, including the axis ticklabels, resizes according to
the available space within the already defined chart-area with space for
titles and legends and a little bit more. The axis scales then resize to the
now defined size of the "inner" plot-area.
Have a go with the following macro, paste the following into a normal
module. Start with (say) a square chart sized to suit and with the axes
scaled as you wish. Select the chart and press Alt-F8
Sub PlotAspect()
Dim bAdjWidth As Boolean
Dim i As Long
Dim aspect As Double, xy As Double
Dim cht As Chart
Dim ax(1 To 2) As Axis
Dim axY As Axis
Dim axX As Axis
Set cht = ActiveChart
If cht Is Nothing Then
MsgBox "Select chart you want to 'aspect' the PlotArea"
Exit Sub
End If
' make the plotarea as big as possible
With cht.PlotArea
.Left = 0
.Top = 0
.Width = cht.ChartArea.Width
.Height = cht.ChartArea.Height
End With
Set axX = cht.Axes(xlCategory, xlPrimary)
Set axY = cht.Axes(xlValue, xlPrimary)
aspect = (axY.MaximumScale - axY.MinimumScale) / _
(axX.MaximumScale - axX.MinimumScale)
xy = axY.Height / axX.Width
xy = axY.Height / axX.Width
xy = xy / aspect
bAdjWidth = xy < 1
With cht.PlotArea
For i = 1 To 4
xy = axY.Height / axX.Width
xy = xy / aspect
If bAdjWidth Then
If xy < 1 Then
.Width = .Width * xy
Else
.Width = .Width * xy
End If
Else
If xy > 1 Then
.Height = .Height / xy
Else
.Height = .Height / xy
End If
End If
Next
End With
' target is xy = 1.0 and aspect axY.Height / axX.Width
MsgBox xy & vbCr & _
aspect & vbCr & _
axY.Height / axX.Width
End Sub
As written the plot will size as large as possible within the chart but in
proportion to the X/Y axes, at least that's the intention!
One more thing, probably better size the ticklabel fonts in the axes to
suit, and uncheck the font autosize options
Regards,
Peter T
Unless I'm missing something, simply select the outer part of the chart
and
resize with the grab handles until the "inner" plot area (by default the
grey area where the data is displayed) looks square. You could if you
prefer resize just the plot area.
Regards,
Peter T
Thanks Peter for the help. However, my question is still not solved.
I can manually change the chart box size, but I would like a way in
Excel that will automatically show the graph scales according to real
size, so that I can judge the shape of the graph when I see it on the
screen. For this square one, I happened to know it should be a square,
but I don't like to watch the axis of x, y and judge by myself that
the graph should be a square rather than shown by Excel as rectangle;
in case I need to work on some other shaped plots and I can have an
immediate feeling on what the shape of the chart is (again, rather
than distorted by the Excel)
