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

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.
> >
>
>
>

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.
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>