Can you stratify the chart's plot area with colors in an Excel Chart?

[glennpc]

I want an Excel line chart that has Months on the X axis (Jan, Feb, etc.) and a percentage on the Y axis (interested in really only 60 % to 100%). But what I want to show is color bands on the chart's plot area so that when the line crosses over point 63% up to 74%, you are "average" (green), and from 75%% to 84% you'd be "above average" (yellow), and from 85% to 89%, you'd be "super good" (blue), and from 90%, you'd be "walking on water" (orange). Obviously, these aren't the actual terms.

Is there a way to have Excel show color bands like this on the chart? I would want it to scale so the lines/colors don't move if I make the chart larger or smaller.

Any ideas?

 

[NARAYANK991]

Hi Glenn ,

Is it an embedded chart or a chart on a chart sheet ?

Narayan

 

[glennpc]

We could go either way. Right now it is just floating on the page where the data is.

 

[NARAYANK991]

Hi Glenn ,

Have you tried with an Area chart , using a Gradient fill ? You can have stops at 10 % ( which is when the the chart crosses 90 % ) , 25 % ( between 75 % and 90 % ) ,...

Narayan

 

[glennpc]

This link looks like a potential solution. Just need to figure out how to create multiple bands... What do you think?

http://www.exceldashboardtemplates.com/how-to-create-a-dynamic-banding-on-an-excel-line-chart/

 

[NARAYANK991]

Hi Glenn ,

Sure. It's probably worth trying.

However , I was under the impression that only the area under the line would be colored with different colors.

Narayan

 

[glennpc]

Your Area chart might work, but I haven't done a lot with those. The key would be if we had straight lines and we could dictate exactly where the cutoffs would be-- 63%, 75%, 85% and 90% and 100%. I guess the areas would overlap each other in layers. I will have to mess with that and learn more about area charts.

Thanks Narayan!

 

[Faseeh]

glennpc,

Can you upload a sample file?

 

[Luke M]

Is this what you're looking for?
http://peltiertech.com/WordPress/excel-charts-with-horizontal-bands/

 

[glennpc]

Luke-- Peltier's material is also very good, but his write ups are not as step by step. I used the one from ExcelDashboardTemplates.com, although it only showed one band (I think that author's application was a quality control chart). I adapted it to have more than one band and it worked fine. I uploaded a copy here.

The one thing that bothers me about Excel with things like this is after you get the chart you need, it isn't all that robust-- for example, I have two of the series set as lines, and if I now want to change them to bars, it totally mangles up the work I've done creating the color bands. Since so many people seem to want this type of chart, it seems like Microsoft should include one in Excel-- one that would let you set and adjust the bands independently from the actual data lines on the chart. Maybe in future versions....