Excel - conditional formating on graphs. Change colour on a value


G

Guest

Excel - 2003 or previous.
Conditional formating on cells is useful but I would like to see this on
graphs as well.
For example in a bar graph chart the series colour can be set either
automatically or manually. A single data point can be set manually to a
different colour.
I would like to change a data point bar by condition / comparing to a value.
The application is to 'flag' a data point by colour change if the value
exceeds a certain comparison value.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...fb8e647110&dg=microsoft.public.excel.charting
 
Ad

Advertisements

G

Guest

Jon,
I found your website and solution just minutes after posting my question. An
ingenious workround! The problem is that this makes the data table quite
complex. I would like to build in these formuales and then hide the cells but
as soon as you hide data it is no longer available for charts!
Another option would be to have the additional series and formulae in an
area outside of the print area and then view in 'print preview'
The table I have created is to be used by non excel experts so I have to
keep it as simple to use as possible and lock or conceal any formula to
prevent tampering!
 
J

Jon Peltier

Welcome to my world.

In general it's worth the effort to put your main data on a hidden
worksheet, and have the chart and any table in the display area link to this
data.

- Jon
 
G

Guest

Jon,
Thank you for the help - I tried this last night and I have been able to
produce the chart I need. I have also combined Bars with Lines so the
threshold values that change the colour are displayed as a line across the
chart. Net result is that as a bar graph value meets or exceeds one of the
lines it changes colour.

NB: Another trick I employed that you may find useful (or you may already
know this) is to use a customised background on a bar graph to give me a
coloured scale.
eg from 0% to 80% green, from 80% to 90% amber and from 90% to 100% red.
I started by setting rows and columns to the same size for square cells,
filled 8 cells green, 1 amber and 1 red. copied and pasted this into 'paint'
to create a bitmap. Then I used the format chart, background - picture and
selected the bitmap I had just created. This scaled itself to the chart
background and gave me a bar graph with green up to 80% etc.
 
Ad

Advertisements

J

Jon Peltier

You've seen my more flexible version, with stacked columns on the primary
axis and a regular column or line series on the secondary. When I get a
chance I'll post it as a web page.

- Jon
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top