Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

colour coding graph values

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Colour coding graph values

Colour coding graph values

ResolvedVersion 2007

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?

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

Excel tip:

Creating custom lists

In Excel if you type in January in a cell, you can then copy this cell to replicate Febraury, MArch, April etc.

This list has come from Tools- options and Custom lists.

Therefore to save time and create your own list you can click on New (in Tools and custom list tab) and type out the lsit that you want copied quickly.

All you have to do is then type in the 1st word and you will be able to copy the rest of the list quickly.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.11 secs.