3

Imagine that you have a dataset to which an additional measurement is added each day. In Excel, this could be a table to which additional rows are added. Imagine that you plot the time evolution of a variable in a chart. Would it be possible to add a scroll bar to the chart, such that the chart shows you by default the last 14 values but allows you to scroll further back in time?

1
  • 1
    I don't think this is possible OOTB. It may be possible with VBA – Burgi Jan 11 '16 at 16:21
3

In a sense, yes, but there's no automatic way, it would be a separate scroll bar changing the source values. There is a tutorial here

To summarize the steps:

  1. Gather your overall data
  2. Create a sheet to have all the temporary working data on
  3. Add a chart
  4. Choose a field for the 'scrollbar value' that you will increment later on, in your temporary sheet
  5. Set up your source data pointing at your main data, using the scrollbar value to select the correct row in an INDEX formula, EG - =INDEX('Scrolling chart'!A2:A34,$B$1) where $B$1 is your scrollbar field. Drag this formula down to the amount of points you want on your chart.
  6. Add a scroll bar element, setting it up to increment by 1, and enter your minimum and maximum elements and reference it to your scrollbar value field
  7. Set your chart to look at your data set up in step 4

Now when you move the scrollbar, it'll change the number in cell B1 (in this example). That will then change the value in your formulas, and as such, your chart will change to suit.

To use it backwards, use negative values and a formula to find the last row in the original data set (=LOOKUP(2,1/(A:A<>""),A:A) finds the last row in column A with data in it).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.