Peltier Tech
Chart Add-Ins

Excel Dashboards

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

Chart.PlotArea.InsideHeight
Chart.PlotArea.InsideWidth

2. get the axis scaling parameters (min, max, tick spacing):

With Chart.Axes(xlValue (Y) or xlCategory (X))
    AxisMax = .MaximumScale
    AxisMin = .MinimumScale
    AxisTic = .MajorUnit
End With

3. lock the axis scale parameters so they don't readjust themselves:

With Chart.Axes(xlValue (Y) or xlCategory (X))
    .MaximumScaleIsAuto = False
    .MinimumScaleIsAuto = False
    .MajorUnitIsAuto = False
End With

4. determine the physical spacing (pixels) of the axis ticks:

Xpixels = Chart.PlotArea.InsideWidth * XAxisTic / (XAxisMax - XAxisMin)
Ypixels = Chart.PlotArea.InsideHeight * YAxisTic / (YAxisMax - YAxisMin)

5. shrink the larger axis tick spacing by increasing the maximum of that axis:

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 | Custom Solutions | Charts and Tutorials | PTS Blog

Peltier Technical Services, Inc., Copyright © 2010. All rights reserved.
You may link to this article or portions of it on your site, but copying is prohibited without permission of Peltier Technical Services.

Microsoft Most Valuable Professional

My MVP Profile