Conditional Chartting

I have created my own plot area, and I need to plot a
line chart on it without showing axes, gridlines, or plot
area. My plot area is highlighted red on the upper and
lower rows, then yellow on the following rows (up/down),
while the inner area is highlighted green. My avg values
(data series) is right above the plot area (5 rows green,
2 Yellow, and 2 red - plus 16 columns).
Or my second alternative to plot a column chart that will
show for example: 1.99 - 2.21 = green / 1.70 - 1.99 and
2.21 - 2.50 = yellow, and any value below or over the
last values = Red. Can anybody help?
Thanks.

Does this help?

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Os wrote:

> I have created my own plot area, and I need to plot a
> line chart on it without showing axes, gridlines, or plot
> area. My plot area is highlighted red on the upper and
> lower rows, then yellow on the following rows (up/down),
> while the inner area is highlighted green. My avg values
> (data series) is right above the plot area (5 rows green,
> 2 Yellow, and 2 red - plus 16 columns).
> Or my second alternative to plot a column chart that will
> show for example: 1.99 - 2.21 = green / 1.70 - 1.99 and
> 2.21 - 2.50 = yellow, and any value below or over the
> last values = Red. Can anybody help?
> Thanks.

Thanks Jon for the reply. But is not exactly what i'm
looking for. Like I wrote before, I already set up my
plot area and highlighted it the way I wanted. I only
need to show the curve line on the sheet, without showing
the gridlines, axes, etc. My data series is from only one
row. Any idea?

Thank you.


>-----Original Message-----
>Does this help?
>
>
http://peltiertech.com/Excel/Charts...onalChart1.html
>
>- Jon
>-------
>Jon Peltier, Microsoft Excel MVP
>Peltier Technical Services
>Tutorials and Custom Solutions
>http://PeltierTech.com/
>_______
>
>Os wrote:
>
>> I have created my own plot area, and I need to plot a
>> line chart on it without showing axes, gridlines, or
plot
>> area. My plot area is highlighted red on the upper and
>> lower rows, then yellow on the following rows
(up/down),
>> while the inner area is highlighted green. My avg
values
>> (data series) is right above the plot area (5 rows
green,
>> 2 Yellow, and 2 red - plus 16 columns).
>> Or my second alternative to plot a column chart that
will
>> show for example: 1.99 - 2.21 = green / 1.70 - 1.99
and
>> 2.21 - 2.50 = yellow, and any value below or over the
>> last values = Red. Can anybody help?
>> Thanks.
>
>.
>

Oh, so you need to add the series that makes the line/curve. From the Chart menu,
select Source Data, click on the Series tab, click on Add, then select the values
for this series, add a name, etc.

If you are only providing Y values, Excel is just going to use counting numbers for
X (1, 2, 3, etc.). Maybe this is okay.

When you add the series, it will probably not be the type you want. Select the new
series and on the chart menu, select Chart Type, and choose an appropriate line or
scatter type.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Os wrote:

> Thanks Jon for the reply. But is not exactly what i'm
> looking for. Like I wrote before, I already set up my
> plot area and highlighted it the way I wanted. I only
> need to show the curve line on the sheet, without showing
> the gridlines, axes, etc. My data series is from only one
> row. Any idea?
>
> Thank you.
>
>
>
>>-----Original Message-----
>>Does this help?
>>
>>
>
> http://peltiertech.com/Excel/Charts...onalChart1.html
>
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>Peltier Technical Services
>>Tutorials and Custom Solutions
>>http://PeltierTech.com/
>>_______
>>
>>Os wrote:
>>
>>
>>>I have created my own plot area, and I need to plot a
>>>line chart on it without showing axes, gridlines, or
>
> plot
>
>>>area. My plot area is highlighted red on the upper and
>>>lower rows, then yellow on the following rows
>
> (up/down),
>
>>>while the inner area is highlighted green. My avg
>
> values
>
>>>(data series) is right above the plot area (5 rows
>
> green,
>
>>>2 Yellow, and 2 red - plus 16 columns).
>>>Or my second alternative to plot a column chart that
>
> will
>
>>>show for example: 1.99 - 2.21 = green / 1.70 - 1.99
>
> and
>
>>>2.21 - 2.50 = yellow, and any value below or over the
>>>last values = Red. Can anybody help?
>>>Thanks.
>>
>>.
>>

Thanks Jon. That was my initail try before, but is not
helping my purpose. You see, I have a user enters data
every half an hour, and according to that data/values, we
need the curve line to visually show whether the product
is on the Green (pref), Yellow, or Red. But I don't know
how to highlight individual gridlines with different
color. Because Excel will highlight the whole chart area.
I need the most outer rows (upper + lowest), to always be
Red, the inner rows to always be Yellow, and the most
inner 5 rows to always be Green. I just can't figure it
out. That's why I highlighted 9 rows on the sheet, and
would like the cure line only to show - everything else
should be transparent, but i'm not sure that will work
either, because my rows are just simply rows, without any
scaling. I hope you got the picture by now.
Thanks.


>-----Original Message-----
>Oh, so you need to add the series that makes the
line/curve. From the Chart menu,
>select Source Data, click on the Series tab, click on
Add, then select the values
>for this series, add a name, etc.
>
>If you are only providing Y values, Excel is just going
to use counting numbers for
>X (1, 2, 3, etc.). Maybe this is okay.
>
>When you add the series, it will probably not be the
type you want. Select the new
>series and on the chart menu, select Chart Type, and
choose an appropriate line or
>scatter type.
>
>- Jon
>-------
>Jon Peltier, Microsoft Excel MVP
>Peltier Technical Services
>Tutorials and Custom Solutions
>http://PeltierTech.com/
>_______
>
>Os wrote:
>
>> Thanks Jon for the reply. But is not exactly what i'm
>> looking for. Like I wrote before, I already set up my
>> plot area and highlighted it the way I wanted. I only
>> need to show the curve line on the sheet, without
showing
>> the gridlines, axes, etc. My data series is from only
one
>> row. Any idea?
>>
>> Thank you.
>>
>>
>>
>>>-----Original Message-----
>>>Does this help?
>>>
>>>
>>
>>
http://peltiertech.com/Excel/Charts...onalChart1.html
>>
>>>- Jon
>>>-------
>>>Jon Peltier, Microsoft Excel MVP
>>>Peltier Technical Services
>>>Tutorials and Custom Solutions
>>>http://PeltierTech.com/
>>>_______
>>>
>>>Os wrote:
>>>
>>>
>>>>I have created my own plot area, and I need to plot a
>>>>line chart on it without showing axes, gridlines, or
>>
>> plot
>>
>>>>area. My plot area is highlighted red on the upper
and
>>>>lower rows, then yellow on the following rows
>>
>> (up/down),
>>
>>>>while the inner area is highlighted green. My avg
>>
>> values
>>
>>>>(data series) is right above the plot area (5 rows
>>
>> green,
>>
>>>>2 Yellow, and 2 red - plus 16 columns).
>>>>Or my second alternative to plot a column chart that
>>
>> will
>>
>>>>show for example: 1.99 - 2.21 = green / 1.70 - 1.99
>>
>> and
>>
>>>>2.21 - 2.50 = yellow, and any value below or over the
>>>>last values = Red. Can anybody help?
>>>>Thanks.
>>>
>>>.
>>>
>
>.
>

If you want to show colored gridlines in the chart, you can use this technique, and
add one series for each colored line:

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

If instead you'd like to actually color different bands in the chart background, use
this technique, but instead of a 2x2 pattern, make it 1 wide by N high:

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

You could also adopt the conditional charting approach from this page, so the data
points themselves take on the appropriate color:

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Os wrote:
> Thanks Jon. That was my initail try before, but is not
> helping my purpose. You see, I have a user enters data
> every half an hour, and according to that data/values, we
> need the curve line to visually show whether the product
> is on the Green (pref), Yellow, or Red. But I don't know
> how to highlight individual gridlines with different
> color. Because Excel will highlight the whole chart area.
> I need the most outer rows (upper + lowest), to always be
> Red, the inner rows to always be Yellow, and the most
> inner 5 rows to always be Green. I just can't figure it
> out. That's why I highlighted 9 rows on the sheet, and
> would like the cure line only to show - everything else
> should be transparent, but i'm not sure that will work
> either, because my rows are just simply rows, without any
> scaling. I hope you got the picture by now.
> Thanks.
>
>
>
>>-----Original Message-----
>>Oh, so you need to add the series that makes the
>
> line/curve. From the Chart menu,
>
>>select Source Data, click on the Series tab, click on
>
> Add, then select the values
>
>>for this series, add a name, etc.
>>
>>If you are only providing Y values, Excel is just going
>
> to use counting numbers for
>
>>X (1, 2, 3, etc.). Maybe this is okay.
>>
>>When you add the series, it will probably not be the
>
> type you want. Select the new
>
>>series and on the chart menu, select Chart Type, and
>
> choose an appropriate line or
>
>>scatter type.
>>
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>Peltier Technical Services
>>Tutorials and Custom Solutions
>>http://PeltierTech.com/
>>_______
>>
>>Os wrote:
>>
>>
>>>Thanks Jon for the reply. But is not exactly what i'm
>>>looking for. Like I wrote before, I already set up my
>>>plot area and highlighted it the way I wanted. I only
>>>need to show the curve line on the sheet, without
>
> showing
>
>>>the gridlines, axes, etc. My data series is from only
>
> one
>
>>>row. Any idea?
>>>
>>>Thank you.
>>>
>>>
>>>
>>>
>>>>-----Original Message-----
>>>>Does this help?
>>>>
>>>>
>>>
>>>
> http://peltiertech.com/Excel/Charts...onalChart1.html
>
>>>>- Jon
>>>>-------
>>>>Jon Peltier, Microsoft Excel MVP
>>>>Peltier Technical Services
>>>>Tutorials and Custom Solutions
>>>>http://PeltierTech.com/
>>>>_______
>>>>
>>>>Os wrote:
>>>>
>>>>
>>>>
>>>>>I have created my own plot area, and I need to plot a
>>>>>line chart on it without showing axes, gridlines, or
>>>
>>>plot
>>>
>>>
>>>>>area. My plot area is highlighted red on the upper
>
> and
>
>>>>>lower rows, then yellow on the following rows
>>>
>>>(up/down),
>>>
>>>
>>>>>while the inner area is highlighted green. My avg
>>>
>>>values
>>>
>>>
>>>>>(data series) is right above the plot area (5 rows
>>>
>>>green,
>>>
>>>
>>>>>2 Yellow, and 2 red - plus 16 columns).
>>>>>Or my second alternative to plot a column chart that
>>>
>>>will
>>>
>>>
>>>>>show for example: 1.99 - 2.21 = green / 1.70 - 1.99
>>>
>>>and
>>>
>>>
>>>>>2.21 - 2.50 = yellow, and any value below or over the
>>>>>last values = Red. Can anybody help?
>>>>>Thanks.
>>>>
>>>>.
>>>>
>>
>>.
>>

The process is pretty much as on the example I cited.

Let's do the bands first. This is for five bands, each 10 units high:

bands
red 10
yellow 10
green 10
yellow 10
red 10

Select the data, make a stacked column chart, data in rows. Now a little formatting.
Double click a series, on the Options tab, set the gap width to zero. This makes the
columns as wide as the entire chart. Now color the columns, and set the border of
each to None, on the patterns tab.

Double click the vertical axis and select the Scale tab, set the max to 50, and
check the Axis Crosses at Maximum.

Here's some sample data:

Line
A 8
B 14
C 24
D 35
E 43

Copy the range, select the chart, choose Paste Special from the Edit menu. Choose
New Series, By Columns, Name in First Row, Categories in First Column. Well, that's
another column series that messed up the nice bands. So select the goofy bars,
choose Chart Type from the Chart menu, and choose a Line type. That fixed the bars.
Now double click the line, and on the Axis tab, click on Secondary, then format the
series on the Patterns tab. Select the chart, choose Chart Options on the Chart
menu, and on the Axes tab, check the Secondary X Axis box. Your choice whether to
keep or uncheck the secondary Y axis.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


Os wrote:

> I have tried your second method to color the background
> into Red, Yellow, Green, Yellow, and Red, but it didn't
> work for me. This site will not let me attach how I want
> my chart to look like, so you can understand what i'm
> talking about. It seems to me that what i'm asking is
> simple, but I just can't get it. Like I said before, I
> need the background colored from top to bottom as: Red,
> Yellow, Green, Yellow, Red. And to stay that way, while
> the line curve should reflect my only data series
> (values) as it is entered by user.
>
>
>>-----Original Message-----
>>If you want to show colored gridlines in the chart, you
>
> can use this technique, and
>
>>add one series for each colored line:
>>
>>
>
> http://peltiertech.com/Excel/Charts...HorzSeries.html
>
>>If instead you'd like to actually color different bands
>
> in the chart background, use
>
>>this technique, but instead of a 2x2 pattern, make it 1
>
> wide by N high:
>
>>
>
> http://peltiertech.com/Excel/Charts...QuadrantBackgro
> und.html
>
>>You could also adopt the conditional charting approach
>
> from this page, so the data
>
>>points themselves take on the appropriate color:
>>
>>
>
> http://peltiertech.com/Excel/Charts...onalChart1.html
>
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>Peltier Technical Services
>>Tutorials and Custom Solutions
>>http://PeltierTech.com/
>>_______
>>
>>Os wrote:
>>
>>>Thanks Jon. That was my initail try before, but is not
>>>helping my purpose. You see, I have a user enters data
>>>every half an hour, and according to that data/values,
>
> we
>
>>>need the curve line to visually show whether the
>
> product
>
>>>is on the Green (pref), Yellow, or Red. But I don't
>
> know
>
>>>how to highlight individual gridlines with different
>>>color. Because Excel will highlight the whole chart
>
> area.
>
>>>I need the most outer rows (upper + lowest), to always
>
> be
>
>>>Red, the inner rows to always be Yellow, and the most
>>>inner 5 rows to always be Green. I just can't figure
>
> it
>
>>>out. That's why I highlighted 9 rows on the sheet, and
>>>would like the cure line only to show - everything
>
> else
>
>>>should be transparent, but i'm not sure that will work
>>>either, because my rows are just simply rows, without
>
> any
>
>>>scaling. I hope you got the picture by now.
>>>Thanks.
>>>
>>>
>>>
>>>
>>>>-----Original Message-----
>>>>Oh, so you need to add the series that makes the
>>>
>>>line/curve. From the Chart menu,
>>>
>>>
>>>>select Source Data, click on the Series tab, click on
>>>
>>>Add, then select the values
>>>
>>>
>>>>for this series, add a name, etc.
>>>>
>>>>If you are only providing Y values, Excel is just
>
> going
>
>>>to use counting numbers for
>>>
>>>
>>>>X (1, 2, 3, etc.). Maybe this is okay.
>>>>
>>>>When you add the series, it will probably not be the
>>>
>>>type you want. Select the new
>>>
>>>
>>>>series and on the chart menu, select Chart Type, and
>>>
>>>choose an appropriate line or
>>>
>>>
>>>>scatter type.
>>>>
>>>>- Jon
>>>>-------
>>>>Jon Peltier, Microsoft Excel MVP
>>>>Peltier Technical Services
>>>>Tutorials and Custom Solutions
>>>>http://PeltierTech.com/
>>>>_______
>>>>
>>>>Os wrote:
>>>>
>>>>
>>>>
>>>>>Thanks Jon for the reply. But is not exactly what i'm
>>>>>looking for. Like I wrote before, I already set up my
>>>>>plot area and highlighted it the way I wanted. I only
>>>>>need to show the curve line on the sheet, without
>>>
>>>showing
>>>
>>>
>>>>>the gridlines, axes, etc. My data series is from only
>>>
>>>one
>>>
>>>
>>>>>row. Any idea?
>>>>>
>>>>>Thank you.
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>>-----Original Message-----
>>>>>>Does this help?
>>>>>>
>>>>>>
>>>>>
>>>>>
> http://peltiertech.com/Excel/Charts...onalChart1.html
>
>>>>>>- Jon
>>>>>>-------
>>>>>>Jon Peltier, Microsoft Excel MVP
>>>>>>Peltier Technical Services
>>>>>>Tutorials and Custom Solutions
>>>>>>http://PeltierTech.com/
>>>>>>_______
>>>>>>
>>>>>>Os wrote:
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>>I have created my own plot area, and I need to plot
>
> a
>
>>>>>>>line chart on it without showing axes, gridlines,
>
> or
>
>>>>>plot
>>>>>
>>>>>
>>>>>
>>>>>>>area. My plot area is highlighted red on the upper
>>>
>>>and
>>>
>>>
>>>>>>>lower rows, then yellow on the following rows
>>>>>
>>>>>(up/down),
>>>>>
>>>>>
>>>>>
>>>>>>>while the inner area is highlighted green. My avg
>>>>>
>>>>>values
>>>>>
>>>>>
>>>>>
>>>>>>>(data series) is right above the plot area (5 rows
>>>>>
>>>>>green,
>>>>>
>>>>>
>>>>>
>>>>>>>2 Yellow, and 2 red - plus 16 columns).
>>>>>>>Or my second alternative to plot a column chart
>
> that
>
>>>>>will
>>>>>
>>>>>
>>>>>
>>>>>>>show for example: 1.99 - 2.21 = green / 1.70 - 1.99
>>>>>
>>>>>and
>>>>>
>>>>>
>>>>>
>>>>>>>2.21 - 2.50 = yellow, and any value below or over
>
> the
>
>>>>>>>last values = Red. Can anybody help?
>>>>>>>Thanks.
>>>>>>
>>>>>>.
>>>>>>
>>>>
>>>>.
>>>>
>>
>>.
>>

Jon,

Thanks for reply, and for being patient with me. I did
the first three steps fine but when I came to this one:

"So select the goofy bars,
choose Chart Type from the Chart menu, and choose a Line
type. That fixed the bars.
Now double click the line, and on the Axis tab, click on
Secondary, then format"

I end up with 15 bars (from my data) plus my original 5
colums (red, yellow, green,etc) on the same plot are.
When I select the chart, and try to change the type to
Line, willn't let me do it. If select the entire area,
will just show a blank plot area! How can I do this step?

Thanks.

>-----Original Message-----
>The process is pretty much as on the example I cited.
>
>Let's do the bands first. This is for five bands, each
10 units high:
>
> bands
>red 10
>yellow 10
>green 10
>yellow 10
>red 10
>
>Select the data, make a stacked column chart, data in
rows. Now a little formatting.
>Double click a series, on the Options tab, set the gap
width to zero. This makes the
>columns as wide as the entire chart. Now color the
columns, and set the border of
>each to None, on the patterns tab.
>
>Double click the vertical axis and select the Scale tab,
set the max to 50, and
>check the Axis Crosses at Maximum.
>
>Here's some sample data:
>
> Line
>A 8
>B 14
>C 24
>D 35
>E 43
>
>Copy the range, select the chart, choose Paste Special
from the Edit menu. Choose
>New Series, By Columns, Name in First Row, Categories in
First Column. Well, that's
>another column series that messed up the nice bands. So
select the goofy bars,
>choose Chart Type from the Chart menu, and choose a Line
type. That fixed the bars.
>Now double click the line, and on the Axis tab, click on
Secondary, then format the
>series on the Patterns tab. Select the chart, choose
Chart Options on the Chart
>menu, and on the Axes tab, check the Secondary X Axis
box. Your choice whether to
>keep or uncheck the secondary Y axis.
>
>- Jon
>-------
>Jon Peltier, Microsoft Excel MVP
>Peltier Technical Services
>Tutorials and Custom Solutions
>http://PeltierTech.com/
>_______
>
>
>Os wrote:
>
>> I have tried your second method to color the
background
>> into Red, Yellow, Green, Yellow, and Red, but it
didn't
>> work for me. This site will not let me attach how I
want
>> my chart to look like, so you can understand what i'm
>> talking about. It seems to me that what i'm asking is
>> simple, but I just can't get it. Like I said before, I
>> need the background colored from top to bottom as:
Red,
>> Yellow, Green, Yellow, Red. And to stay that way,
while
>> the line curve should reflect my only data series
>> (values) as it is entered by user.
>>
>>
>>>-----Original Message-----
>>>If you want to show colored gridlines in the chart,
you
>>
>> can use this technique, and
>>
>>>add one series for each colored line:
>>>
>>>
>>
>>
http://peltiertech.com/Excel/Charts...HorzSeries.html
>>
>>>If instead you'd like to actually color different
bands
>>
>> in the chart background, use
>>
>>>this technique, but instead of a 2x2 pattern, make it
1
>>
>> wide by N high:
>>
>>>
>>
>>
http://peltiertech.com/Excel/Charts...QuadrantBackgro
>> und.html
>>
>>>You could also adopt the conditional charting approach
>>
>> from this page, so the data
>>
>>>points themselves take on the appropriate color:
>>>
>>>
>>
>>
http://peltiertech.com/Excel/Charts...onalChart1.html
>>
>>>- Jon
>>>-------
>>>Jon Peltier, Microsoft Excel MVP
>>>Peltier Technical Services
>>>Tutorials and Custom Solutions
>>>http://PeltierTech.com/
>>>_______
>>>
>>>Os wrote:
>>>
>>>>Thanks Jon. That was my initail try before, but is
not
>>>>helping my purpose. You see, I have a user enters
data
>>>>every half an hour, and according to that
data/values,
>>
>> we
>>
>>>>need the curve line to visually show whether the
>>
>> product
>>
>>>>is on the Green (pref), Yellow, or Red. But I don't
>>
>> know
>>
>>>>how to highlight individual gridlines with different
>>>>color. Because Excel will highlight the whole chart
>>
>> area.
>>
>>>>I need the most outer rows (upper + lowest), to
always
>>
>> be
>>
>>>>Red, the inner rows to always be Yellow, and the most
>>>>inner 5 rows to always be Green. I just can't figure
>>
>> it
>>
>>>>out. That's why I highlighted 9 rows on the sheet,
and
>>>>would like the cure line only to show - everything
>>
>> else
>>
>>>>should be transparent, but i'm not sure that will
work
>>>>either, because my rows are just simply rows, without
>>
>> any
>>
>>>>scaling. I hope you got the picture by now.
>>>>Thanks.
>>>>
>>>>
>>>>
>>>>
>>>>>-----Original Message-----
>>>>>Oh, so you need to add the series that makes the
>>>>
>>>>line/curve. From the Chart menu,
>>>>
>>>>
>>>>>select Source Data, click on the Series tab, click
on
>>>>
>>>>Add, then select the values
>>>>
>>>>
>>>>>for this series, add a name, etc.
>>>>>
>>>>>If you are only providing Y values, Excel is just
>>
>> going
>>
>>>>to use counting numbers for
>>>>
>>>>
>>>>>X (1, 2, 3, etc.). Maybe this is okay.
>>>>>
>>>>>When you add the series, it will probably not be the
>>>>
>>>>type you want. Select the new
>>>>
>>>>
>>>>>series and on the chart menu, select Chart Type, and
>>>>
>>>>choose an appropriate line or
>>>>
>>>>
>>>>>scatter type.
>>>>>
>>>>>- Jon
>>>>>-------
>>>>>Jon Peltier, Microsoft Excel MVP
>>>>>Peltier Technical Services
>>>>>Tutorials and Custom Solutions
>>>>>http://PeltierTech.com/
>>>>>_______
>>>>>
>>>>>Os wrote:
>>>>>
>>>>>
>>>>>
>>>>>>Thanks Jon for the reply. But is not exactly what
i'm
>>>>>>looking for. Like I wrote before, I already set up
my
>>>>>>plot area and highlighted it the way I wanted. I
only
>>>>>>need to show the curve line on the sheet, without
>>>>
>>>>showing
>>>>
>>>>
>>>>>>the gridlines, axes, etc. My data series is from
only
>>>>
>>>>one
>>>>
>>>>
>>>>>>row. Any idea?
>>>>>>
>>>>>>Thank you.
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>>-----Original Message-----
>>>>>>>Does this help?
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>
http://peltiertech.com/Excel/Charts...onalChart1.html
>>
>>>>>>>- Jon
>>>>>>>-------
>>>>>>>Jon Peltier, Microsoft Excel MVP
>>>>>>>Peltier Technical Services
>>>>>>>Tutorials and Custom Solutions
>>>>>>>http://PeltierTech.com/
>>>>>>>_______
>>>>>>>
>>>>>>>Os wrote:
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>>I have created my own plot area, and I need to
plot
>>
>> a
>>
>>>>>>>>line chart on it without showing axes, gridlines,
>>
>> or
>>
>>>>>>plot
>>>>>>
>>>>>>
>>>>>>
>>>>>>>>area. My plot area is highlighted red on the
upper
>>>>
>>>>and
>>>>
>>>>
>>>>>>>>lower rows, then yellow on the following rows
>>>>>>
>>>>>>(up/down),
>>>>>>
>>>>>>
>>>>>>
>>>>>>>>while the inner area is highlighted green. My avg
>>>>>>
>>>>>>values
>>>>>>
>>>>>>
>>>>>>
>>>>>>>>(data series) is right above the plot area (5
rows
>>>>>>
>>>>>>green,
>>>>>>
>>>>>>
>>>>>>
>>>>>>>>2 Yellow, and 2 red - plus 16 columns).
>>>>>>>>Or my second alternative to plot a column chart
>>
>> that
>>
>>>>>>will
>>>>>>
>>>>>>
>>>>>>
>>>>>>>>show for example: 1.99 - 2.21 = green / 1.70 -
1.99
>>>>>>
>>>>>>and
>>>>>>
>>>>>>
>>>>>>
>>>>>>>>2.21 - 2.50 = yellow, and any value below or over
>>
>> the
>>
>>>>>>>>last values = Red. Can anybody help?
>>>>>>>>Thanks.
>>>>>>>
>>>>>>>.
>>>>>>>
>>>>>
>>>>>.
>>>>>
>>>
>>>.
>>>
>
>.
>

Are the 15 added bars all in the same series? They probably should be, so they'll be
represented by a series of points connected by a line.

You only need to click on one of the added bars, then change the series type. When
you say it wouldn't let you, how did it inform you of this?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Os wrote:

> Jon,
>
> Thanks for reply, and for being patient with me. I did
> the first three steps fine but when I came to this one:
>
> "So select the goofy bars,
> choose Chart Type from the Chart menu, and choose a Line
> type. That fixed the bars.
> Now double click the line, and on the Axis tab, click on
> Secondary, then format"
>
> I end up with 15 bars (from my data) plus my original 5
> colums (red, yellow, green,etc) on the same plot are.
> When I select the chart, and try to change the type to
> Line, willn't let me do it. If select the entire area,
> will just show a blank plot area! How can I do this step?
>
> Thanks.
>
>
>>-----Original Message-----
>>The process is pretty much as on the example I cited.
>>
>>Let's do the bands first. This is for five bands, each
>
> 10 units high:
>
>> bands
>>red 10
>>yellow 10
>>green 10
>>yellow 10
>>red 10
>>
>>Select the data, make a stacked column chart, data in
>
> rows. Now a little formatting.
>
>>Double click a series, on the Options tab, set the gap
>
> width to zero. This makes the
>
>>columns as wide as the entire chart. Now color the
>
> columns, and set the border of
>
>>each to None, on the patterns tab.
>>
>>Double click the vertical axis and select the Scale tab,
>
> set the max to 50, and
>
>>check the Axis Crosses at Maximum.
>>
>>Here's some sample data:
>>
>> Line
>>A 8
>>B 14
>>C 24
>>D 35
>>E 43
>>
>>Copy the range, select the chart, choose Paste Special
>
> from the Edit menu. Choose
>
>>New Series, By Columns, Name in First Row, Categories in
>
> First Column. Well, that's
>
>>another column series that messed up the nice bands. So
>
> select the goofy bars,
>
>>choose Chart Type from the Chart menu, and choose a Line
>
> type. That fixed the bars.
>
>>Now double click the line, and on the Axis tab, click on
>
> Secondary, then format the
>
>>series on the Patterns tab. Select the chart, choose
>
> Chart Options on the Chart
>
>>menu, and on the Axes tab, check the Secondary X Axis
>
> box. Your choice whether to
>
>>keep or uncheck the secondary Y axis.
>>
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>Peltier Technical Services
>>Tutorials and Custom Solutions
>>http://PeltierTech.com/
>>_______
>>
>>
>>Os wrote:
>>
>>
>>>I have tried your second method to color the
>
> background
>
>>>into Red, Yellow, Green, Yellow, and Red, but it
>
> didn't
>
>>>work for me. This site will not let me attach how I
>
> want
>
>>>my chart to look like, so you can understand what i'm
>>>talking about. It seems to me that what i'm asking is
>>>simple, but I just can't get it. Like I said before, I
>>>need the background colored from top to bottom as:
>
> Red,
>
>>>Yellow, Green, Yellow, Red. And to stay that way,
>
> while
>
>>>the line curve should reflect my only data series
>>>(values) as it is entered by user.
>>>
>>>
>>>
>>>>-----Original Message-----
>>>>If you want to show colored gridlines in the chart,
>
> you
>
>>>can use this technique, and
>>>
>>>
>>>>add one series for each colored line:
>>>>
>>>>
>>>
>>>
> http://peltiertech.com/Excel/Charts...HorzSeries.html
>
>>>>If instead you'd like to actually color different
>
> bands
>
>>>in the chart background, use
>>>
>>>
>>>>this technique, but instead of a 2x2 pattern, make it
>
> 1
>
>>>wide by N high:
>>>
>>>
>>>>
>>>
>>>
> http://peltiertech.com/Excel/Charts...QuadrantBackgro
>
>>>und.html
>>>
>>>
>>>>You could also adopt the conditional charting approach
>>>
>>>from this page, so the data
>>>
>>>
>>>>points themselves take on the appropriate color:
>>>>
>>>>
>>>
>>>
> http://peltiertech.com/Excel/Charts...onalChart1.html
>
>>>>- Jon
>>>>-------
>>>>Jon Peltier, Microsoft Excel MVP
>>>>Peltier Technical Services
>>>>Tutorials and Custom Solutions
>>>>http://PeltierTech.com/
>>>>_______
>>>>
>>>>Os wrote:
>>>>
>>>>
>>>>>Thanks Jon. That was my initail try before, but is
>
> not
>
>>>>>helping my purpose. You see, I have a user enters
>
> data
>
>>>>>every half an hour, and according to that
>
> data/values,
>
>>>we
>>>
>>>
>>>>>need the curve line to visually show whether the
>>>
>>>product
>>>
>>>
>>>>>is on the Green (pref), Yellow, or Red. But I don't
>>>
>>>know
>>>
>>>
>>>>>how to highlight individual gridlines with different
>>>>>color. Because Excel will highlight the whole chart
>>>
>>>area.
>>>
>>>
>>>>>I need the most outer rows (upper + lowest), to
>
> always
>
>>>be
>>>
>>>
>>>>>Red, the inner rows to always be Yellow, and the most
>>>>>inner 5 rows to always be Green. I just can't figure
>>>
>>>it
>>>
>>>
>>>>>out. That's why I highlighted 9 rows on the sheet,
>
> and
>
>>>>>would like the cure line only to show - everything
>>>
>>>else
>>>
>>>
>>>>>should be transparent, but i'm not sure that will
>
> work
>
>>>>>either, because my rows are just simply rows, without
>>>
>>>any
>>>
>>>
>>>>>scaling. I hope you got the picture by now.
>>>>>Thanks.
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>>-----Original Message-----
>>>>>>Oh, so you need to add the series that makes the
>>>>>
>>>>>line/curve. From the Chart menu,
>>>>>
>>>>>
>>>>>
>>>>>>select Source Data, click on the Series tab, click
>
> on
>
>>>>>Add, then select the values
>>>>>
>>>>>
>>>>>
>>>>>>for this series, add a name, etc.
>>>>>>
>>>>>>If you are only providing Y values, Excel is just
>>>
>>>going
>>>
>>>
>>>>>to use counting numbers for
>>>>>
>>>>>
>>>>>
>>>>>>X (1, 2, 3, etc.). Maybe this is okay.
>>>>>>
>>>>>>When you add the series, it will probably not be the
>>>>>
>>>>>type you want. Select the new
>>>>>
>>>>>
>>>>>
>>>>>>series and on the chart menu, select Chart Type, and
>>>>>
>>>>>choose an appropriate line or
>>>>>
>>>>>
>>>>>
>>>>>>scatter type.
>>>>>>
>>>>>>- Jon
>>>>>>-------
>>>>>>Jon Peltier, Microsoft Excel MVP
>>>>>>Peltier Technical Services
>>>>>>Tutorials and Custom Solutions
>>>>>>http://PeltierTech.com/
>>>>>>_______
>>>>>>
>>>>>>Os wrote:
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>>Thanks Jon for the reply. But is not exactly what
>
> i'm
>
>>>>>>>looking for. Like I wrote before, I already set up
>
> my
>
>>>>>>>plot area and highlighted it the way I wanted. I
>
> only
>
>>>>>>>need to show the curve line on the sheet, without
>>>>>
>>>>>showing
>>>>>
>>>>>
>>>>>
>>>>>>>the gridlines, axes, etc. My data series is from
>
> only
>
>>>>>one
>>>>>
>>>>>
>>>>>
>>>>>>>row. Any idea?
>>>>>>>
>>>>>>>Thank you.
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>>-----Original Message-----
>>>>>>>>Does this help?
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>>
> http://peltiertech.com/Excel/Charts...onalChart1.html
>
>>>>>>>>- Jon
>>>>>>>>-------
>>>>>>>>Jon Peltier, Microsoft Excel MVP
>>>>>>>>Peltier Technical Services
>>>>>>>>Tutorials and Custom Solutions
>>>>>>>>http://PeltierTech.com/
>>>>>>>>_______
>>>>>>>>
>>>>>>>>Os wrote:
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>>I have created my own plot area, and I need to
>
> plot
>
>>>a
>>>
>>>
>>>>>>>>>line chart on it without showing axes, gridlines,
>>>
>>>or
>>>
>>>
>>>>>>>plot
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>>>area. My plot area is highlighted red on the
>
> upper
>
>>>>>and
>>>>>
>>>>>
>>>>>
>>>>>>>>>lower rows, then yellow on the following rows
>>>>>>>
>>>>>>>(up/down),
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>>>while the inner area is highlighted green. My avg
>>>>>>>
>>>>>>>values
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>>>(data series) is right above the plot area (5
>
> rows
>
>>>>>>>green,
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>>>2 Yellow, and 2 red - plus 16 columns).
>>>>>>>>>Or my second alternative to plot a column chart
>>>
>>>that
>>>
>>>
>>>>>>>will
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>>>show for example: 1.99 - 2.21 = green / 1.70 -
>
> 1.99
>
>>>>>>>and
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>>>2.21 - 2.50 = yellow, and any value below or over
>>>
>>>the
>>>
>>>
>>>>>>>>>last values = Red. Can anybody help?
>>>>>>>>>Thanks.
>>>>>>>>
>>>>>>>>.
>>>>>>>>
>>>>>>
>>>>>>.
>>>>>>
>>>>
>>>>.
>>>>
>>
>>.
>>

Yes they are. It's from two rows (one with the actual
values, and the other with numbers from 1 to 15).
I did just that; clicked on one column then selected
chart type, selected Line type, but the bars still there,
no Line!! What's it that i'm doning wrong.



>-----Original Message-----
>Are the 15 added bars all in the same series? They
probably should be, so they'll be
>represented by a series of points connected by a line.
>
>You only need to click on one of the added bars, then
change the series type. When
>you say it wouldn't let you, how did it inform you of
this?
>
>- Jon
>-------
>Jon Peltier, Microsoft Excel MVP
>Peltier Technical Services
>Tutorials and Custom Solutions
>http://PeltierTech.com/
>_______
>
>Os wrote:
>
>> Jon,
>>
>> Thanks for reply, and for being patient with me. I did
>> the first three steps fine but when I came to this one:
>>
>> "So select the goofy bars,
>> choose Chart Type from the Chart menu, and choose a
Line
>> type. That fixed the bars.
>> Now double click the line, and on the Axis tab, click
on
>> Secondary, then format"
>>
>> I end up with 15 bars (from my data) plus my original
5
>> colums (red, yellow, green,etc) on the same plot are.
>> When I select the chart, and try to change the type to
>> Line, willn't let me do it. If select the entire area,
>> will just show a blank plot area! How can I do this
step?
>>
>> Thanks.
>>
>>
>>>-----Original Message-----
>>>The process is pretty much as on the example I cited.
>>>
>>>Let's do the bands first. This is for five bands, each
>>
>> 10 units high:
>>
>>> bands
>>>red 10
>>>yellow 10
>>>green 10
>>>yellow 10
>>>red 10
>>>
>>>Select the data, make a stacked column chart, data in
>>
>> rows. Now a little formatting.
>>
>>>Double click a series, on the Options tab, set the gap
>>
>> width to zero. This makes the
>>
>>>columns as wide as the entire chart. Now color the
>>
>> columns, and set the border of
>>
>>>each to None, on the patterns tab.
>>>
>>>Double click the vertical axis and select the Scale
tab,
>>
>> set the max to 50, and
>>
>>>check the Axis Crosses at Maximum.
>>>
>>>Here's some sample data:
>>>
>>> Line
>>>A 8
>>>B 14
>>>C 24
>>>D 35
>>>E 43
>>>
>>>Copy the range, select the chart, choose Paste Special
>>
>> from the Edit menu. Choose
>>
>>>New Series, By Columns, Name in First Row, Categories
in
>>
>> First Column. Well, that's
>>
>>>another column series that messed up the nice bands.
So
>>
>> select the goofy bars,
>>
>>>choose Chart Type from the Chart menu, and choose a
Line
>>
>> type. That fixed the bars.
>>
>>>Now double click the line, and on the Axis tab, click
on
>>
>> Secondary, then format the
>>
>>>series on the Patterns tab. Select the chart, choose
>>
>> Chart Options on the Chart
>>
>>>menu, and on the Axes tab, check the Secondary X Axis
>>
>> box. Your choice whether to
>>
>>>keep or uncheck the secondary Y axis.
>>>
>>>- Jon
>>>-------
>>>Jon Peltier, Microsoft Excel MVP
>>>Peltier Technical Services
>>>Tutorials and Custom Solutions
>>>http://PeltierTech.com/
>>>_______
>>>
>>>
>>>Os wrote:
>>>
>>>
>>>>I have tried your second method to color the
>>
>> background
>>
>>>>into Red, Yellow, Green, Yellow, and Red, but it
>>
>> didn't
>>
>>>>work for me. This site will not let me attach how I
>>
>> want
>>
>>>>my chart to look like, so you can understand what i'm
>>>>talking about. It seems to me that what i'm asking is
>>>>simple, but I just can't get it. Like I said before,
I
>>>>need the background colored from top to bottom as:
>>
>> Red,
>>
>>>>Yellow, Green, Yellow, Red. And to stay that way,
>>
>> while
>>
>>>>the line curve should reflect my only data series
>>>>(values) as it is entered by user.
>>>>
>>>>
>>>>
>>>>>-----Original Message-----
>>>>>If you want to show colored gridlines in the chart,
>>
>> you
>>
>>>>can use this technique, and
>>>>
>>>>
>>>>>add one series for each colored line:
>>>>>
>>>>>
>>>>
>>>>
>>
http://peltiertech.com/Excel/Charts...HorzSeries.html
>>
>>>>>If instead you'd like to actually color different
>>
>> bands
>>
>>>>in the chart background, use
>>>>
>>>>
>>>>>this technique, but instead of a 2x2 pattern, make
it
>>
>> 1
>>
>>>>wide by N high:
>>>>
>>>>
>>>>>
>>>>
>>>>
>>
http://peltiertech.com/Excel/Charts...QuadrantBackgro
>>
>>>>und.html
>>>>
>>>>
>>>>>You could also adopt the conditional charting
approach
>>>>
>>>>from this page, so the data
>>>>
>>>>
>>>>>points themselves take on the appropriate color:
>>>>>
>>>>>
>>>>
>>>>
>>
http://peltiertech.com/Excel/Charts...onalChart1.html
>>
>>>>>- Jon
>>>>>-------
>>>>>Jon Peltier, Microsoft Excel MVP
>>>>>Peltier Technical Services
>>>>>Tutorials and Custom Solutions
>>>>>http://PeltierTech.com/
>>>>>_______
>>>>>
>>>>>Os wrote:
>>>>>
>>>>>
>>>>>>Thanks Jon. That was my initail try before, but is
>>
>> not
>>
>>>>>>helping my purpose. You see, I have a user enters
>>
>> data
>>
>>>>>>every half an hour, and according to that
>>
>> data/values,
>>
>>>>we
>>>>
>>>>
>>>>>>need the curve line to visually show whether the
>>>>
>>>>product
>>>>
>>>>
>>>>>>is on the Green (pref), Yellow, or Red. But I don't
>>>>
>>>>know
>>>>
>>>>
>>>>>>how to highlight individual gridlines with
different
>>>>>>color. Because Excel will highlight the whole chart
>>>>
>>>>area.
>>>>
>>>>
>>>>>>I need the most outer rows (upper + lowest), to
>>
>> always
>>
>>>>be
>>>>
>>>>
>>>>>>Red, the inner rows to always be Yellow, and the
most
>>>>>>inner 5 rows to always be Green. I just can't
figure
>>>>
>>>>it
>>>>
>>>>
>>>>>>out. That's why I highlighted 9 rows on the sheet,
>>
>> and
>>
>>>>>>would like the cure line only to show - everything
>>>>
>>>>else
>>>>
>>>>
>>>>>>should be transparent, but i'm not sure that will
>>
>> work
>>
>>>>>>either, because my rows are just simply rows,
without
>>>>
>>>>any
>>>>
>>>>
>>>>>>scaling. I hope you got the picture by now.
>>>>>>Thanks.
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>>-----Original Message-----
>>>>>>>Oh, so you need to add the series that makes the
>>>>>>
>>>>>>line/curve. From the Chart menu,
>>>>>>
>>>>>>
>>>>>>
>>>>>>>select Source Data, click on the Series tab, click
>>
>> on
>>
>>>>>>Add, then select the values
>>>>>>
>>>>>>
>>>>>>
>>>>>>>for this series, add a name, etc.
>>>>>>>
>>>>>>>If you are only providing Y values, Excel is just
>>>>
>>>>going
>>>>
>>>>
>>>>>>to use counting numbers for
>>>>>>
>>>>>>
>>>>>>
>>>>>>>X (1, 2, 3, etc.). Maybe this is okay.
>>>>>>>
>>>>>>>When you add the series, it will probably not be
the
>>>>>>
>>>>>>type you want. Select the new
>>>>>>
>>>>>>
>>>>>>
>>>>>>>series and on the chart menu, select Chart Type,
and
>>>>>>
>>>>>>choose an appropriate line or
>>>>>>
>>>>>>
>>>>>>
>>>>>>>scatter type.
>>>>>>>
>>>>>>>- Jon
>>>>>>>-------
>>>>>>>Jon Peltier, Microsoft Excel MVP
>>>>>>>Peltier Technical Services
>>>>>>>Tutorials and Custom Solutions
>>>>>>>http://PeltierTech.com/
>>>>>>>_______
>>>>>>>
>>>>>>>Os wrote:
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>>Thanks Jon for the reply. But is not exactly what
>>
>> i'm
>>
>>>>>>>>looking for. Like I wrote before, I already set
up
>>
>> my
>>
>>>>>>>>plot area and highlighted it the way I wanted. I
>>
>> only
>>
>>>>>>>>need to show the curve line on the sheet, without
>>>>>>
>>>>>>showing
>>>>>>
>>>>>>
>>>>>>
>>>>>>>>the gridlines, axes, etc. My data series is from
>>
>> only
>>
>>>>>>one
>>>>>>
>>>>>>
>>>>>>
>>>>>>>>row. Any idea?
>>>>>>>>
>>>>>>>>Thank you.
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>>-----Original Message-----
>>>>>>>>>Does this help?
>>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>>>
>>
http://peltiertech.com/Excel/Charts...onalChart1.html
>>
>>>>>>>>>- Jon
>>>>>>>>>-------
>>>>>>>>>Jon Peltier, Microsoft Excel MVP
>>>>>>>>>Peltier Technical Services
>>>>>>>>>Tutorials and Custom Solutions
>>>>>>>>>http://PeltierTech.com/
>>>>>>>>>_______
>>>>>>>>>
>>>>>>>>>Os wrote:
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>