Conditional formatting in a bar chart

[Charles Eaves]

I have a horizontal bar chart for weekly sales figures i.e. this years
sales minus last years sales .
I am using Excel 2007.
I have positive and negative sales figures.
Everything works fine but I would like to chart negative sales as red and
positive sales as green on the same chart?
I am new to Excel and I am not comfortable using VBA code, if that helps.
Thanks

 

[Andy Pope]

Hi,

No need for vba. Just use 2 series and some formula.
http://peltiertech.com/Excel/Charts/ConditionalChart1.html

Cheers
Andy

 

[Charles Eaves]

Thanks Andy
I will give it a go but I my table has 3 columns, This years sales, last
years sales and Net sales.
My chart get its data from the net sales column.
The numbers are positive and negative.
I want to plot from the net sales column and according to the value of the
net sales figure, I want the bars to be red for negative net sales and green
for positive net sales.

 

[Andy Pope]

You need to create another 2 columns of data via formula based on the
net sales values.

If you have xl2003 or older and you are charting positive and negative
values then maybe this is mode suited.
http://www.andypope.info/charts/Invertneg.htm

Cheers
Andy

Thanks Andy
I will give it a go but I my table has 3 columns, This years sales, last
years sales and Net sales.
My chart get its data from the net sales column.
The numbers are positive and negative.
I want to plot from the net sales column and according to the value of the
net sales figure, I want the bars to be red for negative net sales and green
for positive net sales.

 

[ShaneDevenshire]

Hi,

Suppose the data you originally plotted was in columns A1:B13
A B C D
Months Amount Positive Negative
Jan -16 0 -16
Feb 6 6 0
Mar -16 0 -16
Apr -7 0 -7
May 1 1 0
Jun 11 11 0
Jul 18 18 0
Aug -18 0 -18
Sep -8 0 -8
Oct -10 0 -10
Nov 29 29 0
Dec 12 12 0

then you create two new columns, above those are C and D
In cell C2 enter the formula
=IF(B2>=0,B2,0)
In cell D2 enter the formula
=IF(B2<0,B2,0)
Copy these down and then plot A1:A13,C113. You do that by highlighting
the first set of data and holding down the Ctrl key and selecting C113.

--
Thanks,
Shane Devenshire

Thanks Andy
I will give it a go but I my table has 3 columns, This years sales, last
years sales and Net sales.
My chart get its data from the net sales column.
The numbers are positive and negative.
I want to plot from the net sales column and according to the value of the
net sales figure, I want the bars to be red for negative net sales and green
for positive net sales.