Results 1 to 9 of 9
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Scrolling Charts (Excel 2000 >)

    Hi all,
    Here is a great trick to make a chart interactive with a scrollbar. This can be really handy in a presentation where you want to show progress in sales etc, one series at a time.
    See the attachment for a working example and the info to create it yourself!
    Let me know if this is useful for you. Always great to get some feedback!
    Regards,
    Rudi

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

    Re: Scrolling Charts (Excel 2000 >)

    That's very nice. If you have monthly data for a longer period, say four years, a variant would be to use the scroll bar to display a 12 month "window" into the 48 month period.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Scrolling Charts (Excel 2000 >)

    Absolutely.
    All it would cost is to go to the properties of the scrollbar, and change the max value and the increment!
    Will work well for most periods.
    Regards,
    Rudi

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

    Re: Scrolling Charts (Excel 2000 >)

    Attached is a version showing a scrolling 4 month window into a 24 month period. Looking at the definition of the named ranges will show how it is done.

    (I fixed the minimum and maximum value of the value axis to make it easier to follow the chart as you're scrolling.)

  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: Scrolling Charts (Excel 2000 >)

    Nice tip.

    I use a "variant" of this tip to have a combobox select the dataset to choose (the column) when you have lots of different values to plot (x is time, Y might be various expense classes, physical properties, etc)

    Instead of having (eg) 15 graphs using the dame X and different Ys, I use 1 chart with a combobox to choose the Y value. The big advantage is that only 1 chart has to be modified, not all 15, in case you want to change the format.

    One very important point to emphasize (You do mention it in your description), is that whether a scroll bar, combobox, spinner, etc is used (or combinations) they must be from the FORMs toolbar, so you have limited formatting of it. The items from the control toolbox can not be added to chart objects.

    Steve

  6. #6
    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: Scrolling Charts (Excel 2000 >)

    For other users I will also mention (I am sure you are aware of this, Hans):
    There are lots of variants of this depending on your needs. You could have 2 scrollbars one to move thru the "periods" (like this example which plotted each separate 4 month period) and the other to change the size of the window (4 month, 5 month 6 month, etc)

    Steve

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Scrolling Charts (Excel 2000 >)

    This is a very wise modification when you are dealing with large volumes of info, otherwise the chart can get very crampt. I'm going to save this into my tips directory for future reference.
    PS
    If you're a surfer and miss the waves on the ocean, just click the scrollbar and hold, then you get the effect of waves that should satisy those cravings!!! :-)

    Thanks Hans, nice improvement!!!
    Regards,
    Rudi

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

    Re: Scrolling Charts (Excel 2000 >)

    Good point. Once you understand how this technique works, it is easy to extend it in different ways.

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Scrolling Charts (Excel 2000 >)

    Thanx Steve,
    I have seen that variant in action too. (Using a combo box to display different data on the same chart).
    That too is a nice format based on the induviduals needs!
    If anyone is interested in the steps to create this form of chart, see the attachement which documents the steps to set this up!
    Regards,
    Rudi

Posting Permissions

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