Results 1 to 12 of 12
  1. #1
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Graph Background Colors (2K SR1)

    When using the graphics feature in excel, the graph portion where the data points are shown is typically grey. Is it possible to:

    1. Change the color from grey to something else?
    2. Change the color to multiple colors based on areas of the graph?
    Regards,

    Gary
    (It's been a while!)

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

    Re: Graph Background Colors (2K SR1)

    1. Double click the grey area to format the plot area. If you want to change the default color, format a chart the way you want and set it as default chart (Chart | Chart Type..., Set as Default)

    2. Depends on what you want. You can create another series, using another value axis (primary vs secondary). The attached workbook demonstrates some primitive effects that can be achieved with another data series.

  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Graph Background Colors (2K SR1)

    WOW - That is really cool. I didn't know you could do that. That gives me some ideas on what to experiment with. How about generating the color scheme background in the attached document where the colors are independent of the series data.

    I figured I might be able to do this using various series to get the colors where needed and then making the series transparent. Your thoughts?
    Regards,

    Gary
    (It's been a while!)

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

    Re: Graph Background Colors (2K SR1)

    None at the moment, perhaps I'll have a look later, if nobody else reacts.

  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: Graph Background Colors (2K SR1)

    Something like this?This is a stacked column, with the same y, but different X- axis

    The Y axis will adjust automatically to the levels

    Since there are 2 different X-levels (1 numbers, 1 category) you have to keep them "in sync" the range of numbers in the X- axis, must match the range of categories you plot in the 2nd x- axis.

    The data for the stacked column is in Sheet1 and is based on your "chart"

    Steve

  6. #6
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Graph Background Colors (2K SR1)

    Steve,

    Interesting.

    This is closer but not there yet. The Red, Yellow, Green color scheme is independent of the data. I managed to do this by ceating a graphic in excel and then creating a .bmp file from it and then inserting the bitmap as a picture into the graph. By tweaking the excel file before creating the bitmap, I managed to get it lined up properly. See attachment.

    Ultimately it would be better if the Red, Yellow, Green color schemes would show correctly even if the data changes.

    Your post is neat but the Red, Yellow, Green color schemes are in the wrong places.
    - Lower left corner of Green is at 1,1
    - Lower left corner of Yellow is at .9, .9

    - See attachment. Of course my system fails if the starting and ending of the graphic data points change.
    Regards,

    Gary
    (It's been a while!)

  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: Graph Background Colors (2K SR1)

    Change the data region if I misinterprested the ranges

    See attached

    The color scheme I have is independent of the data. The x- range of the chart must be same as the x-range in the "background" You will eiher have to do this manually or via macro, you can not do it with a formula (though possibly some userdefined functions might work). You would have to give me some idea of the variation of the x-axis if you are going to change this. It might still have problems "automatically" adjusting when the x-axis range is changed since there is no event that traps this.

    You could have a macro adjust the X-range so it can adjust both of them together.

    The Y-range can be adjusted as desired. (I assumed that the you won't go above 10) if so, change the data accordingly.

    The only way I can see to completely have it automated is to not use a fill, but "draw different color shading into the region, using lines. In this case the X/Y have the same scale, but this is more complicated to setup, but not too difficult
    Steve

  8. #8
    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: Graph Background Colors (2K SR1)

    Here is an example of what I alluded to.

    The range goes from 0 -2 at intervals of .005. If you want to "fill" in better, but in more divisions (.001 should work) and if you need a larger/smaller range change that, The file grows in size with the calcs. I have paste-special values all but the one row of calcs. These can be copied down if you change the range or the divisions. WIth lots of data it will start getting sluggish so choose the range and division with care.

    Steve

  9. #9
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Graph Background Colors (2K SR1)

    Hey Steve,

    Thanks for this. This is really slick and what I was looking for. I need some time to digest it to make sure I understand plotting the other three series, red, yellow, and green, via the table and data points provided.

    As to ranges, I will need to standardize them to minimize the range of the graphs; going to 10 is definitely too high. If I can standardize the x and y ranges then I should be able to standardize the form as well.

    Thanks for your help. I will post additional questions if I can't figure out how you made the entire color scheme work.
    Regards,

    Gary
    (It's been a while!)

  10. #10
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Graph Background Colors (2K SR1)

    Steve,

    A dumb question. How did you set the Second Category (x) axis lables on the graph? I can use your example but can't seem to replicate it when creating a new graph to make sure I understand how it works.
    Regards,

    Gary
    (It's been a while!)

  11. #11
    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: Graph Background Colors (2K SR1)

    Select a range of data and add them as new series to chart (make sure you indicate that the first column is X-values)
    Right click one of the ranges , Format data series - axis (tab) "Secondary axis"<ok>
    Right click on that range , chart-type - column - stacked column <ok>
    Right click on chart, chart options, axes tab - check Category (X) Axis, uncheck Value(Y) Axis <ok>
    Right click each of the other 2 color ranges and format to sec axis and change chart type to stacked col
    Right click one of the ranges, format data series, options (tab) change gap width to "0" <ok>
    Format each series to the color desired for area and border
    After ensuring that the bottom x and the topX are aligned, dbl click the upper X axis and format in pattern tab to have no tick marks and no labels.
    The Y will adjust for both ranges since they have the same axis.

    Steve

  12. #12
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Graph Background Colors (2K SR1)

    Thanks Steve,

    I missed setting the other ranges as a chart type.
    Regards,

    Gary
    (It's been a while!)

Posting Permissions

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