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

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Peter Shore" <PeterShore@discussions.microsoft.com> wrote in message
news:229B38C2-3D2D-4117-81A2-748BB66D8F4D@microsoft.com...
> 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!
>
> "Jon Peltier" wrote:
>
>> Conditional Charts:
>> http://peltiertech.com/Excel/Charts/...nalChart1.html
>>
>> - Jon
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Peltier Technical Services
>> Tutorials and Custom Solutions
>> http://PeltierTech.com/
>> _______
>>
>>
>> "Peter Shore" <Peter Shore@discussions.microsoft.com> wrote in message
>> news:5DA5CE6D-9234-4B47-AB8F-F3FB8E647110@microsoft.com...
>> > 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.
>> >
>>
>>
>>

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.


"Jon Peltier" wrote:

> 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
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> http://PeltierTech.com
> _______
>
>
> "Peter Shore" <PeterShore@discussions.microsoft.com> wrote in message
> news:229B38C2-3D2D-4117-81A2-748BB66D8F4D@microsoft.com...
> > 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!
> >
> > "Jon Peltier" wrote:
> >
> >> Conditional Charts:
> >> http://peltiertech.com/Excel/Charts/...nalChart1.html
> >>
> >> - Jon
> >> -------
> >> Jon Peltier, Microsoft Excel MVP
> >> Peltier Technical Services
> >> Tutorials and Custom Solutions
> >> http://PeltierTech.com/
> >> _______
> >>
> >>
> >> "Peter Shore" <Peter Shore@discussions.microsoft.com> wrote in message
> >> news:5DA5CE6D-9234-4B47-AB8F-F3FB8E647110@microsoft.com...
> >> > 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.
> >> >
> >>
> >>
> >>
>
>
>

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Peter Shore" <PeterShore@discussions.microsoft.com> wrote in message
news:D8F63840-FAB0-42BD-BA97-1798D2E3BE6C@microsoft.com...
> 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.
>
>
> "Jon Peltier" wrote:
>
>> 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
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Tutorials and Custom Solutions
>> http://PeltierTech.com
>> _______
>>
>>
>> "Peter Shore" <PeterShore@discussions.microsoft.com> wrote in message
>> news:229B38C2-3D2D-4117-81A2-748BB66D8F4D@microsoft.com...
>> > 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!
>> >
>> > "Jon Peltier" wrote:
>> >
>> >> Conditional Charts:
>> >> http://peltiertech.com/Excel/Charts/...nalChart1.html
>> >>
>> >> - Jon
>> >> -------
>> >> Jon Peltier, Microsoft Excel MVP
>> >> Peltier Technical Services
>> >> Tutorials and Custom Solutions
>> >> http://PeltierTech.com/
>> >> _______
>> >>
>> >>
>> >> "Peter Shore" <Peter Shore@discussions.microsoft.com> wrote in message
>> >> news:5DA5CE6D-9234-4B47-AB8F-F3FB8E647110@microsoft.com...
>> >> > 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.
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>