### Excel Chart Add-Ins | Training | Charts and Tutorials | Peltier Tech Blog

Books at Amazon.com    # Make Gridlines Square.

A common question people ask is "How do I format my chart so its gridlines make a square pattern?" Excel has no setting that forces equally spaced horizontal and vertical gridlines or horizontal and vertical axis ticks, but you can achieve this effect using VBA. Below is a chart before and after making the appropriate scale adjustment.

##### Default Chart with Rectangular Grid ##### Adjusted Chart with Square Grid The easiest approach is to follow this sequence:

1. get the size of the rectangle that is defined by the axes and that contains the plotted points, using
2. ```Chart.PlotArea.InsideHeight
Chart.PlotArea.InsideWidth
```
3. get the axis scaling parameters (min, max, tick spacing):
4. ```With Chart.Axes(xlValue (Y) or xlCategory (X))
AxisMax = .MaximumScale
AxisMin = .MinimumScale
AxisTic = .MajorUnit
End With
```
5. lock the axis scale parameters so they don't readjust themselves:
6. ```With Chart.Axes(xlValue (Y) or xlCategory (X))
.MaximumScaleIsAuto = False
.MinimumScaleIsAuto = False
.MajorUnitIsAuto = False
End With
```
7. determine the physical spacing (pixels) of the axis ticks:
8. ```Xpixels = Chart.PlotArea.InsideWidth * XAxisTic / (XAxisMax - XAxisMin)
Ypixels = Chart.PlotArea.InsideHeight * YAxisTic / (YAxisMax - YAxisMin)
```
9. shrink the larger axis tick spacing by increasing the maximum of that axis:
10. ```If Xpixels > Ypixels Then
.Axes(xlCategory).MaximumScale = Xpixels / Ypixels * (XAxisMax - XAxisMin) + XAxisMin
Else
.Axes(xlValue).MaximumScale = Ypixels / Xpixels * (YAxisMax - YAxisMin) + YAxisMin
End If
```

##### Implementation

The code below represents a regular code module in an Excel VBA project. MakePlotGridSquare is the major piece of the code, and it takes two arguments: myChart is the chart to be squared off, and bEquiTic is an optional boolean argument that indicates whether to make the major tick spacings of the two axes equal. If this argument is False or omitted, the gridline will be made square, but the tick spacings will remain as they were before the code was run.

Two small procedures call the main MakePlotGridSquare procedure:

• MakePlotGridSquareOfActiveChart passes the active chart to the main procedure;
• MakePlotGridSquareOfAllCharts cycles through the charts on the active sheet, passing each in turn to the main procedure.

A final embellishment in the code below is a Do loop. Sometimes when the axis scales are adjusted, something else may change which will throw off the chart's rescaled axes. Usually it is due to the axis maximum being changed to a number with more digits, and word wrapping of the longer tick labels (or other effect) changes the margins of the chart's inside plot area. This loop repeats the rescaling until the vertical and horizontal tick spacings are equal within 1%.

Copy the code below, and paste it into a new module. Make sure the Option Explicit line appears only once.

 ```Option Explicit Sub MakePlotGridSquareOfActiveChart() MakePlotGridSquare ActiveChart End Sub Sub MakePlotGridSquareOfAllCharts() Dim myChartObject As ChartObject For Each myChartObject In ActiveSheet.ChartObjects MakePlotGridSquare myChartObject.Chart Next End Sub Sub MakePlotGridSquare(myChart As Chart, Optional bEquiTic As Boolean = False) Dim plotInHt As Integer, plotInWd As Integer Dim Ymax As Double, Ymin As Double, Ydel As Double Dim Xmax As Double, Xmin As Double, Xdel As Double Dim Ypix As Double, Xpix As Double With myChart ' get plot size With .PlotArea plotInHt = .InsideHeight plotInWd = .InsideWidth End With Do ' Get axis scale parameters and lock scales With .Axes(xlValue) Ymax = .MaximumScale Ymin = .MinimumScale Ydel = .MajorUnit .MaximumScaleIsAuto = False .MinimumScaleIsAuto = False .MajorUnitIsAuto = False End With With .Axes(xlCategory) Xmax = .MaximumScale Xmin = .MinimumScale Xdel = .MajorUnit .MaximumScaleIsAuto = False .MinimumScaleIsAuto = False .MajorUnitIsAuto = False End With If bEquiTic Then ' Set tick spacings to same value Xdel = WorksheetFunction.Max(Xdel, Ydel) Ydel = Xdel .Axes(xlCategory).MajorUnit = Xdel .Axes(xlValue).MajorUnit = Ydel End If ' Pixels per grid Ypix = plotInHt * Ydel / (Ymax - Ymin) Xpix = plotInWd * Xdel / (Xmax - Xmin) ' Keep plot size as is, adjust max scales If Xpix > Ypix Then .Axes(xlCategory).MaximumScale = plotInWd * Xdel / Ypix + Xmin Else .Axes(xlValue).MaximumScale = plotInHt * Ydel / Xpix + Ymin End If ' Repeat if "something" else changed to distort chart axes ' Don't repeat if we're within 1% Loop While Abs(Log(Xpix / Ypix)) > 0.01 End With End Sub``` #### Peltier Technical Services, Inc.

##### Excel Chart Add-Ins | Training | Charts and Tutorials | PTS Blog 