Shaded Quadrant Background for Excel XY Scatter Chart

Shaded Background Quadrants

People often ask how to shade the background of an XY Scatter chart. This may help to define certain regions of performance or cost-benefit. The desired end result is something like this:

XY Chart With Colored Quadrant Background

This is a rework of an older article. The protocol has been simplified and the steps reordered to work more reliably in Excel 2013 and other recent Excel versions.

Scatter Plot Data

Here is the simple XY data used in this tutorial. The data scales between 0 and 20 on both axes.

XY Data and Chart

Background Data

These two tables show the data and calculations needed to draw the shaded background areas in the chart.

The first table shows relevant values for the X and Y axis, including the minimum and maximum, as well as where we want the divisions between left and right shaded areas and between upper and lower shaded areas. You don’t need to use such a summary table, but you might find it helpful.

In this example, we want the areas to extend from X=zero (the left edge of the chart) to X=12, and from X=12 to X=20 (the right edge of the chart). Likewise we want them to extend from Y=zero (the bottom of the chart) to Y=9, and from Y=9 to Y=20 (the top of the chart).

The axis parameters were selected arbitrarily here, partly to show that the quadrants don’t have to be of equal width or height. These are static values, but you could put formulas into the range C3:D5 if you wanted the divisions based on the statistics of the data or on any other variable criteria.

Data for Background Colored Areas

The second table above uses formulas that reference the first range:

Formulas for Background Colored Areas

The factor of 1000 is used in the formulas in C8:C11 simply because it will provide the appropriate resolution of the division between shaded areas.

This data will produce this background pattern:

Background Colored Areas of Chart

The need for the “Bottom” area series is discussed at the end of this tutorial. It may be better to wait until indicated in the middle of this protocol before you read it.

Building the Chart

Start constructing the chart by selecting the XY data and inserting an XY Scatter Chart from the Insert tab of the ribbon. The legend has been included to help show all the series in the chart through this protocol.

Initial Chart With XY Data Only

Next, copy the data for the shaded regions, which is in C7:H11 (don’t include column B, which has intermediate values).

Select the chart, then click the little down-pointing triangle on the Paste button of the ribbon’s Home tab, and select Paste Special. You will see a dialog like this, and you should make sure these are the settings before clicking OK. In this case, Excel should figure them out correctly.

Paste Special Dialog for Chart Data

The following Paste Special dialog is for pasting data somewhere in a worksheet. This means that a cell or cells in the worksheet were selected when you clicked on Paste Special. Cancel this dialog, select the chart, and re-invoke Paste Special.

Paste Special Dialog for Worksheet Data

The chart now looks like this. Since the added series have a maximum X value of 1000, the original XY data with its maximum of 20 is pushed to the left edge of the chart. Don’t fret…

Chart With All Data as XY Type

One by one, select each of the newly added series, and assign them to the secondary axis. This is the first setting you can control if you select the series and press Ctrl+1 to format it. It also can be done from the new Change Chart Type dialog in Excel 2013, shown at a later step of this procedure.

Excel has added a new vertical axis along the right edge of the chart.

Background Data Moved to Secondary Axis

Add the secondary horizontal axis. The easiest way in Excel 2013 is to click on the Plus icon next to the chart, click on the right-pointing triangle next to “Axes”, and check the appropriate box.

Using Excel 2013's Plus Icon to Add Secondary Horizontal Axis

You could also use Excel 2013’s Add Chart Element control on the Chart Tools > Design tab.

Using Excel 2013's Add Chart Element Control to Add Secondary Horizontal Axis

In Excel 2007 or 2010, go to the Chart Tools Layout tab, click the Axes down-pointing triangle, then Secondary Horizontal Axis, then select show Left to right Axis.

Using Excel 2007/2010's Chart Tools Layout Controls to Add Secondary Horizontal Axis

Here is the chart with all four axes deployed. Now the original XY data on the primary axis overlaps with the added data on the secondary axes.

Secondary Horizontal Axis Added to Chart

Now we need to change the added series from XY to Stacked Area. This is very easy with Excel 2013’s new Change Chart Type dialog. Right click on any series in the chart and choose Change Series Chart Type… The dialog shows the chart with a list of series in the chart. Beside each series is a dropdown showing its chart type and a checkbox showing its axis group.

We could have changed the axis group of the added series to secondary in this dialog, as long as we did so before selecting a different chart type.

Excel 2013 Change Chart Type Dialog

This panel shows all series in the chart, after changing axis group of the added series, but before changing their chart type to Stacked Area.

Chart Series List Before Changing Types

Click on the chart type dropdown, and you’ll see a collection of chart types. The XY Scatter type is selected, and the cursor is hovering over the Stacked Area icon.

Excel 2013 Chart Type Dropdown

Here is the panel of chart series after changing the added series to Stacked Area.

Chart Series List After Changing Types

If you’re using Excel 2007 or 2010, you have to right click on each series you need to change, one at a time, and select Change Chart Type. You will see this dialog. The XY Scatter type is selected, and the cursor is hovering over Stacked Area.

Excel 2013 Change Chart Type Dialog

If you have to change one series at a time, the intermediate chart will look rather strange. This is the chart after changing one series to Stacked Area in Excel 2010. If you only changed one series on the secondary axis in 2013, it would also look like this.

Chart With One XY Series Changed to Area

But don’t panic: after changing all of the series, the chart will look the same in all three Excel versions.

Here is the chart with all added series changed to Stacked Area.

Chart With All XY Series Changed to Area

The need for the “Bottom” area chart series becomes evident in the chart above, with a little explanation. If you are interested, there is an explanation at the end of this tutorial.

The next step is to format the area series. Here I’ve made the “bottom” series completely transparent, and applied a transparency of 50% to the rest. This tones down the intense colors, so they don’t overshadow the actual data, and also allows the gridlines to show through.

Chart With Partially Transparent Background Areas

Select the secondary (top) horizontal axis and press Ctrl+1. In the formatting dialog or task pane, change the Axis Type to Date Axis. This changes the diagonal lines between adjacent shaded regions into vertical lines.

Secondary Horizontal Axis Converted to Date Scale

Select the secondary (top) horizontal axis, and press Delete. Then select the secondary (right) vertical axis and press Delete.

Both Secondary Axes Deleted From Chart

Finally, rescale both axes as appropriate. For this example, both should have a minimum of 0, a maximum of 20, and a major unit of 5.

Primary Axis Scales Adjusted

Finally, delete the legend, and the chart is finished.

Finished XY Chart With Colored Quadrant Background

Variations

Once you know the approach, it is not hard to expand the grid to 3-by-3. In the table below I’ve lightened the zero values to enhance the data for the shaded areas.

XY Chart with 3-by-3 grid of background shaded regions

In fact, you can use any reasonable (i.e., visually and cognitively useful) number of shaded areas, and you don’t need the same number of rows and columns of colors.

It’s also easy to highlight a target region, especially since you only need the bottom area and the target area.

XY Chart with shaded target region

Why We Need the Bottom Fill Series

Here is the chart right after all of the secondary axis series have been converted to Stacked Area type.

Chart With One XY Series Changed to Area

The “bottom” series seems to be on top of all of the rest of the series, which is pretty strange.

If we delete the “bottom” series, the “lower left” series has disappeared from the lower left region of the chart and now it is seen on top of the chart. Broken!

Chart Without Bottom Series

So what’s going on, anyway?

Let’s start with only the “lower left” series, in the position we want it: the bottom left portion of the filled regions.

Desired Appearance of Lower Left Series

Look what happens when we move the category (horizontal) axis to the top of the chart, where the secondary category axis is found. Instead of filling below the “lower left” data points, the fill is above these points.

Appearance of Lower Left Series With Axis Above Plot Area

Aha! In an area chart, the first series fills between the category axis and the data points. So let’s start with the “bottom” series, with values of zero.

Desired Appearance of Lower Left Series

The “bottom” series starts from the category axis at the top of the chart, explaining why it appeared above the other areas, and extends to the bottom of the chart, or actually, to zero on the value (vertical) axis.

Let’s now add the “lower left” series, nearly transparent so we can see both series.

Bottom Series Gives Zero Baseline to Stack Lower Left Series Upon

The “lower left” values are added to the “bottom” values, so it fills below its data points, in front of the “bottom” area.

Let’s add the other three series:

Detail Showing Bottom Series Behind Other Transparent Areas

They all stack up nicely in front of the “bottom” series. Nice, just what we wanted.

Back to the non-transparent version that brought us here.

Chart With One XY Series Changed to Area

You may now return to the protocol.

 

Peltier Tech Charts for Excel

Comments

  1. Brilliant , Wonderful, Perfect. Thank you!

  2. Nice explanation. That behaviour just reinforces my belief that the software default should have been for the bar or area to extend to the zero line’s position on the Y scale and stop, not for it to extend to the *X scale’s* position on the Y scale.

  3. Drew Georgopulos says:

    Chart voodoo laid bare!
    Thanks so much for this.

  4. I’m so glad I found this tutorial. Upgrading from Excel 2003 to Excel 2010 at work has been quite traumatic! This was a really nice and easy tutorial to follow. Plus the trick of copying data and using paste special to add it to a chart is a little gem. Thank you so much for taking the time to write this.

  5. Thank you for this tutorial. It has helped a lot. I am using one of your chart GetXYonAnyChart.xls to create custom message when a data point is clicked. It works find on my Excel 2010 but another colleague has Excel 2007 and it doesn’t work there. Nothing happens when I click on data point. No events are getting fired. I tried to do multiple setting changes but nothing worked so far. Would really appreciate if you can provide any pointer on this.

    In your example, out of 6 charts on a sheet, the one with scatterline work on Excel 2007 of my colleague. Other five do not work. I changed my chart type to scatterline but that did not work for me.

    Any help will be highly appreciated.

  6. Gavin –
    I haven’t looked at that stuff for years. I’m not surprised it didn’t work in 2007. A lot of chart stuff, including the chart event procedures used in that example, were broken in 2007. I remember pulling out my hair whenever I had to program charts in 2007. I never officially upgraded to 2007 for my own work, I kept working in 2003. When 2010 came out, I did move over, and now I’m mostly using 2013. And 2003 looks awfully dated to me now.

  7. I’m looking for a way to post XY data onto a scatter plot that has unique colored quadrants, as can be seen in the website: http://www.flickr.com/photos/104159538@N06/10071269716/. There are several unusual things about it: One, the axes are not uniform in spacing, and it’s not logarithmic either. Second, I also need to be able to use data where, say I have four data points A, B, C and D, I can choose whether to plot A, C and D or just A and B or even just C by itself. Is pivot table the best option there, and how would I approach it? Thanks so much.

  8. I could not get this to work with Excel Mac 2011. The ‘change axis to date’ step throws up an error “the entry is invalid for the data used by this chart. Please enter a value that falls within the minimum and maximum data values used by this chart.” Could not figure out any dates that would avoid the error and show the boxes.

    The closest I felt to a solution was a discovery that it may be an Office Mac bug.
    http://www.realgeek.com/forums/horizontal-axis-scale-issue-470229.html

  9. So helpful, thank you!

  10. Dear John,
    This tutorial is just perfect ! Thank you so much ! I was searching for this kind of tricks since several weeks. I met a problem during the step “Show secondary horizontal axis” (I am using Excel 2010). When I try to show the secondary horizontal axis, the only options I have are : “None”, “Show default axis”, “Show axis in thousands” etc but any “Show left to right axis”.
    When I try to apply “Show default axis”, the result is only applied to the “Bottom – upper right” data but not for the “XY” series. I could not figure out how to solve this problem. The “XY” marks are still sticked to the left.

    Could you please help me to solve this problem? Thank you in advance.

  11. Dear Jon,

    please find a printscreen of my issue here : http://aspw.free.fr/Quadrant.jpg

  12. Philippe –
    Make sure all five series (bottom, lower left and right, upper left and right) are plotted on the secondary axis. If any are still on the primary, then both axes will scale to accommodate the full 0 to 1000 range of X values, and the markers with their puny X values will be smushed against the left edge of the chart.

  13. Jon,

    Super thanks ! You solved it ! :)

    Best regards,

  14. Thank you so much for this tutorial – it works perfect!

    However, I have a followup question as I tried to do the same in VBA. The key step by setting the second x-axes to “Date axis” doesn’t lead to the same result (I used:
    ActiveChart.Axes(xlCategory, xlSecondary).CategoryType = xlTimeScale ).

    When I check the properties of that axes it is acutally changed to “Date axis”, but the chart still displays diamonds instead of rectangles. Do you have any idea why this happens?

    Any help is much appreciated!
    Best regards,
    Christa

  15. By “diamonds”, do you mean the background areas don’t have vertical edges? You probably need to make sure your data in the coded version is the same as in the manual version, which presumably worked. You need two points with the same X value and different Y values to get a vertical line on a date axis.

    In the old days, Microsoft called the date axis a time axis, even though it could only deal with integers, or whole days, and not fractional numbers, or times. They changed the wording in the dialog, but kept the xlTimeScale constant name in VBA.

  16. Hi Jon,
    thanks a lot for your answer. Yes, by “diamonds” I mean “no vertical edges”.
    The input data for manual and VBA, resp., are the same. But I found a not-so-nice workaround: copy/paste the chart updates it and everything is correct (refresh / calculate / .. wouldn’t do it).
    Thanks a lot again for this tutorial

  17. Funny thing. I was having a similar issue, that a chart with a shaded background like this was not updating properly when modified using VBA in a specific version of Excel, I think 2010. So in the code I tested for Excel version, and if it was the offending version, the code copied the chart and pasted it where the old one was, then deleted the old chart. Months later when revisiting other features of the code, I wondered if I really needed that workaround, so I tried without copying the chart, and it worked fine. I don’t know why it started working, maybe there was some fix included in some update of Microsoft Office.

  18. Hi Jon,

    Thanks for your tutorial, it is great! I have followed it step by step and my chart looks the same as yours but when I change the Secondary Horizontal Axis type to Date Axis the two area series on the left (green and blue) stretch horizontally across the entire chart and the other area series (yellow and purple) disappear. Do you have any idea what the problem might be?

    Many thanks,
    Alice

  19. Alice –
    Are the series all on the appropriate axis?

  20. Jon,

    Great tutorial, I’ve been a fan of your work and knowledge for some years now. I have a question: Why if I extend the limits of the X axis the shading extends and does not stop at 20 and this doesn’t happend to the Y axis, if I extend the limits to 40, the vertical shading will stop at 20 and from 20 to 40 there is white background. For my particular application I would want the x axis to not have shading from 20 to 40

    Thank you very much.

    Sincerely,

    Enrique De Velasco

  21. Jon,

    Thanks a lot for this tutorial, it really worked.
    I just have 1 more inquiry, Is it possible to add tags with a list of names to each of the XY points?

    Once again, thanks
    José

  22. Hello Jon,

    Still very satisfied with your shaded quadrant background tips, very useful. I know this is an Excel oriented tips but I am thinking to migrate to LibreOffice. I am trying to generate shaded quadrant background in Calc but I couldn’t figure it out. Any chance you had experiment this in Calc?

    Thanks in advance for your reply.

  23. Oliver Verderber says:

    Thanks so much!
    @Jose Pla, you probably should try XY Chart Labeler

  24. Hi Jon

    I worked through your tutorial, and it worked perfectly.

    I’m now trying to apply this to an X-Y scatter chart where the X and Y axes start at a -ve minimum through to a +ve maximum. I’m stumped with the minimum as your example starts with a minimum of zero – I’ve tried to figure out what I need to change in the formulae, any guidance would be gratefully received!

    Great work with the blog btw :o)

  25. Clem –

    Did you enter your negative value in D3, instead of 0?

  26. I think I tried that, but knowing me I probably didn’t!

    I’ll let you know how I get on, thx

  27. John,

    Thanks for this tutorial, it was very useful. One question: I have used this method to create shaded areas and although it looks 100% when created, something happens when I save and later reopens the file: the shaded areas go skew! I checked the values at the top and bottom of the area and the horizontal axis values are the same. Have you come across this before?

    Kind regards

    John

  28. John –

    What version of Excel are you using? Excel 2010 sometimes messed this up if none of the service pack updates were applied, and I think 2007 did as well.

  29. Brilliant tutorial – very clear and easy to follow. Saved me a heap of time. Many thanks.

  30. I have a question
    Presently I was just able to add the X and Y values on the graph.

    In my application X and Y are used for on supplier.
    I need to have the supplier name instead of the X&Y value

    Can you help me

  31. Pierre –

    You can change the labels as appropriate using one of these approaches:

    + Select each label (click to select the series of labels, then click again to select the individual label), double click to edit the label’s text, and type the label you want.

    + Select each label (click to select the series of labels, then click again to select the individual label, but without selecting the text of the label or having the cursor in the label), type an equals sign in the formula bar, and click on the cell containing the label you want, then press Enter. The formula bar will show the link, e.g., =WorksheetName!$F$2.

    + Download and install Rob Bovey’s free Chart Labeler from http://appspro.com. This allows you to assign labels from a worksheet range to the points in a chart.

    + If you’re using Excel 2013, you can format the labels to use Values from Cells, and select the range of cells containing your labels.

  32. Hi,
    Brilliant tutorials, I follow it & works without a fault. Million thanks!
    However, Some questions though:
    – what can I do if I want the area to be dynamic? because not all of my data can be plot on the same value range (example: Market A has 10 items which can be plot between 0 to 20, but Market B has 10 items which can only be plot between 100 to 200. putting them on the same plot would not be suitable)
    – what I need to change in the range to include negative axis? example: a Profit & growth plot will have negative & positive together & I need to separate them at 0 axis
    Thank you again
    Thang Tran

  33. Hi!
    Can I ask you what is the different in the calculate tabel, if my chart range are -50:-30 ->+50:+50
    Thank you for your help

  34. Hi Jon, this tutorial is awesome. However when I apply the same concept to my range of data, I get a completely different result. Would you mind helping me figure out where I’m wrong?
    Thanks in advance.
    Gwenn

  35. Hi
    Thanks for your blog, Jon.
    My graph plots scores (X-axis) and Improvement since last tested (Y-axis). It looks fine if I use only +’ve numbers in the Y-range but my range goes starts negative and goes positive. I’ve used the lowest and highest numbers in each range for the Minimum and Maximum and I’ve used the average of each range for the middle point.
    Minimum 84.6 -1.7
    Middle 123.2 0.5
    Maximum 157.6 3.2
    All other formulas are as per your example. Any suggestions?

  36. Chris –

    By bad. The formulas for lower left and lower right should be changed from

    =$D$4

    to

    =$D$4-$D$3

    I’ve updated the screenshot.

  37. Works perfectly.Terrific.
    Thanks

  38. Hi! Thanks for the awesome guide! I used it many times already!

    Is there a way to do this with a Bubble Chart? Where you can actually increase the bubble size based on a third variable?

    Thanks you very much!!

  39. Bubble charts do adjust bubble sizes based on a third variable. However, you cannot combine a bubble chart with another chart type, so no way to get the shaded background on an “official” bubble chart.

    You could try the simpler technique described in Simple XY Quad Chart Using Axes as Quadrant Boundaries.

    Or you could make an XY chart, and use VBA to modify the symbol size.

    Neither of these options is formula-driven, so you need to manually move the axes in the first option, or use VBA for either option.

  40. Amazing – this worked perfectly! However when I save the file and return to it the horizontal axis has reverted to the original scale and it goes back to the other shading.

    How can I save it an ensure it stays the same? I’m using Excel 2010.

  41. Sam –

    From time to time, I’ve had issues with the date axis being unreliable. What versions of Excel and Windows are you using?

  42. Hi Jon, I’m using Excel 2010 on Windows 7. Thanks!

  43. Sam –

    A client has a problem with a quadrant-type chart (mixed XY-Area type) in Excel 2010. This is a chart sheet, not an embedded chart. When the chart is updated, the date axis breaks. Interesting that if the chart is copied, the copy has a working date axis, and the original can be deleted.

    I don’t know if this helps at all.

  44. When I am changing axes one by one. Secondary Axes are not showing.

  45. As always, amazing advice. One question though, when I delink the chart (Excel 2010, using your tips here: the secondary axis changes to a text axis and there is no way to get it back to a functioning date axis, which leaves everything slanted. I even tried changing the x-axis values to actual dates to no avail. I know I could copy and paste as a picture, but with xy charts it’s nice if the user can hover over the point to get the values.

    Any thoughts? I’ve only found one other instance of this issue via Google and no real resolution so any guidance would be appreciated.

  46. Arjit –

    You need to format at least one series so it appears on the secondary axis.

    At this point, Excel usually shows the secondary vertical axis, but not always the secondary horizontal axis. Make sure the axes you want are visible using the Axes controls on the ribbon, or the Axes item in the pop-up when you click on the “+” skittle floating next to the chart in Excel 2013+.

  47. Jake –

    What version of Excel? I recall that charts in Excel 2010 would sometimes not work correctly when the time axis data was delinked from the worksheet.

  48. I’m having two problems:

    1) When I change the secondary horizontal axis to “date”, I completely lose the lower and upper left boxes.

    2) I want the four boxes to meet at one specific point, but it seems like no matter what values I change for the 5 boxes, the layout stays exactly the same with only the secondary axes changing.

    Thanks!

  49. Thanks a lot Jon, This blog has helped me lot.

    Just wanted to understand if there is a way to reduce the width of specific quadrant in the chart.

  50. Sushma –

    With this approach, you can change the width of both quadrants in a column, or both quadrants in a row, by changing the values in cells C4:D4 in my example.

  51. Can you do this with percentages? I have growth as a percentages as my x, and margin as a percentage as my y. Everything seems to work up until I change the secondary (top) horizontal axis’s Axis Type to Date Axis. At this point, I lose my quadrants.

  52. hi,jon,thank you for this tutorial, inspire me a lot. when i try to make this graph, i found that the stacked bar chart can do the trick, and the backgroud data can be simpler. and i am very interested in the way you treat the data.

  53. Collin –

    You can use percentages, but you have to process them like any other data. In the section called “Background Data”, you X value percentages go in cells C3:C5, and they are converted in C8:C11 into values normalized to a full scale of 1000.

  54. John –

    For simple quadrants, for example if the left and right parts are the same width, then yes, stacked columns are easier. But using areas make the approach a little more complicated and a lot more flexible.

  55. jon,the way how to construct the background data for stacked aera chart is charming, and can u share how can i master such skill.

  56. Fiz os passos conforme descrito, mas quando altero o tipo de gráfico para área empilha, o eixo secundário horizontal superior se altera (Fica: 1,2,3,4) e quando realizado a alteração do tipo de eixo para “Eixo de Data” não ocorre nada. As figura continuam do mesmo jeito. Poderiam me ajudar?

    Google Translate:
    I did the steps as described, but when I change the chart type to stacking area, the upper horizontal secondary axis changes (Fits: 1,2,3,4) and when changing the axis type to “Data Axis” Nothing happens. The figures continue the same way. can you help me?

  57. When I make date axis on horizontol, nothing happens?

  58. Diogo –

    Could you share your data with me?

  59. Kirs –

    Could you share your data?

Trackbacks

  1. […] in Shaded Quadrant Background for Excel XY Scatter Chart I showed how to generate a background grid of colored […]

  2. […] document.write(''); I have put together a sample workbook in case anybody wants to see if this error occurs on their system. If you would like to receive a copy of this file, please let me know and I can email it to you. As it turns out, the foundation of this chart comes from Jon's website posting last year: Shaded Quadrant Background for Excel XY Scatter Chart – Peltier Tech Blog […]

Speak Your Mind

*

Peltier Tech Charts for Excel 3.0