Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Aug 2002
    Location
    MN, USA
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Chart color coding (2003)

    Attached you'll find a file with a stacked column chart and the corresponding data. I was asked to add in a couple vertical lines at the tic marks to the left and right of the 15-21 group. One of these lines would be yellow (left) and the other would be red (right). They would be used to help the user visually see the transition from green to yellow to red for problem report aging (the data chart has the cell background color change). How would I go about doing this?

    I'm open to other solution suggestions for the same problem, as the above method might not be the simplest. Maybe have the column labels be the corresponding color (0-7 & 8-14 are green, 15-21 is yellow, etc). Maybe change the chart background color for each column section (might need 3 charts right next to each other).

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Chart color coding (2003)

    What about the attached? It's based on an example by Stephen Bullen (FunChrt4.zip from Office Automation Ltd. - Stephen Bullen's Excel Page)

  3. #3
    Lounger
    Join Date
    Aug 2002
    Location
    MN, USA
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Chart color coding (2003)

    Thanks for the quick reply,

    You're talking about what was done for method 1, series 1 I'm assuming. For that, I'll have to make three additional data columns that only serve the purpose of being a background color for each of my three colors. Having the chart height be flexible to the actual number of Problems, but not reach the top of my background color bars will be a challenge. I'll try and mess around with it a little bit, but I'm hoping there is a more graceful way to do this.

    Half the problem is that I will never be the one using the sheet I'm making, so making it easy for someone else to update later is challenging.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Chart color coding (2003)

    I added one dummy series that is displayed in the background, no other extra series are needed. This series is not stacked with the rest, and its gap width is set to 0, so that its columns fill the plot area. I manually changed the color of the columns (data points) of this series to green, yellow or orange. I had to fix the scale of the value axis; this could be a problem if the data change.

    Perhaps someone else will have a better suggestion.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Chart color coding (2003)

    An "alternative" is to create tham as 2 different axes: the "background" as the primary (to have it plotted first) and all the "real data" (ie the stacked ranges) as the secondary axis. The primary can be "locked" to allows have the "background full" and the seconday can be automatic.

    The "problem" is:
    While is is possible to "not display" the axis for the "background", the axis for the "real data" is on the right instead of the left. I have not figured out an easy way around this.

    It would be nice to just have the "background" as the secondary axis, but I can think of no way to have the secondary data plot before the primary data...

    Steve

  6. #6
    Lounger
    Join Date
    Aug 2002
    Location
    MN, USA
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Chart color coding (2003)

    Well, I did it with the background 2-axis method and it looks pretty nice. I also just used the draw line tool to do what I originally requested and it worked just fine. I was worried about the lines not 'sticking' to the chart so if it was moved or resized the lines wouldn't scale/come with properly, but they do. I submitted both methods to the user who requested the changes and they like the background method a lot more.

    Thanks for the help!

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Chart color coding (2003)

    If you add more categories the lines will not "adjust".

    Steve

Posting Permissions

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