Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Shading areas in a graph
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 ? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Shading areas in a graph
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. -- Miranda "Andrew" wrote: 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 ? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Shading areas in a graph
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 -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Andrew" wrote: 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 ? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Shading areas in a graph
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 Andrew wrote: 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 ? -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I fill different areas in a graph with multiple lines? | Charts and Charting in Excel | |||
Conditional Shading Areas | Charts and Charting in Excel | |||
Powerpoint Graph- Shading in between two linear graph lines | Charts and Charting in Excel | |||
Line Graph and Shading | Charts and Charting in Excel | |||
Graph with shaded/colored Areas?? | Charts and Charting in Excel |