Excel Chart With Colored Quadrant Background
by Jon Peltier
Wednesday, September 14th, 2011
Peltier Technical Services, Inc., Copyright © 2012.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
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 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.
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)*1000to 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-C13and cells D21, E23, and E24 link to cell D20. Cell F20 contains the formula
=C15-C14and 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.
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).
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: 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.
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.
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.
Related Posts:
- Area Chart With Gap
- Fill Below an XY Chart Series (XY-Area Combo Chart)
- Peltier Goes Bar Hopping
- Fill Between XY Chart Series (XY-Area Combo Chart)
- Series Lines: Useful or Chart Junk?
- How to Make Horizontal Bullet Graphs in Excel
Posted: Wednesday, September 14th, 2011 under Combination Charts.
Comments: 35
Comments
Comment from Joe Mako
Time: Wednesday, September 14, 2011, 1:14 pm
Thanks for putting this together Jon,
I recreated this kind of view in Tableau, but instead of a static mid point, I have it based on the median value, along with a select-able legend for filtering the view:
http://public.tableausoftware.com/views/ColoredQuadrant/ColoredQuadrant
Comment from Jon Peltier
Time: Wednesday, September 14, 2011, 1:50 pm
Joe -
Good stuff. I’m jealous of the easy filtering. I could do it with checkboxes etc, but it always takes a couple minutes.
The cells that contain the border values could always contain a formula, so the borders could indicate median or any other calculated value. That’s easy in Excel, so I’m not jealous of that.
Comment from Leonid
Time: Wednesday, September 14, 2011, 6:04 pm
When dynamic nature of a colored background is not required the quick and simple way to achive this is to color in a desired way a contiguous range of cells with proportional rows and colomns count, copying them on a clipboard and formatting Plot Area as Fill->Picture or texture fill->Clipboard.
Comment from laguerriere
Time: Thursday, September 15, 2011, 4:40 am
Hello Jon
thank you again for a cool tutorial. I already know that I could use it for the pain-gain matrix for our consulting presentations. Need to figure out how to add 6 quandrants though and combine it with bubble charts.
I have only one problem: I did everything like you described but the Bot Left is not coloured… It’s white on the chart and pink on the legend. Very weird.
Maybe you could help to find out why? Here is the workbook
http://www.4shared.com/file/u9UYM3pU/PTS-chart-with-colored-quadran.html
Thank you in advance!
Pingback from Colors, Legends, and Labeling | Bootstrapping Life
Time: Thursday, September 15, 2011, 8:08 am
[...] colors and legends are not always a good method for creating a graph. The chart below from this post reminded me of this important point. The colors in the legend have no intuitive meaning and this is [...]
Comment from Eric
Time: Friday, September 16, 2011, 11:47 am
Jon,
I’ve been trying to automate this process (so that the user can provide data and Min/Boundary/Max values). I’ve got it so that everything works through the penultimate step (where you select Date Axis etc and the chart magically goes from being funky pyramids to the quadrants when you do this manually). I’ve checked the settings after I use VBA to set the Date Axis, Axis Labels to None, and Line Color to none and it seems to be the same as when I do the whole process manually and the chart changes to quadrants. Any idea why VBA doesn’t make the magic change from pyramids to quadrants?
I am working on an isolated LAN (w/Excel 2007) so I can’t give you the code, but I tried it at home with Excel 2010 last night and got the same incomplete results. Again, everything looks like the blog post through the very last step.
Any ideas?
Eric
Comment from Eric
Time: Friday, September 16, 2011, 2:02 pm
Jon,
I think that the problem may be associated with the change to Date Axis for axis type. I did this by:
ActiveChart.Axes(xlCategory).CategoryType = xlTimeScale
When this is done, the option button goes to the Date Axis option, but the change in the Format Axis box doesn’t show the Axis Options that you get when you do the change manually (i.e., Min, Max, Major/Minor units, Base unit, etc).
When I recorded the macro for this step, the only thing that was recorded was the above VB step. Did I miss something?
Thanks,
Eric
Comment from Eric
Time: Friday, September 16, 2011, 6:58 pm
@laguerriere
I am having the same problem with Bottom Left in my macro version of this blog entry. Let me know if you figure it out.
Thanks,
Eric
Comment from DaleW
Time: Saturday, September 17, 2011, 10:28 am
Jon,
Using Excel 2003, I got the same result as laguerriere and Eric: the Bot Left quadrant was the background color of the plot area (white by default).
Obviously, this was where I changed the plot area color and declared success. You might have a more elegant solution.
Thank you for this latest combo chart tutorial. (I can understand why Numbers on the iPad doesn’t even try to support combo charts imported from Excel.)
Comment from Jon Peltier
Time: Saturday, September 17, 2011, 3:29 pm
Okay, to all of you who had a blank bottom left quadrant, I apologize. I did this tutorial with that bastard child of Microsoft, Excel 2007.
In Excel 2003 and 2010, an area chart series fills between the category axis and the data. If the category axis is at the bottom, it fills below the data. If the axis is at the top, it fills above the data. So if you’re paying attention, the area under the bottom left series is blank in the last three charts with trapezoids, and the area above the upper trapezoids is no longer blank, but has the fill color of the bottom left series.
Excel 2003: Area chart fills between axis and data.
Excel 2010: Area chart fills between axis and data.
Excel 2007: Area chart fills below data.
The other area series look the same in all versions, because these stack on top of the first series, that is, on top of the data of the first series.
I’ve corrected this protocol by inserting a dummy transparent series with values equal to the minimum of the vertical axis. This series will always reach to the bottom of the chart, as in the >500 half of the charts shown in this comment.
Comment from laguerriere
Time: Monday, September 19, 2011, 5:10 am
Thank you Jon, it works now!
Comment from James
Time: Monday, September 19, 2011, 1:43 pm
Hi Jon,
Thanks for your renewed colored quadrant method.
Building on it, I am trying to replace markers with oval shapes to simulate a bubble chart, but I am now facing the obstacle of the new series index.
As I paste a new series collection, is there a way to “force” the index number in order to attach shapes in a consistent way ?
Thanks a lot for your insight.
Comment from Jon Peltier
Time: Monday, September 19, 2011, 3:43 pm
James -
The last series you add should be
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count)
You can also give each series a unique name and call it using
ActiveChart.SeriesCollection(“UniqueSeriesName”)
Comment from Jon Peltier
Time: Monday, September 19, 2011, 4:15 pm
Eric :
Here’s what the Excel 2010 recorder gave me:
Sub Macro3()
ActiveChart.Axes(xlCategory).Select
ActiveChart.Axes(xlCategory).CategoryType = xlTimeScale
ActiveChart.Axes(xlCategory).MaximumScale = 40
ActiveChart.Axes(xlCategory).MajorUnit = 1
ActiveChart.Axes(xlCategory).MajorUnit = 10
ActiveChart.Axes(xlCategory).BaseUnit = xlDays
ActiveChart.Axes(xlCategory).AxisBetweenCategories = False
End Subwhich can be rewritten:
Sub Macro3()
With ActiveChart.Axes(xlCategory)
.CategoryType = xlTimeScale
.MaximumScale = 40
.MajorUnit = 10
.BaseUnit = xlDays
.AxisBetweenCategories = False
End With
End Sub
Comment from James
Time: Tuesday, September 20, 2011, 1:50 am
Hi Jon,
Thanks for your answer.
You are right … and I already gave a unique name to each of the four series.
My problem is that I can loop thru Series Index integers with ActiveChart.SeriesCollection.Count, but I do not know how to loop thru the Array made of the four UniqueSeriesNames …
Thanks again for your kind assistance.
Comment from James007
Time: Tuesday, September 20, 2011, 10:30 am
Hi Jon,
To solve my problem, I have written some not very elegant code …
Within the SeriesCollection.Count loop, I have inserted another loop on each item of the reference array, in order to have each item tested against each series collection unique name … which, in turn, allows to assign the right oval shape to each series.
Thanks again for your very helpful blog.
Comment from Eric
Time: Tuesday, September 20, 2011, 11:47 am
Jon,
Thanks, that helped a lot. However, I still have a problem if I try to set the quadrant data series through code and not by referencing a range on a worksheet.
The problem seems to me to be that I can’t set some of the values of the quadrant series data to be empty or null. When you paste in values to the chart using Paste Special, you have empty values for some of the cells in Baseline, Bottom Left, etc.
For specifying the values of the quadrant’s series in code, those “blank” values come up as either 0′s or as #N/A (when I use the keyword Empty) and you get the pyramids again. If I use a helper worksheet (which remains hidden to the user) or hide the quadrant series from the user the code version will work and I get the ultimate product. But my goal is to not have the hidden helper sheet or ranges.
For example, here’s the part of the code that puts in the series for the Bottom Left:
‘Add Bottom Left Series
Set ns = ch.SeriesCollection.NewSeries
With ns
.XValues = Array(0, _
0, _
Resolution * (BoundaryX – MinX) / (MaxX – MinX), _
Resolution * (BoundaryX – MinX) / (MaxX – MinX), _
Resolution * (BoundaryX – MinX) / (MaxX – MinX), _
Resolution, _
Resolution)
.Values = Array(0, _
BoundaryY, _
BoundaryY, _
0, _
Empty, _
Empty, _
Empty)
.Name = “Bottom Left”
.ChartType = xlAreaStacked
End With
You can put in 0′s for the Empty… same result = pyramids.
Any ideas on how to set the series values so that the right points get ignored?
Thanks for your help, and thanks for this great blog,
Eric
Comment from Jon Peltier
Time: Tuesday, September 20, 2011, 1:33 pm
Eric -
You should be able to use zeros instead of “empty”.
You could use either of these to enter an array into the chart:
ActiveChart.SeriesCollection(1).Values = Array(1, 3, 42.5, 2, 0, 0)
ActiveChart.SeriesCollection(1).Values = “={1, 3, 42.5, 2, 0, 0}”
As long as you have defined the category type of the correct category axis as xlTimeScale, you should get boxes.
Pingback from Campaign Intensity, Negativity, and Using Color to Represent Quantitative Variables
Time: Tuesday, September 20, 2011, 4:00 pm
[...] posted a similar graph of faked data in a recent blog in which I discussed Jon Peltier’s mapping of non-intuitive colors to qualitative variables. Jon was kind enough to provide thoughtful feedback on my graph and we [...]
Comment from Eric
Time: Tuesday, September 20, 2011, 4:52 pm
Jon,
Nope. If you specify the values in code (as either 0 or Empty) that are empty in the orange block, you get pyramids (trapezoids, actually, I guess). If you specify the values as a range, you get quadrants.
The interesting thing remains the format options for the horizontal axis. I put a break on the code after entering the series values and moving the point values (the blue box data) to the secondary axis. If you specify the values for the quadrant data in code and then try to set the horizontal axis to a date axis, the format box will not change and so not give you options to set min/max etc values for the axis. If you specify a range and try this, it will.
So I guess that I’ll have to maintain the quadrant data either on a helper sheet or somewhere on the data sheet. I was trying to avoid that.
Thanks anyway,
Eric
Comment from Jon Peltier
Time: Tuesday, September 20, 2011, 10:17 pm
Eric -
I ran this in Excel 2007 SP2 (make sure you have SP2 installed), and it made a nice 4-quad chart background.
Sub MakeChart()
Dim cht As Chart
Dim srs As Series
Set cht = ActiveSheet.ChartObjects.Add(25, 25, 350, 300).Chart
cht.ChartArea.ClearContents
Set srs = cht.SeriesCollection.NewSeries
With srs
.Values = Array(0, 50, 50, 0, 0, 0, 0)
.XValues = Array(0, 0, 500, 500, 500, 1000, 1000)
.Name = "Bottom Left"
.ChartType = xlAreaStacked
End With
Set srs = cht.SeriesCollection.NewSeries
With srs
.Values = Array(0, 0, 0, 0, 50, 50, 0)
.XValues = Array(0, 0, 500, 500, 500, 1000, 1000)
.Name = "Bottom Right"
.ChartType = xlAreaStacked
End With
Set srs = cht.SeriesCollection.NewSeries
With srs
.Values = Array(0, 50, 50, 0, 0, 0, 0)
.XValues = Array(0, 0, 500, 500, 500, 1000, 1000)
.Name = "Top Left"
.ChartType = xlAreaStacked
End With
Set srs = cht.SeriesCollection.NewSeries
With srs
.Values = Array(0, 0, 0, 0, 50, 50, 0)
.XValues = Array(0, 0, 500, 500, 500, 1000, 1000)
.Name = "Top Right"
.ChartType = xlAreaStacked
End With
With cht.Axes(xlCategory)
.CategoryType = xlTimeScale
.MinimumScale = 0
.MaximumScale = 1000
.MajorUnit = 200
.MajorUnitScale = xlDays
.BaseUnit = xlDays
.AxisBetweenCategories = False
End With
With cht.Axes(xlValue)
.MinimumScale = 0
.MaximumScale = 100
.MajorUnit = 20
End With
End Sub
Comment from Jon Peltier
Time: Tuesday, September 20, 2011, 10:32 pm
Eric -
This one includes the XY series, and does the axis stuff.
Sub MakeChart2()
Dim cht As Chart
Dim srs As Series
Set cht = ActiveSheet.ChartObjects.Add(25, 25, 350, 300).Chart
cht.ChartArea.ClearContents
Set srs = cht.SeriesCollection.NewSeries
With srs
.Values = Array(0, 0, 0, 0, 0, 0, 0)
.XValues = Array(0, 0, 500, 500, 500, 1000, 1000)
.Name = "Baseline"
.ChartType = xlAreaStacked
End With
Set srs = cht.SeriesCollection.NewSeries
With srs
.Values = Array(0, 50, 50, 0, 0, 0, 0)
.XValues = Array(0, 0, 500, 500, 500, 1000, 1000)
.Name = "Bottom Left"
.ChartType = xlAreaStacked
End With
Set srs = cht.SeriesCollection.NewSeries
With srs
.Values = Array(0, 0, 0, 0, 50, 50, 0)
.XValues = Array(0, 0, 500, 500, 500, 1000, 1000)
.Name = "Bottom Right"
.ChartType = xlAreaStacked
End With
Set srs = cht.SeriesCollection.NewSeries
With srs
.Values = Array(0, 50, 50, 0, 0, 0, 0)
.XValues = Array(0, 0, 500, 500, 500, 1000, 1000)
.Name = "Top Left"
.ChartType = xlAreaStacked
End With
Set srs = cht.SeriesCollection.NewSeries
With srs
.Values = Array(0, 0, 0, 0, 50, 50, 0)
.XValues = Array(0, 0, 500, 500, 500, 1000, 1000)
.Name = "Top Right"
.ChartType = xlAreaStacked
End With
Set srs = cht.SeriesCollection.NewSeries
With srs
.Values = Array(25, 75, 40, 80, 65, 30)
.XValues = Array(35, 20, 60, 45, 85, 75)
.Name = "XY Data"
.ChartType = xlXYScatter
.AxisGroup = xlSecondary
End With
cht.HasAxis(xlCategory, xlSecondary) = True
cht.HasAxis(xlValue, xlSecondary) = True
With cht.Axes(xlValue, xlSecondary)
.Crosses = xlAxisCrossesAutomatic
.MinimumScale = 0
.MaximumScale = 100
.MajorUnit = 20
End With
With cht.Axes(xlCategory, xlPrimary)
.CategoryType = xlTimeScale
.MinimumScale = 0
.MaximumScale = 1000
.MajorUnit = 200
.MajorUnitScale = xlDays
.BaseUnit = xlDays
.AxisBetweenCategories = False
.TickLabelPosition = xlTickLabelPositionNone
.Border.LineStyle = xlNone
End With
With cht.Axes(xlValue, xlPrimary)
.Crosses = xlAxisCrossesMaximum
.MinimumScale = 0
.MaximumScale = 100
.MajorUnit = 20
End With
cht.HasAxis(xlValue, xlSecondary) = False
End Sub
Comment from Eric
Time: Wednesday, September 21, 2011, 10:05 am
Jon,
Just tried the second version on a box with Excel 2007 with SP2 installed. It errors out in the
With cht.Axes(xlCategory, xlPrimary)
section on the
.MajorUnitScale = xlDays
statement with a
Run time error ‘-2147467256 (80004005)’:
Invalid Parameter
Again, when I check the format box of the Horizontal Axis (primary, i.e., top one) I do not see the options for setting min/max/major unit / minor unit values.
So what setting am I missing somewhere in the bowels of Excel? That’s got to be the answer, right?
Thanks for helping,
Eric
Comment from Jon Peltier
Time: Wednesday, September 21, 2011, 10:38 am
Eric -
Yeah, now that I test in 2010, I’m getting that same error. I’m looking into it.
Comment from Jon Peltier
Time: Wednesday, September 21, 2011, 11:20 am
Eric -
No joy. I’ve tried a number of things. I even created the series using dummy worksheet ranges. As soon as the data for any of the series is entered as (or changed to) an array, the date axis breaks. The date axis option is selected, but the scaling parameters are those of a category axis.
This is the first charting thing I’ve encountered that works worse in Excel 2010 than in 2007.
Comment from Alison
Time: Thursday, September 22, 2011, 6:48 pm
Genius! Thanks so much!
Comment from Eric
Time: Thursday, September 22, 2011, 6:51 pm
Thanks for trying, Jon. I am going with the Helper sheet option… Ah well.
Cheers,
Eric
Pingback from “Quadrant” for Data Visualization Platforms « Data Visualization
Time: Monday, September 26, 2011, 10:13 pm
[...] am going to update using method suggested by Jon Peltier: http://peltiertech.com/WordPress/excel-chart-with-colored-quadrant-background/ – Thank you Jon! I hope I will have time before end of 2011 for [...]
Comment from Anonymous
Time: Thursday, November 10, 2011, 2:04 pm
What version of Excel are you using for this? I use Excel 2002 (10.6834.6830) SP3 at work and don’t have the option to upgrade.
Alot of the things you refer to don’t seem to exist in my version of Excel like “Chart Tools > Layout tab” and “stacked area” chart as in “Change the Baseline series chart type to stacked area.” I can’t find these things.
I wish you (and all other experts who write tutorials) would explain everything in the greatest detail possible or include foot notes that would explain things in details.
Pingback from Excel 2003 Rectangle de couleur dans graph type bulle
Time: Monday, November 21, 2011, 12:11 pm
[...] [...]
Comment from chatupon
Time: Tuesday, November 29, 2011, 4:39 am
Your makechart so cool!
But can you show how to show datalabel on mark? (it should show data of third column)
Comment from 3G
Time: Wednesday, November 30, 2011, 2:18 pm
Hey Jon-
Love the technique! I am going to use this as a risk quad chart, and, would like to be able to add data labels (i.e. a “risk Number”) to the 10 points I’m going to plot. How can I add this info?
Thanks for all of the awesome posts!
3G
Comment from Jon Peltier
Time: Wednesday, November 30, 2011, 5:12 pm
GGG -
To label points, check out Rob Bovey’s Chart Labeler, at http://appspro.com.
Comment from CW
Time: Tuesday, February 7, 2012, 3:09 pm
Hi,
This is cool stuff. But what if I only want the green and blue areas shaded – and shaded the same color even. So I basically want the graph divided into 2/3 and 1/3 the last 1/3 is one color – essentially I am drawing attention to the part of a graph that is projection. How do I adapt the above formula?
Thanks!
Cecilia
Comment from Jon Peltier
Time: Tuesday, February 7, 2012, 10:53 pm
CW –
The last two charts in the article show how you can change the dividing parameters in B14:C14 to move the intersection of the colored regions. In your case say you want to shade only the last 1/4 of the chart. Change the value in B14 to 15 (which is 3/4 of 20). Then since you don’t want to shade the left part of the chart, you can delete the Bot Left and Top Left series. Also, since you only need one color in the right part of the chart, delete the Top Right series. To make the remaining area reach the top of the chart, change C14 to 20.
Write a comment
I welcome comments from my readers. If you have an opinion on this post, if you have a question or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion.
If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.
Read the PTS Blog Comment Policy.