Mrinmoy Roy
There are a few reasons one might want to make square gridlines instead of the more standard rectangular gridlines. First, square gridlines can help to create a more consistent look for a document or design. Additionally, square gridlines can help to make a document or design more symmetrical. The square gridlines can be helpful when trying to line up various elements on a page, as they will all line up perfectly if the gridlines are square. In this article, I will show you how to make square grid lines in an Excel graph using VBA codes.
Download Practice Workbook
You can download the Excel file from the following link and practice along with it.
4 Ways to Make Square Grid Lines in Excel Graph
I have used the following dataset which is a Product Delivery Report. I will use this dataset to generate an Excel Graph.
Using the Product Delivery Report, I’ve generated the following Excel Graph. This graph has the traditional rectangular grid on it. I will use this graph to show you make square grid lines in an Excel graph.
1. Changing Axis Scales to Make Square Grid Lines in Graph
My first approach to making square grid lines is adjusting the axis scales. This is done by locking the graph dimension first. Then scaling up the horizontal axis to match the spacing of the vertical axis. Thus, we can make square grid lines.
Now follow the steps below:
❶ First, press ALT + F11 to open the VBA Editor.
❷ Then go to Insert ➤ Module to create a new module.
❸ After that, insert the following piece of code in the newly opened module.
Sub Changing_Axis_Scales()
If Not ActiveChart Is Nothing Then
ChangingAxisScales ActiveChart
ElseIf TypeName(Selection) = "DrawingObjects" Then
Dim zshp As Shape
For Each zshp In Selection.ShapeRange
If zshp.HasChart Then
ChangingAxisScales zshp.Chart
End If
Next
Else
MsgBox "No Chart selected. Please select at least one.", vbExclamation, "Warning!"
End If
End Sub
Function ChangingAxisScales(zChart As Chart)
With zChart
With .PlotArea
Dim xplotInHt As Double, xplotInWd As Double
xplotInHt = .InsideHeight
xplotInWd = .InsideWidth
End With
With .Axes(xlValue)
Dim Ymax1 As Double, Ymin1 As Double, Ymaj1 As Double
Ymax1 = .MaximumScale
Ymin1 = .MinimumScale
Ymaj1 = .MajorUnit
.MaximumScaleIsAuto = False
.MinimumScaleIsAuto = False
.MajorUnitIsAuto = False
End With
With .Axes(xlCategory)
Dim Xmax1 As Double, Xmin1 As Double, Xmaj1 As Double
Xmax1 = .MaximumScale
Xmin1 = .MinimumScale
Xmaj1 = .MajorUnit
.MaximumScaleIsAuto = False
.MinimumScaleIsAuto = False
.MajorUnitIsAuto = False
End With
Dim Ytic1 As Double, Xtic1 As Double
Ytic1 = xplotInHt * Ymaj1 / (Ymax1 - Ymin1)
Xtic1 = xplotInWd * Xmaj1 / (Xmax1 - Xmin1)
If Xtic1 > Ytic1 Then
.Axes(xlCategory).MaximumScale = xplotInWd * Xmaj1 / Ytic1 + Xmin1
Else
.Axes(xlValue).MaximumScale = xplotInHt * Ymaj1 / Xtic1 + Ymin1
End If
End With
End Function
❹ Now go back to your worksheet and click on your Excel graph to select it.
❺ After that, press ALT + F5 keys to open the Macro window.
❻ Now select the macro Changing_Axis_Scales and hit the Run button.
After hitting the Run button, the VBA code will run. The code will make your rectangular grid lines square just like the picture below:
Read More: How to Make Solid Grid Lines in Excel (2 Easy Ways)
2. Forcing Equal Major Unit Spacing for Square Grid Lines
The second approach is to force equal the spacing between the X & Y axis. This is the most common way of making square grid lines from rectangular grid lines.
Now follow the steps below:
❶ First, press ALT + F11 to open the VBA Editor.
❷ Then go to Insert ➤ Module to create a new module.
❸ After that, insert the following piece of code in the newly opened module.
Sub Forcing_Equal_Major_Unit_Spacing()
If Not ActiveChart Is Nothing Then
ForcingEqualMajorUnitSpacing ActiveChart, True
ElseIf TypeName(Selection) = "DrawingObjects" Then
Dim zshp As Shape
For Each zshp In Selection.ShapeRange
If zshp.HasChart Then
ForcingEqualMajorUnitSpacing zshp.Chart
End If
Next
Else
MsgBox "No Chart selected. Please select at least one.", vbExclamation, "Warning!"
End If
End Sub
Function ForcingEqualMajorUnitSpacing(xChart As Chart, Optional xEqualMajorUnit As Boolean = False)
With xChart
With .PlotArea
Dim plotInHt1 As Double, plotInWd1 As Double
plotInHt1 = .InsideHeight
plotInWd1 = .InsideWidth
End With
With .Axes(xlValue)
Dim Ymax1 As Double, Ymin1 As Double, Ymaj1 As Double
Ymax1 = .MaximumScale
Ymin1 = .MinimumScale
Ymaj1 = .MajorUnit
.MaximumScaleIsAuto = False
.MinimumScaleIsAuto = False
.MajorUnitIsAuto = False
End With
With .Axes(xlCategory)
Dim Xmax1 As Double, Xmin1 As Double, Xmaj1 As Double
Xmax1 = .MaximumScale
Xmin1 = .MinimumScale
Xmaj1 = .MajorUnit
.MaximumScaleIsAuto = False
.MinimumScaleIsAuto = False
.MajorUnitIsAuto = False
End With
If xEqualMajorUnit Then
Xmaj1 = WorksheetFunction.Min(Xmaj1, Ymaj1)
Ymaj1 = Xmaj1
.Axes(xlCategory).MajorUnit = Xmaj1
.Axes(xlValue).MajorUnit = Ymaj1
End If
Dim Ytic1 As Double, Xtic1 As Double
Ytic1 = plotInHt1 * Ymaj1 / (Ymax1 - Ymin1)
Xtic1 = plotInWd1 * Xmaj1 / (Xmax1 - Xmin1)
If Xtic1 > Ytic1 Then
.Axes(xlCategory).MaximumScale = plotInWd1 * Xmaj1 / Ytic1 + Xmin1
Else
.Axes(xlValue).MaximumScale = plotInHt1 * Ymaj1 / Xtic1 + Ymin1
End If
End With
End Function
❹ Now go back to your worksheet and click on your Excel graph to select it.
❺ After that, press ALT + F5 keys to open the Macro window.
❻ Now select the macro Forcing_Equal_Major_Unit_Spacing and hit the Run button.
After hitting the Run button, the VBA code will run. The code will make your rectangular grid lines square just like the picture below:
Read More: How to Adjust Gridlines in Excel Chart (2 Ways)
3. Square Grid Lines by Changing Plot Area Size
In this method, I changed the plot area size to make square grid lines from rectangular grid lines. As the plot area size is adjusted, you will see some empty spaces on both the left and right sides of the graph.
Now follow the steps below:
❶ First, press ALT + F11 to open the VBA Editor.
❷ Then go to Insert ➤ Module to create a new module.
❸ After that, insert the following piece of code in the newly opened module.
Sub Changing_Plot_Area_Size()
If Not ActiveChart Is Nothing Then
ChangingPlotAreaSize ActiveChart, True
ElseIf TypeName(Selection) = "DrawingObjects" Then
Dim zshp As Shape
For Each zshp In Selection.ShapeRange
If zshp.HasChart Then
ChangingPlotAreaSize zshp.Chart
End If
Next
Else
MsgBox "No Chart selected. Please select at least one.", vbExclamation, "Warning!"
End If
End Sub
Function ChangingPlotAreaSize(xChart As Chart, Optional xEqualMajorUnit As Boolean = False)
With xChart
With .PlotArea
Dim plotInHt1 As Double, plotInWd1 As Double
plotInHt1 = .InsideHeight
plotInWd1 = .InsideWidth
End With
With .Axes(xlValue)
Dim Ymax1 As Double, Ymin1 As Double, Ymaj1 As Double
Ymax1 = .MaximumScale
Ymin1 = .MinimumScale
Ymaj1 = .MajorUnit
.MaximumScaleIsAuto = False
.MinimumScaleIsAuto = False
.MajorUnitIsAuto = False
End With
With .Axes(xlCategory)
Dim Xmax1 As Double, Xmin1 As Double, Xmaj1 As Double
Xmax1 = .MaximumScale
Xmin1 = .MinimumScale
Xmaj1 = .MajorUnit
.MaximumScaleIsAuto = False
.MinimumScaleIsAuto = False
.MajorUnitIsAuto = False
End With
If xEqualMajorUnit Then
Xmaj1 = WorksheetFunction.Min(Xmaj1, Ymaj1)
Ymaj1 = Xmaj1
.Axes(xlCategory).MajorUnit = Xmaj1
.Axes(xlValue).MajorUnit = Ymaj1
End If
Dim Ytic1 As Double, Xtic1 As Double
Ytic1 = plotInHt1 * Ymaj1 / (Ymax1 - Ymin1)
Xtic1 = plotInWd1 * Xmaj1 / (Xmax1 - Xmin1)
If Xtic1 < Ytic1 Then
.PlotArea.InsideHeight = .PlotArea.InsideHeight * Xtic1 / Ytic1
.PlotArea.Top = .PlotArea.Top + _
(.ChartArea.Height - .PlotArea.Height - .PlotArea.Top) / 2
Else
.PlotArea.InsideWidth = .PlotArea.InsideWidth * Ytic1 / Xtic1
.PlotArea.Left = .PlotArea.Left + _
(.ChartArea.Width - .PlotArea.Width - .PlotArea.Left) / 2
End If
End With
End Function
❹ Now go back to your worksheet and click on your Excel graph to select it.
❺ After that, press ALT + F5 keys to open the Macro window.
❻ Now select the macro Changing_Plot_Area_Size and hit the Run button.
After hitting the Run button, the VBA code will run. The code will make your rectangular grid lines square just like the picture below:
4. Changing Chart Size to Make Square Grid Lines
In the previous method, I changed the plot area size to make square grid lines. This time, I will change the chart size to make square grid lines. By changing the chart size, there will be no empty spaces on the right and left sides of the chart.
So this approach is the best way to make square grid lines in an Excel Graph.
Now follow the steps below:
❶ First, press ALT + F11 to open the VBA Editor.
❷ Then go to Insert ➤ Module to create a new module.
❸ After that, insert the following piece of code in the newly opened module.
Sub Changing_Chart_Size()
If Not ActiveChart Is Nothing Then
ChangingChartSize ActiveChart, True, True
ElseIf TypeName(Selection) = "DrawingObjects" Then
Dim zshp As Shape
For Each zshp In Selection.ShapeRange
If zshp.HasChart Then
ChangingChartSize zshp.Chart, True, True
End If
Next
Else
MsgBox "No Chart selected. Please select at least one.", vbExclamation, "Warning!"
End If
End Sub
Function ChangingChartSize(xChart As Chart, xShrinkChart As Boolean, _
Optional xEqualMajorUnit As Boolean = False)
With xChart
With .PlotArea
Dim plotInHt1 As Double, plotInWd1 As Double
plotInHt1 = .InsideHeight
plotInWd1 = .InsideWidth
End With
With .Axes(xlValue)
Dim Ymax1 As Double, Ymin1 As Double, Ymaj1 As Double
Ymax1 = .MaximumScale
Ymin1 = .MinimumScale
Ymaj1 = .MajorUnit
.MaximumScaleIsAuto = False
.MinimumScaleIsAuto = False
.MajorUnitIsAuto = False
End With
With .Axes(xlCategory)
Dim Xmax1 As Double, Xmin1 As Double, Xmaj1 As Double
Xmax1 = .MaximumScale
Xmin1 = .MinimumScale
Xmaj1 = .MajorUnit
.MaximumScaleIsAuto = False
.MinimumScaleIsAuto = False
.MajorUnitIsAuto = False
End With
If xEqualMajorUnit Then
Xmaj1 = WorksheetFunction.Min(Xmaj1, Ymaj1)
Ymaj1 = Xmaj1
.Axes(xlCategory).MajorUnit = Xmaj1
.Axes(xlValue).MajorUnit = Ymaj1
End If
Dim Ytic1 As Double, Xtic1 As Double
Ytic1 = plotInHt1 * Ymaj1 / (Ymax1 - Ymin1)
Xtic1 = plotInWd1 * Xmaj1 / (Xmax1 - Xmin1)
If xShrinkChart Then
If Xtic1 < Ytic1 Then
.Parent.Height = .Parent.Height - .PlotArea.InsideHeight * (1 - Xtic1 / Ytic1)
Else
.Parent.Width = .Parent.Width - .PlotArea.InsideWidth * (1 - Ytic1 / Xtic1)
End If
Else
If Xtic1 < Ytic1 Then
.PlotArea.InsideHeight = .PlotArea.InsideHeight * Xtic1 / Ytic1
.PlotArea.Top = .PlotArea.Top + _
(.ChartArea.Height - .PlotArea.Height - .PlotArea.Top) / 2
Else
.PlotArea.InsideWidth = .PlotArea.InsideWidth * Ytic1 / Xtic1
.PlotArea.Left = .PlotArea.Left + _
(.ChartArea.Width - .PlotArea.Width - .PlotArea.Left) / 2
End If
End If
End With
End Function
❹ Now go back to your worksheet and click on your Excel graph to select it.
❺ After that, press ALT + F5 keys to open the Macro window.
❻ Now select the macro Changing_Chart_Size and hit the Run button.
After hitting the Run button, the VBA code will run. The code will make your rectangular grid lines square just like the picture below:
Read More: How to Change Gridlines to Dash in Excel (with Easy Steps)
Things to Remember
- All the 4 codes given in this article can make square grid lines perfectly when the value of both X & Y axes are close to each other.
- If the value of both X & Y axes differs much, then instead of using the MaximumScale & MinimumScale properties, set manual values. Otherwise, the final result will be unusable.
Practice Section
You will get an Excel sheet like the following screenshot, at the end of the provided Excel file where you can practice all the topics discussed in this article.
Conclusion
To sum up, we have discussed 4 ways to make square grid lines in Excel Graph. Please don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.
Will this work with Microsoft office non commercial use 2010. When I try your instructions the layout looks different. I managed to
Thanks for some help if yo have time.
Hello RAY,
Can you please elaborate what problems you are facing? And the problems in getting the output using these methods? I mean, the output scenario needs to be known and analyze properly to make a solution of your problem.
Thanks with Regards,
Towhid
Excel and VBA Content Developer