Colour coding graph values

The UK's most regular instructor-led training courses.
Training information: microsoft excel training courses · Microsoft+excel+training · Microsoft Excel Training London
See also · excel-courses-london · excel courses in london · excel microsoft training

Resolved · Urgent Priority · Version 2007

6 posts

ReplyTue 19 May 2009, 23:51Delegate Nick said...

Nick has attended:
Excel Intermediate course
Excel Advanced course
Powerpoint Intermediate Advanced course

Colour coding graph values

I am creating a horizontal bar chart with a range of values from -2 to +2. I want to set up the chart so automatically bars with values greater than 0 are given a green fill, and bars with values less than 0 are given a red fill. Can you help me?

Version	Date	Location	Places available	Book	Next place rate (£)		List price
Excel Intermediate 1 day course
Version	Date	Location	Places available	Book	Pay by Card	Pay by Invoice	List price
2007	Mon 2 Aug 2010	Bloomsbury	5		£210	£215	£235
2007	Wed 4 Aug 2010	Limehouse	6		£207	£212	£235
2003	Thu 5 Aug 2010	Limehouse	5		£198	£203	£235
2010	Thu 5 Aug 2010	Bloomsbury	7		£285	£290	£299
2003	Fri 6 Aug 2010	Southwark	0	FULL			£235
2007	Mon 9 Aug 2010	Bloomsbury	6		£207	£212	£235
Full Schedule: See all 163 Excel Intermediate course dates. Bookings currently available until 30th September 2011.

870 posts

ReplyWed 20 May 2009, 00:24Trainer Rajeev said...

RE: Colour coding graph values

Dear Nick

Thank you for attending Excel courses!! I hope you enjoyed the courses and benefited from them.

It looks like you want to apply Conditional formatting to chart series rather than the cells in a worksheet.

Unfortunately I am not sure about colouring the negative bars as red but you can apply an Invert colour which removes the formatting from the bars showing negative values but the positive value bars remain with original colours. I hope it will be of some use to you!!

Please follow these steps:

1. Select the series for which you wish to change the colours

2. Right-click and then choose Format Data series from the short cut menu

3. Click the Fill from the dialog box

4. Ensure that you either choose Solid fill or Automatic from the fill options and then choose Invert if negative

I am not sure but may be there is a way of displaying the series as red for negative bars but I assume that you should be able to do this by some kind of VBA coding!!

I'll pass on this query to one of the trainers who have more experience with VBA!

I hope this has partially answered your query.

If this has answered your query then I would request you to please mark the question as resolved!! If not and you have a specific question related to this then please post it as a new question and we should be able to provide you the solution for it!!

Kindest Regards

Rajeev Rawat
MOS Master Instructor 2000 and 2003
MCAS Master Instructor 2007
MCT

· Jazzing Up Excel With Graphics
· Identifying Suitable Candidates For VBA For Excel Training
· An Introduction to Microsoft Excel: Top Features for the Excel Novice
· How to Create Better Excel Spreadsheets: Part Four
· Microsoft Excel as a Reporting Tool

Please browse our web site to find out more about
microsoft excel training courses and other Microsoft training courses.

Excel tip:

Select blank cells automatically

Get Excel to find any blank (empty) cells in a region for you by:

1. Selecting the appropriate region from your spreadsheet.

2. On the menu bar, go to Edit - Go to.

3. Click the 'Special' button, then select Blanks and click OK.

View all Excel hints and tips