Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    163
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi,

    Is is possible to put conditional formatting on data points in a chart? I am measuring whether different divisions are meeting a target. I calculate the percentage plus or minus, then put the percentages in a chart. If the percentage is positive I want the data point to be coloured green. If the percentage is negative, I want the data point to be coloured red. If I tick "invert if negative" Excel shows the negative data points as white.

    eg Div A -58%
    Div B -36%
    Div C +12%
    Div D +33%

    Also, when I try and create this chart, the category names appear below the zero line rather than outside the plot area. Can anyone tell me how to get the category names to show outside the plat area?

    Thanks for any help.

    capri

  2. #2
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    163
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by capri View Post
    Is is possible to put conditional formatting on data points in a chart?

    I think I found the solution at

    http://www.andypope.info/charts/Invertneg.htm

    in case the full url does not show -- ^http://www.andypope.info/charts/Invertneg.htm

    in case anyone else is looking for the same type of solution. I haven't tried it yet, but it appears to do what I want.

    capri

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brooklyn, New York, USA
    Posts
    200
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I'd also like to change the formatting of data points in a bar chart, but not if they are negative. I used to work on a help desk where we were asked to do this and someone showed me how to insert a formula that would effect the bar fill (if this, then that...) but I don't remember how to, and I've just looked through excel to find an opportunity to without success. Any clues?
    thanks!

    Naomi

  4. #4
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    A formula that changes the formatting of a chart?
    New to me (and therefore I am very curious). Was this specific to bar charts only?

    Check out Jon Peltier's site for some general principles. I was thinking of something more direct originally.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  5. #5
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brooklyn, New York, USA
    Posts
    200
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi Genie,
    I don't remember if it was only bar charts, but sure as I can see the sky from my window, I know we did conditional formatting within excel charts.

    Naomi

  6. #6
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Does Jon's advice match your memories?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  7. #7
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brooklyn, New York, USA
    Posts
    200
    Thanks
    2
    Thanked 0 Times in 0 Posts
    No, I'm afraid not. I already have a floating bar chart that has conditions for the length of each bar. It's the exception I want to build in (in other words, a condition within a condition) that would actually change the color already assigned to that bar. I remember that we would put formulas right into the formatting area of the chart. This was 15 years ago.

  8. #8
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Quote Originally Posted by naomi View Post
    This was 15 years ago.
    That might be why I'm having trouble finding it in 2007!
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  9. #9
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Still thinking about this - the only way I can think of changing the colour dynamically would be to layer one transparent chart on top of another each chart using the desired colour formatting.
    Using an intermediate data set, zero out the data when the conditions are not met (keeping the whole chart invisible).

    I sure hope an Excel genius will chime in!
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  10. #10
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brooklyn, New York, USA
    Posts
    200
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by WebGenii View Post
    That might be why I'm having trouble finding it in 2007!
    I thought that might get a laugh! On the other hand, I expected that whatever I could do in whatever version that was 15 years ago should be doable now. Guess not!

  11. #11
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brooklyn, New York, USA
    Posts
    200
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by WebGenii View Post
    Still thinking about this - the only way I can think of changing the colour dynamically would be to layer one transparent chart on top of another each chart using the desired colour formatting.
    Using an intermediate data set, zero out the data when the conditions are not met (keeping the whole chart invisible).

    I sure hope an Excel genius will chime in!

    this worked. Quite a workaround! I had to make the fonts of the main (underlying) graph white!

  12. #12
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brooklyn, New York, USA
    Posts
    200
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I finally got it, and I'm slapping my head a little cause it's so clear. I started with a floating bar chart (first bar = transparent) and stacked the first actual on top of that. What I completely forgot was that I could stack a 3rd actual on top of that, and 0 out the bar below. On the 2nd actual, I'd 0 out the bar above. This way I have

  13. #13
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    For what its' worth; I think I found the function you used to use for formatting.
    The PATTERN function in the old Excel 4.0 macro language would let you specify the colour (among other formatting elements) of a series.

    How it was activated - I have no idea since when the Excel 4.0 macro language was current, I was just learning spreadsheets - I hadn't graduated to programming them.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  14. #14
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brooklyn, New York, USA
    Posts
    200
    Thanks
    2
    Thanked 0 Times in 0 Posts
    sounds right, Genii!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •