Results 1 to 13 of 13
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    hide empty range from Chart legend (Excel 2003)

    I have a chart where the data is not fixed in that it can be from 1-7 data series. If the user provides a name for the data series, it shows up in the chart but if they choose to only use say the first 4 data sets, the last 3 are empty (name is empty, data contains all 0's). When I plot the data, I can't figure out how to show the correct number of series names in the legend. The series that are "blank" show as just a color legend dot, no name).

    I can't use VBA in this model so need some other trick to only show a legend that contains non-blank data series names. Any ideas?

    Sample where "" means there is no series name since it hasn't been identified by the user:
    <pre>Data1 44 88 99 110 150
    Data2 22 33 55 99 122
    Data3 65 88 91 114 131
    "" 0 0 0 0 0
    "" 0 0 0 0 0</pre>

    The chart shows 5 items for the legend, first 3 have the names shown, last two just have colored dots.

    Thnx,
    Deb

  2. #2
    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: hide empty range from Chart legend (Excel 2003)

    You can remove the points from the chart by using IFs and replacing with =NA() rather than zero. A #NA error will not plot.

    You can remove the legend label, but not the marker with a formula approach. it would either require VB or manually removing the item from the legend.

    Steve

  3. #3
    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: hide empty range from Chart legend (Excel 2003)

    Upon further thought a "workaround" may be to create your own "legend" in worksheet (you would have to manually create the "markers" as pictures). Then add a "picture" of this range as an object on the chart. The chart range could be created with a dynamic range so it would grow/expand as the data set does.

    Caveats:
    1) it requires that the "empty" sets are always at the end
    2) changing markers/color on the chart will not change the markers on your legend. You have to change these manually by changing the pictures you create.

    Steve

  4. #4
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: hide empty range from Chart legend (Excel 2003)

    Thanks for both ideas... it seems there isn't a non-code way to do this as I suspected (and I can't hide rows w/o code which also solves the legend problem) so I'll leave it as is. For now, if they do not define all 7 data sets, the default name is "Not Defined" so the legend will show up with that name for all the empty data sets.

    Thnx,
    Deb

  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: hide empty range from Chart legend (Excel 2003)

    The second solution is a "non-code way"...

    Steve

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: hide empty range from Chart legend (Excel 2003)

    Can you post a censored sample of the chart and data as it is set up currently?
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: hide empty range from Chart legend (Excel 2003)

    Here's a sample with made-up data. I found out yesterday I may have to add a feature which I can only implement with VBA so if that's the case, I can easily solve this problem w/code. I was hoping the legend would not appear if its data series was 0 but it doesn't work that way.

    Thnx,
    Deb

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: hide empty range from Chart legend

    The attached chart isn't quite as colourful as the original, but does it work for your purposes?
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    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: hide empty range from Chart legend (Excel 2003)

    Here is my non-macro approach.

    Personally I don't like the changing "label size", it possibly could be adapted to get it more the same size without code, but it will get more complicated since the "picture" is always the same size so the range must be changed with some conditional formatting. It is also still a little "weird" that even with 1 you still have the entire x axis used for all 7 items (this is "by design" as is the legend labels, since you have 7 ranges).

    But if you going to use a macro, I won't work out the details...

    Steve

  10. #10
    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: hide empty range from Chart legend (Excel 2003)

    Here is a modifications of Rory's chart with all the ranges a different color. [I just changed each bar to a separate color, by editing each individually]

    Steve

  11. #11
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: hide empty range from Chart legend (Excel 2003)

    This is getting better and better! I definitely learned a few new tricks to charting.

    Thanks again,
    Deb <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: hide empty range from Chart legend (Excel 2003)

    Nice - I must admit I just assumed that, when the range changed, any missing columns would lose any custom formatting. Good to know they don't!
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    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: hide empty range from Chart legend (Excel 2003)

    You must never assume, especially with something easy enough to test <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    I had never used the technique with a dynamic range and I thought to try it. It would not have surprised me that it got "reset", but you never can be sure of what things are saved and I was pleased to see that the changes are saved.

    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
  •