Chart in Excel 2007- Positive & negative value columns
 

Hello,

How can I draw a column chart in Excel 2007 where the positive values are of
one color and negative values are of a different color.
Example- I would like to fill positive value columns in black and negative
value columns in red.

Thank you

Chart in Excel 2007- Positive & negative value columns
 

Excel 2007 provides "invert colors if negative" like 2003, but unfortunately
it's a little broken, so the negative color is always white.

Do this: put the positive values in one column of the worksheet and the
negatives in the next. Create a clustered column chart with two series, then
format the overlap to 100%, and color the bars as desired.

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


"Tigerxxx" wrote in message
...
Hello,

How can I draw a column chart in Excel 2007 where the positive values are
of
one color and negative values are of a different color.
Example- I would like to fill positive value columns in black and negative
value columns in red.

Thank you

Chart in Excel 2007- Positive & negative value columns
 

Hi Jon,

Thank you for the response. I still wish that it would be possible. Plotting
in 2 columns or rows is currently not feasible for me.

Best Regards

"Jon Peltier" wrote:

Excel 2007 provides "invert colors if negative" like 2003, but unfortunately
it's a little broken, so the negative color is always white.

Do this: put the positive values in one column of the worksheet and the
negatives in the next. Create a clustered column chart with two series, then
format the overlap to 100%, and color the bars as desired.

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


"Tigerxxx" wrote in message
...
Hello,

How can I draw a column chart in Excel 2007 where the positive values are
of
one color and negative values are of a different color.
Example- I would like to fill positive value columns in black and negative
value columns in red.

Thank you

Chart in Excel 2007- Positive & negative value columns
 

Is it really not feasible to use another couple of columns on the sheet, or
on another sheet? Leave the original data alone, and use formulas that only
show a value when the data is on the appropriate side of zero.

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


"Tigerxxx" wrote in message
...
Hi Jon,

Thank you for the response. I still wish that it would be possible.
Plotting
in 2 columns or rows is currently not feasible for me.

Best Regards

"Jon Peltier" wrote:

Excel 2007 provides "invert colors if negative" like 2003, but
unfortunately
it's a little broken, so the negative color is always white.

Do this: put the positive values in one column of the worksheet and the
negatives in the next. Create a clustered column chart with two series,
then
format the overlap to 100%, and color the bars as desired.

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


"Tigerxxx" wrote in message
...
Hello,

How can I draw a column chart in Excel 2007 where the positive values
are
of
one color and negative values are of a different color.
Example- I would like to fill positive value columns in black and
negative
value columns in red.

Thank you

Chart in Excel 2007- Positive & negative value columns
 

Hi Jon,

The charts I am plotting are dynamic i.e. the get updated when the data is
updated. Moreover I have a bif excel list and several charts to plot. hence i
was trying to avoid using two rows for one row of data.

"Jon Peltier" wrote:

Is it really not feasible to use another couple of columns on the sheet, or
on another sheet? Leave the original data alone, and use formulas that only
show a value when the data is on the appropriate side of zero.

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


"Tigerxxx" wrote in message
...
Hi Jon,

Thank you for the response. I still wish that it would be possible.
Plotting
in 2 columns or rows is currently not feasible for me.

Best Regards

"Jon Peltier" wrote:

Excel 2007 provides "invert colors if negative" like 2003, but
unfortunately
it's a little broken, so the negative color is always white.

Do this: put the positive values in one column of the worksheet and the
negatives in the next. Create a clustered column chart with two series,
then
format the overlap to 100%, and color the bars as desired.

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


"Tigerxxx" wrote in message
...
Hello,

How can I draw a column chart in Excel 2007 where the positive values
are
of
one color and negative values are of a different color.
Example- I would like to fill positive value columns in black and
negative
value columns in red.

Thank you

Chart in Excel 2007- Positive & negative value columns
 

If you use formulas, the two rows will update dynamically.

Two rows of cells is a small price to pay to get the chart appearance you
want, and once you set it up, it should remain functional.

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


"Tigerxxx" wrote in message
...
Hi Jon,

The charts I am plotting are dynamic i.e. the get updated when the data is
updated. Moreover I have a bif excel list and several charts to plot.
hence i
was trying to avoid using two rows for one row of data.

"Jon Peltier" wrote:

Is it really not feasible to use another couple of columns on the sheet,
or
on another sheet? Leave the original data alone, and use formulas that
only
show a value when the data is on the appropriate side of zero.

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


"Tigerxxx" wrote in message
...
Hi Jon,

Thank you for the response. I still wish that it would be possible.
Plotting
in 2 columns or rows is currently not feasible for me.

Best Regards

"Jon Peltier" wrote:

Excel 2007 provides "invert colors if negative" like 2003, but
unfortunately
it's a little broken, so the negative color is always white.

Do this: put the positive values in one column of the worksheet and
the
negatives in the next. Create a clustered column chart with two
series,
then
format the overlap to 100%, and color the bars as desired.

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


"Tigerxxx" wrote in message
...
Hello,

How can I draw a column chart in Excel 2007 where the positive
values
are
of
one color and negative values are of a different color.
Example- I would like to fill positive value columns in black and
negative
value columns in red.

Thank you

Chart in Excel 2007- Positive & negative value columns
 

You can set it up without more rows. Define a name "Alpha" which refers to
the range of values to plot. Then set up two more names:

Name: AlphaPlus
Refers To: =IF(Alpha0,Alpha,0)

Name: AlphaMinus
Refers To: =IF(Alpha<0,Alpha,0)

Make your chart using AlphaPlus and AlphaMinus as the source data.

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


"Tigerxxx" wrote in message
...
Hi Jon,

The charts I am plotting are dynamic i.e. the get updated when the data is
updated. Moreover I have a bif excel list and several charts to plot.
hence i
was trying to avoid using two rows for one row of data.

"Jon Peltier" wrote:

Is it really not feasible to use another couple of columns on the sheet,
or
on another sheet? Leave the original data alone, and use formulas that
only
show a value when the data is on the appropriate side of zero.

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


"Tigerxxx" wrote in message
...
Hi Jon,

Thank you for the response. I still wish that it would be possible.
Plotting
in 2 columns or rows is currently not feasible for me.

Best Regards

"Jon Peltier" wrote:

Excel 2007 provides "invert colors if negative" like 2003, but
unfortunately
it's a little broken, so the negative color is always white.

Do this: put the positive values in one column of the worksheet and
the
negatives in the next. Create a clustered column chart with two
series,
then
format the overlap to 100%, and color the bars as desired.

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


"Tigerxxx" wrote in message
...
Hello,

How can I draw a column chart in Excel 2007 where the positive
values
are
of
one color and negative values are of a different color.
Example- I would like to fill positive value columns in black and
negative
value columns in red.

Thank you

Chart in Excel 2007- Positive & negative value columns
 

Hi Jon,

The name range option you mentioned appears interesting. I was able to
create the names.
Say the name of my range is "test"
I am using Excel 2007.
I wam trying to put under "Series value" the name "test" as =test.
However Excel does not seem to be taking this input.
Is there a certain format to use to point the series value to a name?

Thank you



"Jon Peltier" wrote:

You can set it up without more rows. Define a name "Alpha" which refers to
the range of values to plot. Then set up two more names:

Name: AlphaPlus
Refers To: =IF(Alpha0,Alpha,0)

Name: AlphaMinus
Refers To: =IF(Alpha<0,Alpha,0)

Make your chart using AlphaPlus and AlphaMinus as the source data.

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


"Tigerxxx" wrote in message
...
Hi Jon,

The charts I am plotting are dynamic i.e. the get updated when the data is
updated. Moreover I have a bif excel list and several charts to plot.
hence i
was trying to avoid using two rows for one row of data.

"Jon Peltier" wrote:

Is it really not feasible to use another couple of columns on the sheet,
or
on another sheet? Leave the original data alone, and use formulas that
only
show a value when the data is on the appropriate side of zero.

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


"Tigerxxx" wrote in message
...
Hi Jon,

Thank you for the response. I still wish that it would be possible.
Plotting
in 2 columns or rows is currently not feasible for me.

Best Regards

"Jon Peltier" wrote:

Excel 2007 provides "invert colors if negative" like 2003, but
unfortunately
it's a little broken, so the negative color is always white.

Do this: put the positive values in one column of the worksheet and
the
negatives in the next. Create a clustered column chart with two
series,
then
format the overlap to 100%, and color the bars as desired.

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


"Tigerxxx" wrote in message
...
Hello,

How can I draw a column chart in Excel 2007 where the positive
values
are
of
one color and negative values are of a different color.
Example- I would like to fill positive value columns in black and
negative
value columns in red.

Thank you

Chart in Excel 2007- Positive & negative value columns
 

You need to reference the sheet as well:

='Sheet 1'!test

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


"Tigerxxx" wrote in message
...
Hi Jon,

The name range option you mentioned appears interesting. I was able to
create the names.
Say the name of my range is "test"
I am using Excel 2007.
I wam trying to put under "Series value" the name "test" as =test.
However Excel does not seem to be taking this input.
Is there a certain format to use to point the series value to a name?

Thank you



"Jon Peltier" wrote:

You can set it up without more rows. Define a name "Alpha" which refers
to
the range of values to plot. Then set up two more names:

Name: AlphaPlus
Refers To: =IF(Alpha0,Alpha,0)

Name: AlphaMinus
Refers To: =IF(Alpha<0,Alpha,0)

Make your chart using AlphaPlus and AlphaMinus as the source data.

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


"Tigerxxx" wrote in message
...
Hi Jon,

The charts I am plotting are dynamic i.e. the get updated when the data
is
updated. Moreover I have a bif excel list and several charts to plot.
hence i
was trying to avoid using two rows for one row of data.

"Jon Peltier" wrote:

Is it really not feasible to use another couple of columns on the
sheet,
or
on another sheet? Leave the original data alone, and use formulas that
only
show a value when the data is on the appropriate side of zero.

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


"Tigerxxx" wrote in message
...
Hi Jon,

Thank you for the response. I still wish that it would be possible.
Plotting
in 2 columns or rows is currently not feasible for me.

Best Regards

"Jon Peltier" wrote:

Excel 2007 provides "invert colors if negative" like 2003, but
unfortunately
it's a little broken, so the negative color is always white.

Do this: put the positive values in one column of the worksheet and
the
negatives in the next. Create a clustered column chart with two
series,
then
format the overlap to 100%, and color the bars as desired.

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


"Tigerxxx" wrote in message
...
Hello,

How can I draw a column chart in Excel 2007 where the positive
values
are
of
one color and negative values are of a different color.
Example- I would like to fill positive value columns in black and
negative
value columns in red.

Thank you

Chart in Excel 2007- Positive & negative value columns
 

Hi Jon,

I do not seem to getting the same results as you mention.
The chart plots well for the name "Alpha".
However "Alphaplus" & "AlphaMinus" do not seem to work.

Using Excel 2007, I went into: "Formulas", "Define name".
Under the window that pops up I put in the following values:
Name: "Alphaplus"
Scope: I selected the worksheet where applicable
Comment: left it blank
Refers to: =IF(Alpha0,Alpha,0)

Could you please check the above & reconfirm the syntax for writing the name
for "Alphaplus" & "AlphaMinus"?




"Jon Peltier" wrote:

You need to reference the sheet as well:

='Sheet 1'!test

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


"Tigerxxx" wrote in message
...
Hi Jon,

The name range option you mentioned appears interesting. I was able to
create the names.
Say the name of my range is "test"
I am using Excel 2007.
I wam trying to put under "Series value" the name "test" as =test.
However Excel does not seem to be taking this input.
Is there a certain format to use to point the series value to a name?

Thank you



"Jon Peltier" wrote:

You can set it up without more rows. Define a name "Alpha" which refers
to
the range of values to plot. Then set up two more names:

Name: AlphaPlus
Refers To: =IF(Alpha0,Alpha,0)

Name: AlphaMinus
Refers To: =IF(Alpha<0,Alpha,0)

Make your chart using AlphaPlus and AlphaMinus as the source data.

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


"Tigerxxx" wrote in message
...
Hi Jon,

The charts I am plotting are dynamic i.e. the get updated when the data
is
updated. Moreover I have a bif excel list and several charts to plot.
hence i
was trying to avoid using two rows for one row of data.

"Jon Peltier" wrote:

Is it really not feasible to use another couple of columns on the
sheet,
or
on another sheet? Leave the original data alone, and use formulas that
only
show a value when the data is on the appropriate side of zero.

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


"Tigerxxx" wrote in message
...
Hi Jon,

Thank you for the response. I still wish that it would be possible.
Plotting
in 2 columns or rows is currently not feasible for me.

Best Regards

"Jon Peltier" wrote:

Excel 2007 provides "invert colors if negative" like 2003, but
unfortunately
it's a little broken, so the negative color is always white.

Do this: put the positive values in one column of the worksheet and
the
negatives in the next. Create a clustered column chart with two
series,
then
format the overlap to 100%, and color the bars as desired.

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


"Tigerxxx" wrote in message
...
Hello,

How can I draw a column chart in Excel 2007 where the positive
values
are
of
one color and negative values are of a different color.
Example- I would like to fill positive value columns in black and
negative
value columns in red.

Thank you