Chart in Excel 2007- Positive & negative value columns

[Tigerxxx]

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

 

[Jon Peltier]

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

 

[Tigerxxx]

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]

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

 

[Tigerxxx]

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]

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]

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(Alpha>0,Alpha,0)

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

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

- Jon

 

[Tigerxxx]

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]

You need to reference the sheet as well:

='Sheet 1'!test

- Jon

 

[Tigerxxx]

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(Alpha>0,Alpha,0)

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

 

[ScottJ]

Big Thanks for this thread

Thank you very much for the solution below, it works very well in Excel 2007.

Cheers

Scott J, London, UK

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(Alpha>0,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
_______