Banded backgrounds on a scatter chart

[Leeps]

Hello everyone, thanks for reading my thread and welcome to my nightmare :D


So, if you fancy saving my life, here's the issue:


I want to apply a banded background to a scatter chart. It already has some conditional formatting, so I want to apply it to the existing chart, as it is quite complex already.

I have looked at the tutorial by Jon Peltier, and it looks to be exactly what I'm after, the problem is getting it to work! I have got the bands behind the scatter plots, but I cannot ever get them to orient properly, and I really need to use the "switch row/column" toggle due to the way the data falls in, but it will never let me use it (greyed out). this is the tutorial I was trying to use:
Excel Charts With Horizontal Bands - Peltier Tech Blog

I have provided a minimum working example for you to look at if it helps, showing what I am trying to do. The data is audiometric data from hearing tests that I conducted, and I want the banding to show thresholds for categories of hearing loss level. In the MWE I use autoshapes to show the areas, and this has worked up until now, but I've come across two issues with it: 1. The bands move when I change the shape of anything on the template, then need resizing, which is a huge pain. 2. The biggest issue is that I need to export images of the graphs as .pdf, and when these are printed, the transaprancy doesn't render, and the data points are invisible.

MWE:

https://dl.dropboxusercontent.com/u/5366344/AudiometryExample.xlsx


I'm using Excel 2010.


Thanks so much for any help. Please just ask if I can provide any more detail.

 

[sijpie]

Is you Y-axis always the same (I could think this is the case for audiometric results)?

What I have done in the past is create a simple banded image with the bands at the right height, and then set the image as background of the graph area. All you measure points will be properly visible.

I may have time one of these days to download your file( can'tr do it at work), but let me know if the above solution would work

 

[Leeps]

Thanks for your reply sijpie,

The method you suggest is similar to the one I have implemented already, though I didn't knw there was an actual background image function, that would have made my life much simpler. The issue I was having before was that the bands were moving around, and transparency wasn't workign on objects outside of excel but if they were tied in as a background it might be ok. Thanks for the advice, I'll give it a go and report back

Cheers!

 

[sijpie]

Here is an example. I created three coloured bars in a drawing program and made them semi transparent, so I could overlay them onto a photograph to make it more specific to the topic. Then I set the combined image as background for the graph area. Because my graph always shows 0-7% this works fine.