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"?