Shading areas in a graph

[Andrew]

Difficult to explain but, I have a graph where I am tracking planned against
actual over time. I have a planned line from 0 to 100% against which I am
tracking the actual acheived - that part is easy, however i also have a set
of trigger bands green, yellow & red. Green is a band starting at 0% to 10%
on the left through to 0% to 80% on the right (sort of a triangle shape in
the bottom half of the graph). Yellow is from 10% to 20% on the left through
to 80% to 100% on the right (a band going from the bottom left to top right)
and the red is from 20% to 100% on the left through to 100% on the right
(upside down triangle at the top of the grapgh). The question is can I
coulour the grapgh to show these bands and then plot the planned vs actual on
top of them ?

 

[Miranda]

I ususally insert a rectangle and then change the color and translucency.
There may be a way to color the graphs in 2007, but I haven't had it long
enough to do much with graphs. Inserting the shape makes it easy to adjust
if/when needed by moving or resizing.

 

[Luke M]

The trick is to do a combination plot. You need to add 3 "dummy" series to
your plot, but plot them as an stacked area chart. Something like:

........X.......Y
Grn..0......10
Grn..1......80
Ylw..0......10
Ylw..1......20
Red.0.......80
Red.1........0

You can change the chart type of a particular series by selecting that
series in the chart, and then go to Chart - Chart Type. To account for the
axis labels, you can move the area chart series to secondary x and y axis
(then hide the axis labels and tick marks). If using a legend, single click
on the series in the legend, then press delete to hide.

Jon Peltier gives some excellent examples of using combination charts and
background fills at his site:

http://peltiertech.com/Excel/Charts/BackgroundFill.html

 

[Andy Pope]

Hi,

You should be able to create the shading effect by plotting some
additional series as area charts.

Assuming you have the following data layout for 6 data points.

B1:E1 Series labels. Actual, Planned, Yellow, Green
A2:A7 category labels
B2:B7 actual values
C2:C7 planned values

D2: =20%
D7: =100%
D3: =$D$2+(($D$7-$D$2)/(ROWS($D$3:$D$7))*(ROW()-ROW($D$2)))
copy down to D6

E2: =10%
E7: =80%
E3: =$E$2+(($E$7-$E$2)/(ROWS($E$3:$E$7))*(ROW()-ROW($E$2)))
copy down to E6

Create a line chart on the range A1:E7
move Yellow and Green series to secondary axis
Change their chart type to area
format area fill to match series name
format plot area as red. If you want a legend entry for red then add
another area series with the values of 1.
Fix both Y axis to have a maximum of 1

Cheers
Andy