Simple XY Quad Chart Using Axes as Quadrant Boundaries

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.

Quad Chart Using Axes as Quadrant Boundaries

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.

Quad Chart by Axes - Data and Chart

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.

Format Axis Task Pane - Axis Crosses At

The result is shown below left. Repeat for the vertical axis, below right.

Quad Chart by Axes - Position Axes

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.

Format Axis Task Pane - Label Position Low

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.

Quad Chart by Axes - Position Labels and Reformat

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 Sub

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

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

Worksheet_Calculate Event to Keep Axes in Position

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 Sub

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

 

Peltier Tech Chart Utility

Excel Chart With Colored Quadrant Background

A popular graphical technique divides the plotting area of a chart into four quadrants, to facilitate such analyses as risk-reward. For example, one axis shows risk and the other shows reward; the quadrants divide risk and reward into low and high regions, and the quadrants show the combinations (low risk-high reward, low risk-low reward, etc.).

Chart with colored quadrant background

This technique is not difficult to follow in Excel. The plotted points are in an XY series, while the colored quadrants are formed by stacked area chart series.

First, The Data

All of the data needed to build a chart with colored quadrants is shown in the screen shot below. The individual values to be plotted are in the blue shaded range, B2:C11. The axis scales I want to use are in the green tinted range, B13:D15. The data needed for the area chart series is in the light orange range, B18:G25.

Data for chart with colored quadrant background

The orange (area chart) data range contains some formulas. The Baseline column is needed to ensure the same behavior in all versions of Excel (2000 through 2010). I want to scale the area chart horizontal scale from 0 to 1000 to provide enough resolution in the chart. Cells B19:B20 contain the value 0, and cells B24:B25 contain the value 1000. Cell B21 contains the formula

=(B14-B13)/(B15-B13)*1000

to scale the vertical boundary to the desired point along the horizontal scale, and cells B22 and B23 link back to B21.

Cells C19:C25 link to cell C13.

Cells D19, D22, E22, E25, F19, F22, G22, and G25 also link to cell C13. Cell D20 contains the formula

=C14-C13

and cells D21, E23, and E24 link to cell D20. Cell F20 contains the formula

=C15-C14

and cells F21, G23, and G24 link to cell F20.

Select the blue range and insert an XY (Scatter) chart, markers only, to start the charting process (below left). Copy the orange range, select the chart, and use Paste Special to add the data to the chart as new series, with data in columns, and check the first column and first row boxes (below right). This may push all of the plotted values to the left of the chart, but I had set fixed maximum and minimum axis parameters.

Creating chart with colored quadrant background

Change the Baseline series chart type to stacked area. This messes up the axes and scrunches the plotted values to the left of the chart (below left). Repeat for the Bottom Left, Bottom Right, Top Left, and Top Right series (below right). You may format the area series whenever you want. Keep in mind that Excel wants to use richly saturated colors, which will overwhelm the plotted points. I’ve used shades that are a couple steps lighter than the default fill colors. Also keep in mind that the Baseline series should be made transparent (no fill).

Creating chart with colored quadrant background

Format the Values series, and move it to the Secondary axis. This provides a secondary vertical axis (below left). On the Chart Tools > Layout tab, click on Axes, and select the default Secondary Horizontal Axis option, which gives us the top of the plotting rectangle (below right).

Creating chart with colored quadrant background

Format the left vertical axis, and under Horizontal Axis Crosses, select Maximum Axis Value (below left). Then format the right vertical axis, and under Horizontal Axis Crosses, select Automatic (below right).

Creating chart with colored quadrant background

Select the right vertical axis, and press Delete (below left). Format the top horizontal axis: right click on the axis and choose Format Axis. On the Axis Options tab, under Axis Type, select Date Axis; for Axis Labels, select None; for Line Color, select No Line (below right); and under Position Axis, choose On Tick Marks. Note: Date Axis is a setting on the Axis Options tab; do not switch to the Number tab and choose a Date number format.

Creating chart with colored quadrant background

Resize the plot area if necessary (you’ll have to select Plot Area from the selection dropdown at the top left of the Chart Tools > Layout or Format tabs) and adjust any other formatting. Remove the unneeded Baseline legend entry by clicking once to select the legend and again on the “Baseline” label to select the legend entry, then press Delete. Don’t forget that Excel wants to use richly saturated colors for the area fills, which will overwhelm your value markers. Used colors that are a couple shades lighter than the defaults. Your chart is finished.

Chart with colored quadrant background

You can change where the quadrant borders appear by changing the values in cells B14 and C14.

Chart with colored quadrant background

The chart adjusts as soon as you change these values.

Chart with colored quadrant background

 

Peltier Tech Chart Utility

Peltier Tech Charts for Excel 3.0

 

Create Excel dashboards quickly with Plug-N-Play reports.