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:
Chart.PlotArea.InsideHeight Chart.PlotArea.InsideWidth 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 ImplementationThe 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.Excel Chart Add-Ins | Training | Charts and Tutorials | PTS BlogPeltier Technical Services, Inc., Copyright © 2017. All rights reserved. |