How to make X, Y axes of Excel charts to have same scale on thescreen?

[xhm]

Hi:

I have a set of X and Y data that will make a square. But when I plot
them by XY chart in Excel (2003), it looks like a rectangle, even if I
have made the maximum, minimum, major unit, minor unit exactly the
same for both X, and Y axes, and I can see the X and Y data maximum
almost the same, but the graphy on the screen still look like a
rectangle rather than square on the screen. How I can fix this? (by
the way, if in Matlab, I can use 'axis equal').

p.s., this is not an Excel programming question, but since I can't
find an Excel usage group so I sent the question here.

Thanks a lot for any help in advance.

 

[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

 

[xhm]

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)

 

[Peter T]

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)

 

[Peter T]

Slight change to the previous macro (sometimes need to recalc 'aspect' in
each loop)

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
Set cht = ActiveSheet.ChartObjects(1).Chart

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
aspect = (axY.MaximumScale - axY.MinimumScale) / _
(axX.MaximumScale - axX.MinimumScale)
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

Regards,
Peter T

 

[Peter T]

I should have known Jon Peltier would have something on his site -

http://peltiertech.com/Excel/Charts/SquareGrid.html

Try this and the second macro I posted.

Regards,
Peter T