Peltier Tech

Peltier Technical Services - Excel Charts and Programming

Excel Chart With Colored Quadrant Background

by 110 Comments

Wednesday, September 14, 2011 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.
 

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

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.

Here is a view of the formulas in the quadrant data range.

Formulas for chart with colored quadrant background

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.

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 of 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 of 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

More Combination Chart Articles on the Peltier Tech Blog

Posted: Wednesday, September 14th, 2011 under Combination Charts.
Tags: , .
Comments: 110

Peltier Tech Charts for Excel

Microsoft MVP Logo

Reader Interactions

Comments

  2. Jon Peltier says

    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.

  3. Leonid says

    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.

  4. laguerriere says

    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!

  5. Eric says

    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

  6. Eric says

    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

  7. Eric says

    @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

  8. DaleW says

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

  9. Jon Peltier says

    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 2003: Area chart fills between axis and data

    Excel 2010: Area chart fills between axis and data.
    Excel 2010: Area chart fills between axis and data

    Excel 2007: Area chart fills below 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.

  11. James says

    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.

  12. Jon Peltier says

    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”)

  13. Jon Peltier says

    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 Sub

    which can be rewritten:

    Sub Macro3()
  With ActiveChart.Axes(xlCategory)
    .CategoryType = xlTimeScale
    .MaximumScale = 40
    .MajorUnit = 10
    .BaseUnit = xlDays
    .AxisBetweenCategories = False
  End With
End Sub

  14. James says

    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.

  15. James007 says

    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.

  16. Eric says

    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

  17. Jon Peltier says

    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.

  18. Eric says

    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

  19. Jon Peltier says

    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

  20. Jon Peltier says

    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

  21. Eric says

    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

  23. Jon Peltier says

    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.

  26. Anonymous says

    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.

  28. 3G says

    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

  30. CW says

    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

  31. Jon Peltier says

    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.

  32. Mustafa says

    Hi Jon, amazing stuff! Works perfectly and nice brief explanation of the steps to go through!

    I only have 1 question: is there a way to include the min-max values of the axes in a cell? Also right now I’m using percentages 0-100% for both my axes. Min quadrant border is set to 0 and max set to 1. Setting the boundary for the quadrants at for example 0,65 works perfectly, but gets messed up only for the HORIZONTAL axis when I change the minimum and maximum value. Any idea on how to cope with this?

    Thanks in advance!

  33. Jon Peltier says

    Mustafa –

    The values being plotted are on the secondary axis, with whatever X and Y axis scales make sense.

    The area series that provide the shaded quadrants are on the primary axis.

    The primary X axis is date scale, with min=0 and max=1000, and these never change. The cut off between left and right quadrants is scaled between 0 and 1000 based on the min and max of the primary X axis and the value where you want the quadrants to meet. For example, if the plotted points have an axis that goes from 0% to 100% and you want the quadrants to meet at 65%, the formulas should make this transition occur at 650 on the primary X axis.

    The primary Y axis is the only Y axis, since the secondary one was deleted. This ensures that the Y values of the plotted points and of the quadrants are synchronized to one scale.

  34. Mustafa says

    Thanks for your quickly reply Jon! I got it sorted out based on a simple formula like you described:
    =((quadrant border-min. axis value)/((max. axis value-min. axis value)/100))/100

    Only downside is that I have to rescale the actual axis of the graph manually via the format axis menu, but I think I can live with that.

    Mustafa

  37. CW says

    Yeah well eventually it will do it however it loses the option for the “default” axis on the primary and/or secondary axis and there is no way to change the axis except for in date format.

  39. Kim says

    Hi Jon,
    After deleting the right vertical axis, can you reiterate the steps to be performed to form the 4 quadrant? It seems so hard to convert the trapezoids into squares & quadrant. HELP please. Thanks.

  40. Jon Peltier says

    Kim –

    It’s possible to divide the background into more than a two-by-two pattern. This is what the data and resulting chart look like for a 5×5 background. The formulas would work the same as in this example.

    After removing the right hand axis, you have stacked up trapezoids. To convert them to rectangles:

    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.

  41. Kim says

    Thanks Jon.

    However, i’m still having a hard time converting the trapezoid to rectangle. I already followed these steps:

    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.

    Hope I can send the file I’m working now. Thanks.

  42. Kim says

    I still can’t achieve the rectangle form. I must be missing something because after formatting the top horizontal axis, the shape is still trapezoid (only the top horizontal axis was removed).

    Hope you can help me on this. Thanks.

  46. Al Pat says

    Hi Jon,
    I am working on a project that needs 3 x 3 x 3 quadrant chart, I tried everyting you have shown above and was able to create 4 x 4 but am not sure how the formulas will work for 3x3x3 quadrant. My boundries are 3 and 6 on scale of 0 to 9 on both axes. Can you please help me?

  48. Kim says

    Hi Jon,
    Hope you can still help me in achieveing the final chart. I’m stucked up in trapezoid figure and can’t make it appear like quadrants. Hope I can send you my file so can see where I’m coming from?

    Thanks.
    Kim

  49. Dom says

    Jon, you’re a genious!
    However, more explanatory would have been an example not crossing at zero, with different values for the horizontal and vertical axis and no crossing point of (10,10).
    In my chart the min/max values are dynamic, as well as the crossing of the axis. However, as example x ranges from -6% to +2%, y ranges from 0% to 12%, crossing is at (-1%, 5.5%) and thus defines 4 quadrants. I’ll keep on guessing, at least I know now that its possible. Thanks, Dom.

  50. Fred says

    Hi Jon,

    I hope you are still following this thread.

    I am desperately trying to apply this to a chart having to display severalseries as bubble chart, is it only possible? It would exactly match my needs.

    I am building the chart in VBA, but I get plenty of errors. Is it compatible?

    Many thanks already for your help,
    Fred.

  51. Jon Peltier says

    Fred –

    You don’t mention what the error descriptions are, so it’s hard to comment specifically.

    Bubble charts cannot be combined with other chart types. Since you’re using VBA already, you can use the approach here with an XY chart, use circles as your markers, and change the circle size based on a third column of data (much like bubble charts use a third column to control bubble size).

  52. Cam says

    Hi Jon,

    I’m experiencing something similar with the converting to quadrants. I change the axis to “Date” and it just spits out “The entry is invalid for the data used by this chart. Please enter a value that falls within the min and max data values used by this chart”

    Any help would be great!

    Thanks!

  53. Dom says

    Dear Jon

    with some VBA-Chart adjustments those quadrants can be made dynamic depending on the scatter plot values. Increments can be calculated in order to define Interval size and keep (0,0) in axis labelling, Min and Max values should be rounded (floor, ceiling). It’s very important to define the Secondary Vertical Axis crossing as C13.
    Slight transparency of quadrant colors and gridlines (Primary Horizontal and Secondary Vertical Axis) get visible (for those whh like gridlines).
    Here’s my code:

    Sub ChartAdjustments()
    ActiveSheet.ChartObjects(“chartName”).Activate
    ‘Define crossing of Secondary Vertival Axis
    ActiveChart.SetElement (msoElementSecondaryValueAxisShow)
    ActiveChart.Axes(xlValue, xlSecondary).Select
    ActiveChart.Axes(xlValue, xlSecondary).CrossesAt = Range(“SVcross”).Value ‘=C13
    ActiveChart.SetElement (msoElementSecondaryValueAxisNone)
    ‘Define Min, Max and Increment of Primary Vertical Axis
    ActiveChart.Axes(xlValue).Select
    ActiveChart.Axes(xlValue).MinimumScale = Range(“PVmin”).Value ‘=min(C3:C11)
    ActiveChart.Axes(xlValue).MaximumScale = Range(“PVmax”).Value
    ActiveChart.Axes(xlValue).MajorUnit = Range(“PVinc”).Value
    ‘Define Min, Max and Increment of Secondary Horizontal Axis
    ActiveChart.Axes(xlCategory, xlSecondary).Select
    ActiveChart.Axes(xlCategory, xlSecondary).MinimumScale = Range(“SHmin”).Value ‘=min(B3:B11)
    ActiveChart.Axes(xlCategory, xlSecondary).MaximumScale = Range(“SHmax”).Value
    ActiveChart.Axes(xlCategory, xlSecondary).MajorUnit = Range(“SHinc”).Value

    ‘B13 = min(B3:B11) ‘C13 = min(C3:C11)
    ‘B14, C14: dynamic of Range(“B3:B11”), (“C3:C11”)
    ‘B15 = max(B3:B11) ‘C15 = max(C3:C11)
    End Sub

    Best regards and thanks for your great work!
    Dom

  54. Jon Peltier says

    Dom –

    You can do all kinds of things, limited only by (a) your imagination and (b) legibility of the graph. I recently built a chart that had dynamically sized quadrants and dynamically scaled axes, based on the scatter plot data. The client was thrilled.

  55. Fred says

    @Dom

    How do you set the Transaprency property of the series representing the quadrant colors?

    I’m running MS Excel 2003 SP3, and I don’t see this property for the series or series.fill

    Thanks,
    Fred.

  57. Anuja says

    i am using excel 2007 and i am not able to convert the trapezoids into quadrants..please help. after completing the last step of formatting top horizontal axis no changes occur in the chart…please help.
    my data

    VALUE
    9.46 0.45
    9.49 0.98
    9.10 2.85
    11.09 7.47
    11.67 8.06
    12.94 15.61

  60. Craig says

    Thanks so much Jon. I was playing around with a different technique but could not get everything to align correctly. I searched in case someone has solved this already and found you. I used your technique (Excel 2003) and it is perfect. For my purposes I needed the x-axis reversed (0% on the right and 100% on the left as closer to 0% is better and better is further to the right in this graph). I also had to make the y-axis go from -20% to 20%. I adjusted the min-max-boundary section and it all worked.

    Thanks for your help.

  61. Fred says

    Hi Jon,

    I am so sad. Based on your piece of code, I had built a very useful procedure to build from scratch nice charts with 4 quadrants and labelled series with enhanced legends based on data in a sheet. It worked beautifully for my work.
    And then, my organisation migrated to MS Excel 2010, and … it does not work anymore!
    One of the main obstacles being the ‘cht.Axes(xlCategory, xlPrimary).MajorUnitScale = xlDays’ trowing a fatal (run-time) error.
    Any idea on how to fix this issue? I was so proud of my tool and about to promote it to colleagues…

  62. Ivan says

    Hi Jon, Nice work, but I am hoping you can help me with one small issue. I am for my sins using Office Mac 2011 ver 14.1.0 (build 110310), and when I get to the step about changing the top horizontal axis to a date axis, I get the following error message… “The entry is invalid for the data used by this chart”… The funny thing is if I move the dialogue box out of the way I can see that the change has done for the most part what it should, it has created 3 perfect boxes in bottom left, top left and top right, but the bottom right is a triangle across the upper right of the block, i.e. it has only shaded the top half of the block from the top left corner to the bottom right corner… Unfortunately with the error message I can’t do anything as it won’t accept me changing the axis to a date axis and i have to cancel back out of the change… Any ideas?

  63. Jon Peltier says

    Ivan –

    I have only sporadic access to a Mac, so I can’t say much about the issue. But with Mac Excel and Excel 2010 there are apparently a number of issues (judging from other comments here) that I need to look into.

  64. VIO says

    I want to use values in the first column, but percentages ranging between 0% – 25% in the second column. When I try it, the quandrants switch from the four quandrants down to two quandrants. I don’t know how to get it to four quadrants. Can you help me?

  67. Anuja says

    Hi friends,

    Many of us including me were unable to convert trapezoids into quadrants which is the last step for this quadrant chart. But today i was able to do it and my trapezoids were converted to quadrants.How? i believe we need to rectify only one common mistake that is in the last step “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.”
    Under Format top horizontal axis:Axis Options we need to select the check box for change axis type to date axis(common mistake we go to Format Axis:Number(2nd option) and change it to Date). I believe other changes in top horizontal axis are not confusing and are easy to follow.

    i hope this is useful.

    Thanks a lot Jon

  68. Dave says

    Hi Jon – this is awesome! Just one question: Do you know if there’s a way to display labels next to all of the points in this chart? In my case we have a 3rd column in the data range (your blue area) which contains the label text but I can’t figure out a way to display it.

    Also, does anyone have any suggestions on a way to use this method to compare values over time? We will produce this chart every month and would like to be able to see where the points were last month compared to where they are now. Essentially we’d need to be able to overlay 2 charts on top of each other. Sounds like a Photoshop job to me but I thought I’d ask!

  69. Jon Peltier says

    Dave –

    To label points individually, check out Rob Bovey’s Chart Labeler (http://appspro.com)

    How many different points will you be tracking, and do the quadrants change position each month. It there aren’t too many points, and if the quadrants are stable, plot each point as a separate series, with values for each month forming a trail. Keep the first month’s set of all points to mark the beginning of the trails, and maybe add the last month’s set to mark the end.

  70. Dave says

    That’s sorted the labelling issue – thanks.

    The quadrants will be fixed and there aren’t too many points, so I think displaying them each as a series is the way to go. I’m just not too sure how to go about getting a series to display in a scatter graph. What I’d be aiming for is a scatter graph, but instead of a load of single points, it would show a load of pairs of points, each joined by a line. How would I go about that?

    Many thanks!

  72. Dee says

    Jon – amazing and great documentation. If I was able to do it, that’s amazing. The purpose of my quadrant will be to provide data points (X,Y) on two questions of a department on efficiency (throughput) and effectiveness (quality). My “DREAM” quadrant would include the labels of what we are seeking answers to — such as service level, communication, budget, etc.

    Of course, if I apply data labels, it will display the numeric values of the intersected point and not the category it is associated with. I could drop in text boxes, but that doesn’t do me any good when the data changes next quarter/year. Any ideas?
    Dee

  73. Dee says

    Jon, I figured it out. Apply the data labels (that you don’t want). Create a list of corresponding data labels in another column for each X,Y data point. Double click on the data variable and in the formula field enter “=” and click on the cell that you want to be the new label. SO COOL!!!! Thank you, Chandoo.org for helping me out with that!

  77. Jon says

    I was able to figure out why I wasn’t able to get the four quadrants working right. I did run into a small obstacle. I am trying to figure out how to adjust the max, min, and , and B:24 & B:25 to fit my values. My values in B2:B10 are 96, 97, 82, 85 and column C2:C10 130, 160, 103, and 120. I was playing around with it, but I failed to get it to work correctly. I really admire your work and website. It has helped me out tremendously. I enjoy research chandoo and your site for my research. Thanks again!

  79. Johnny says

    Good evening Jon.

    I don´t know what I´m doing wrong but when I do the parto of the top axis to date form and check on Ticks marks I get just two of them, I did everything that you wrote but really I don´t know.

  80. Michelle says

    Hi Jon,

    Thanks for you tutorial. I am facing problems in changing the series to stacked area charts. Every time I do so, excel crashes. Once, it gave me an error message: Not enough system resources to display completely.

    Do you know of any fix for this? I am using Excel 2010.

  81. Jon Peltier says

    I can think of no simple reason for Excel to crash like this. Complicated reasons include problems with the installation.
    You could close Excel and clear out the temp directory, in case there’s some junk left from a previous crash. In fact, clear out the temp directory, reboot the computer, and clear it out again. Then light some incense and restart Excel.

  82. HJ says

    Base C11 C12 C13 C21 C22 C23 C31 C32 C33
    0 0 0 0 0
    0 0 15 78 100
    150 0 15 78 100
    150 0 0 0 0 0 0 0
    150 0 15 78 100
    780 0 15 78 100
    780 0 0 0 0 0 0 0
    780 0 15 78 100
    1000 0 15 78 100
    1000 0 0 0 0

    For a 3 x 3, this is the way I arrange the dummies.
    150 and 780 are just examples.

    If I would like to create a dynamic range for the ‘Value’ as defined in your post,

  84. EM says

    Dear Jon,

    Thanks for this post. I have been trying to create this chart for the last couple of days but I keep getting the error others mentioned. ( .MajorUnitScale = xlDays .BaseUnit = xlDays) . I am using office for Mac but the rest of the macro is working fine. I have tried to do it manually following the steps and I also get an error once I change to date axis type. Do you have a template that I could only alter XY values and the thresholds in case I don’t get the macro running? Thanks !

  85. Ehab says

    Hi Jon,

    Thanks for this amazing tutorial, I did it on Excel 2007 on Windows and it worked, but It did not work on Mac.
    I did it for 3 * 3 , but it gave me a strange shape for the bottom middle and bottom right rectangles.

    Another thing, When I tried to create the graph on Mac , I got an error when I changed the Axis to date

    Any clue about what should I do ?

  87. Paul says

    My values data is made up of three columns, name, score1, score2

    i can get the formatting ok but the plot markers are all bunched up in one corner. i have tried to play around with the values in min, boundary and max but just cannot seem to get it to work.

    any ideas?

  88. Rob says

    There’s a much simpler solution than creating a series:
    1) Create an image that has the colors you want to include in the four quadrants (remember where you save it)
    2) Create your chart
    3) Click on a blank section of your chart to select the plot area
    4) Right click – select “Format plot area”
    5) In the Fill section, select “Picture or Texture fill”
    6) Click File under Insert From
    7) Select the image you created in step 1
    8) Enjoy

  90. Gal says

    So, I know you posted this ages ago but just wanted to say, thank you so much!

    This worked brilliantly!

    Now I just need to work out how to know which point is which in a very long list of points…

  92. Jon Peltier says

    You need to set up 6 areas instead of four, either three wide stacked two high, or two wide stacked three high.
    My comment of 21 February 2012 shows a 5×5 background, which is simply an extension of the technique.

  93. Dale says

    Hi Jon. I am happy to add my voice to the chorus of “thank yous!!” This did the job nicely for me, almost 2 years after your original posting :-)

  94. Pandav says

    Thank you Jon…
    using 5*5 and 2*2 helper data . I have created helper data for my 3*2 region and followed steps and boom..briliant implementation !! thanks a lot

  95. Elena says

    Hi Jon,
    I just wanted to send you a huge THANK YOU!!! I’ve used this site a few times in the last few months to find answers to my Excel issues, but this is the first time I posted to say how much you helped me. Your solutions are always easy to follow and they saved me a lot of time in generating my tedious reports! Thanks :)

  97. Jon Peltier says

    Tyler –

    Do you want four horizontal bands? Modify the data layout of the four area chart series so they all stretch from the beginning to the end of the X axis (1 to 1000) instead of two stopping partway across and the other two starting partway across.

  98. Tyler says

    Jon, that is very helpful. You are correct that I am looking to have four horizonal bands. For example (0-25, 25-50, 50-75, and 75-100). I am still a little confused about which series I should be adjusting. I adjusted the 500 values to 1000 and I am left with 2 horizontal bands from 0-50 and 50-100. Is that what you were referencing? Thanks again.

    0 0 Min
    50 50 Boundary
    100 100 Max

    Baseline Bot Left Bot Right Top Left Top Right
    0 0 0 0
    0 0 50 50
    500 0 50 50
    500 0 0 0 0 0
    500 0 50 50
    1000 0 50 50
    1000 0 0 0

  99. Tyler says

    Thanks for that information, Jon. You are correct. I am looking for four horizonal bands. For example (0-25, 25-50, 50-75, 75-100). When I tried to adjust my series I was left with two horizontal bands (0-50 & 50-100). Can you clarify which data I would need to adjust using your blue, orange, and green ranges your article? Thank you!!

  100. Jon Peltier says

    Tyler –

    You need one series for each colored band (plus zeros for Baseline). You need X values at the beginning and end of the axis, and at any point in between where you have side-by-side bands.

    So you need four colored bands, but no in-between X values. Your data should look like this, the chart below left with a text X-axis, and the chart below right with a date X-axis.

    Data and charts with four colored bands

    Since you have no in-between X values, you don’t need to use a date axis, nor do you need the 0-1000 scale. You can use the simplified data and chart below (with a text X-axis).

    Simplified data and chart with four colored bands

  102. Bob says

    A very useful article as I try to beat a 6 by 6 chart Bullseye Grid into submission for graphing the two indices in Earned Value Management data. Values are centered around 1.0 X and 1.0 Y but the boundaries for the three colours (Green – Ok, Yellow – Caution, Red – Problem) also need to be flexible to shift the colour pattern left to right and up or down.

    A couple of suggestions.

    In the original article, I’d like to see the Values for Chart Series Table (orange table) also shown in the Formula Auditing – Show Formulas format. You do have an explanation of the formulas and cell references but I am trying to get clearer on what goes where.

    In the Comment to Kim, 5 by 5 example:

    I’d like to see how the boundaries table (green table) was setup.

    As in the original article, I’d like to see the 5×5 Chart Series tables shown in the Formula Auditing – Show Formulas format.

    Thanks again for your blog and thanks in advance for any help you can provide.

    Cheers
    Bob

  103. Jon Peltier says

    Bob –

    Good idea, I have added the Show Formulas view of the quadrant data range to the main article.

    I’m always happy that even a 12-year-old article gets a comment.

    For Kim’s example, I built the data by hand. The first column has repeated values at 0, 150, 275, 375, 450, and 500, corresponding to the vertical boundaries between areas. These values would be in my article’s list of values starting in cell B13.

    The shaded regions in the first row (A through E) are all 30 units tall; those in the next row (F-J) are 25 tall; and the third through fifth rows are 20, 15, and 10 tall. The list starting in cell C13 would be the cumulative sums 0, 30, 55, 75, 90, and 100.

    I didn’t bother making this table dynamic with links to the intermediate table, but the formulas would be constructed in a similar way.

Trackbacks

  1. […] 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 […]

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Peltier Tech Charts for Excel