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

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

Changing Axis Scales to Make Square Grid Lines in Excel Graph

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

Changing Axis Scales to Make Square Grid Lines in Excel Graph

After hitting the Run button, the VBA code will run. The code will make your rectangular grid lines square just like the picture below:

Changing Axis Scales to Make Square Grid Lines in Excel Graph

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

Forcing Equal Major Unit Spacing to Make Square Grid Lines in Excel Graph

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

Forcing Equal Major Unit Spacing to Make Square Grid Lines in Excel Graph

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

Changing Plot Area Size to Make Square Grid Lines in Excel Graph

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

Changing Plot Area Size to Make Square Grid Lines in Excel Graph

After hitting the Run button, the VBA code will run. The code will make your rectangular grid lines square just like the picture below:

Changing Plot Area Size to Make Square Grid Lines in Excel Graph


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

Changing Chart Size to Make Square Grid Lines in Excel Graph

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

Changing Chart Size to Make Square Grid Lines in Excel Graph

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.


Related Articles

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo