How to Make Square Grid Lines in Excel Graph (4 Effective Ways)

One might want to make square gridlines instead of the more standard rectangular gridlines for a few reasons. 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.

How to Make Square Grid Lines in Excel Graph: 4 Ways

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 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:

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 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 

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 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 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:

Things to Remember

• All 4 codes given in this article can make square grid lines perfectly when the values of both the 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.

Download Practice Workbook

You can download the Excel file from the following link and practice along with it.

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.

Related Articles

• How to Add Primary Major Horizontal Gridlines in Excel
• How to Add Primary Major Vertical Gridlines in Excel 
• How to Add Primary Minor Vertical Gridlines in Excel
• How to Add Minor Gridlines in Excel 
• How to Add Gridlines to a Graph in Excel
• How to Add Vertical Gridlines to Excel Chart
• How to Adjust Chart Gridlines Spacing in Excel
• How to Remove Gridlines in Excel Graph

<< Go Back To Gridlines in Excel Chart | Excel Chart Elements | Excel Charts | Learn Excel