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.).
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 screenshot 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.
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
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
and cells D21, E23, and E24 link to cell D20. Cell F20 contains the formula
and cells F21, G23, and G24 link to cell F20.
Next, the Chart
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.
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 of steps lighter than the default fill colors. Also keep in mind that the Baseline series should be made transparent (no fill).
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).
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).
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.
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 of shades lighter than the defaults. Your chart is finished.
You can change where the quadrant borders appear by changing the values in cells B14 and C14.
The chart adjusts as soon as you change these values.
More Combination Chart Articles on the Peltier Tech Blog
- Clustered Column and Line Combination Chart
- Precision Positioning of XY Data Points
- Add a Horizontal Line to an Excel Chart
- Horizontal Line Behind Columns in an Excel Chart
- Bar-Line (XY) Combination Chart in Excel
- Salary Chart: Plot Markers on Floating Bars
- Fill Under or Between Series in an Excel XY Chart
- Fill Under a Plotted Line: The Standard Normal Curve