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:
With Chart.Axes(xlValue (Y) or xlCategory (X)) AxisMax = .MaximumScale AxisMin = .MinimumScale AxisTic = .MajorUnit End With
With Chart.Axes(xlValue (Y) or xlCategory (X)) .MaximumScaleIsAuto = False .MinimumScaleIsAuto = False .MajorUnitIsAuto = False End With
Xpixels = Chart.PlotArea.InsideWidth * XAxisTic / (XAxisMax - XAxisMin) Ypixels = Chart.PlotArea.InsideHeight * YAxisTic / (YAxisMax - YAxisMin)
If Xpixels > Ypixels Then .Axes(xlCategory).MaximumScale = Xpixels / Ypixels * (XAxisMax - XAxisMin) + XAxisMin Else .Axes(xlValue).MaximumScale = Ypixels / Xpixels * (YAxisMax - YAxisMin) + YAxisMin End If
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:
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.
Peltier Technical Services, Inc., Copyright © 2017. All rights reserved.