This article will show how easy it is to create a scatter chart with its plot area divided horizontally and vertically into four regions. The regions are separated by the chart axes, and these axes can be positioned where needed to demarcate the quadrants.
I have written a tutorial showing how to create an Excel Chart With Colored Quadrant Background, which was more complicated, as it used stacked areas and secondary axes to get the colored background. This is much simpler to create and maintain, and serves much the same purpose.
Here is the sample X and Y data, with calculated averages, and the initial XY scatter chart. We will position the axes at the respective averages of the X and Y data, though you can position them wherever it makes sense in your analysis.
We need to reposition the axes of this chart. Double click the horizontal axis, or select the horizontal axis and press Ctrl+1 (numeral one), to open the Format Axis task pane (shown here, Excel 2013) or Format Axis dialog (works much the same in earlier Excel versions). Under Axis Options >Vertical Axis Crosses, select the Axis Value option, and enter the X average into the box, as shown.
The result is shown below left. Repeat for the vertical axis, below right.
Those axis labels are totally in the way, but it’s easy to move them. Format each axis (open the task pane or dialog as above) and under Labels > Label Position, select Low from the dropdown.
Now those labels are along the edges of the chart, where they do more good than harm (below left). You can do a small amount of formatting to make the quadrants stick out a bit more clearly. In the chart below right, I’ve used a lighter shade of gray for the gridlines, and I’ve used a darker color, in fact, the same color as the markers, for the axis line color.
It is easy to use VBA to position the axes and axis labels, using a simple routine like that shown below. This routine positions the labels, then uses the averages calculated in the worksheet to position the axis lines.
Sub AxesAsQuadBoundaries1()
With ActiveChart
With .Axes(xlCategory)
.TickLabelPosition = xlTickLabelPositionLow
.CrossesAt = ActiveSheet.Range("A17").Value2
End With
With .Axes(xlValue)
.TickLabelPosition = xlTickLabelPositionLow
.CrossesAt = ActiveSheet.Range("B17").Value2
End With
End With
End SubThe next routine skips the worksheet calculations, instead taking the averages of the X and Y values plotted in the chart to position the axis lines.
Sub AxesAsQuadBoundaries2()
Dim vData As Variant
With ActiveChart
vData = .SeriesCollection(1).XValues
With .Axes(xlCategory)
.TickLabelPosition = xlTickLabelPositionLow
.CrossesAt = WorksheetFunction.Average(vData)
End With
vData = .SeriesCollection(1).Values
With .Axes(xlValue)
.TickLabelPosition = xlTickLabelPositionLow
.CrossesAt = WorksheetFunction.Average(vData)
End With
End With
End SubYou can even use worksheet events to reposition the chart axes. My X and Y values were calculated using =RANDBETWEEN(2,14), so whenever the worksheet calculates (e.g., by pressing the F9 function key), the values change. I can tap into this calculation event as follows.
- Right click on the worksheet tab, and select View Code from the popup menu. The VB Editor opens with a code module corresponding to the worksheet.
- Select Worksheet from the left hand dropdown at the top of the new code module.
- Select Calculate from the right hand dropdown.
- Enter the code as shown.
Here is the code so you don’t need to type it all yourself. Simply copy and paste into the worksheet’s code module.
Private Sub Worksheet_Calculate()
Dim vData As Variant
With Me.ChartObjects(1).Chart
vData = .SeriesCollection(1).XValues
With .Axes(xlCategory)
.TickLabelPosition = xlTickLabelPositionLow
.CrossesAt = WorksheetFunction.Average(vData)
End With
vData = .SeriesCollection(1).Values
With .Axes(xlValue)
.TickLabelPosition = xlTickLabelPositionLow
.CrossesAt = WorksheetFunction.Average(vData)
End With
End With
End SubDepending on the details of your worksheet model, you could use the Worksheet_Calculate, Worksheet_Change, Worksheet_PivotTableUpdate, or other event procedures to update the chart.
