Excel – Create a Dynamic Scrolling Chart
In a couple of my other blogs, I have shown you how to create dynamic ranges ( http://wp.me/p2EAVc-9Y ) and also how to use these to set up a 12 month rolling chart ( http://wp.me/p2EAVc-ah ). This time I am going to show you how to create a dynamic scrolling chart.
By that, I mean a chart that can cover many (100s, 1000s) of data points but only show a limited number at any one time and the user uses a scroll bar to select which data is displayed. This type of chart allows you to scroll through years’ worth of data but only show a set number of data points but only using a single chart.
Here is what we are going to create…
To achieve this we are going to have to combine a number of techniques. So…if you are not familiar with the OFFSET function to create dynamic ranges, then check out this blog first ( http://wp.me/p2EAVc-9Y). If you are not familiar with creating a dynamic chart then check out this blog ( http://wp.me/p2EAVc-ah ). Without this prior knowledge, setting up this scrolling chart will not be obvious…to say the least.
Let’s say I have 8 years of data. If I plot all of it on one chart it’s going to be messy, and not easy to read – too many points and not enough labels!
All data points showing
It would be so much easier to read if there were only 12 months of data visible for example, but I need to look at all my data over the full 8 year period and beyond as I add more data.
Create your chart, including all your data…we will make changes later to convert it into the scrolling dynamic chart we want.
As always, I am going to create my dynamic named range to cover the data points first but to start with I am going to add in my scroll bar as I will need its control cell as part of my dynamic range formula. Place it wherever you want…I quite like to put it directly above or below my chart area.
To add a scroll bar, make sure your developer tab is turned on. From the FORM CONTROLS, select the SCROLL BAR.
Form Controls window
Click and drag your mouse pointer to draw the scroll bar.
Chart with scroll bar underneath
Right click on your SCROLL BAR and select FORMAT CONTROL.
Format control window for the scroll bar
In the CONTROL tab of the FORMAT CONTROL window, set the start value to 0. This may vary slightly depending on where you start your dynamic range from, but in this example, the starting point of my dynamic range is the first data cell in the column so I will leave the default value 0.
Your maximum value will depend on how many data points you have all together. I have left it here at the default 100, but change this according to your own data.
Now assign a cell to link to your SCROLL BAR. This cell is important as this stores the value generated by moving the bar to the left or right. When you close the window you should see 0 in the cell you selected. In this example, I have chosen $G$4
In another cell (again, does not matter where) enter the number of data points you want to see in your chart. For this example I have chosen 12 but you can set any number you like, and in fact this can be changed as you use the chart.
Now to create the dynamic ranges. As always I prefer to set the data range first and base my labels and any subsequent data series off that one.
For the data points, my formula will be as follows;
Dynamic range formula for the data
The start row for my dynamic range is set by the value in the linked cell. Like that, as I move the SCROLL BAR right or left, it sets the first row to start that many rows away from B2. The HEIGHT of my data set to use in the chart is set by the value I have manually entered in G4.
Now for the dynamic range to calculate which labels to use;
Dynamic range formula for the labels
Again if you are unsure as to why the formula is set up like this refer to my other blog “Excel – Create a 12 Month Rolling Chart” ( http://wp.me/p2EAVc-9Y).
The final step is to edit all the series and label range references, replacing them with the dynamic range names just like we did in the 12 month rolling chart.
Edited data cell references
Edited label cell references
You should now have a fully dynamic and interactive chart. All you have to do now is use the scroll button to view all your data.
The final chart with only 12 data points
Posted on July 16, 2014, in Charts, Functions & Formulas and tagged dynamic charts, msexcel, OFFSET function, scroll bar, scrolling chart. Bookmark the permalink. 14 Comments.
Excelmate 
Firstly, I’ve learned so much from your blog. Thank you for all you do.
My question: I’m using dynamic ranges with horizontal data to create a dynamic scrolling chart. *Is there any way to show the dynamic range data below the chart as it scrolls?*
Thanks in advance.
Thanks for the comments. I hope to add more material soon. As for your question I’ll have to have a little think. I’m travelling at the moment for work but will take a look as soon as I get a chance.
Here’s what you need to do;
Let’s say we are displaying 12 values at a time in the chart
Under your chart highlight 12 cells in a row.
While the cells are highlighted type =Name of your dynamic range
Make sure you do not click anywhere while doing this.
Once you’ve typed in your formula press Control + Shift + Enter. This then becomes an array formula and will populate the rest of the highlighted cells with the contents of your dynamic range.
Repeat for the labels…job done.
Now when you use the scroll bar, not only does the chart change but so does the table below the chart
Is there a way to make the maximum value on the scroll bar dynamic? To automatically adjust when data is added?
It can be done with vba easily enough I’ll check if there’s a way of doing it without and post a reply for you
Here’s some code that would update the maximum value of the scroll bar. Not sure how much vba you know but you’d need to get this code built in the workbook or as a macro assigned to a button to update the value manually. Other adjustments would include the correct column to check for number of values.
Sub DynamicMaxScrollBar()
Dim MaxS As Integer
MaxS = WorksheetFunction.Count(Columns(2))
With Selection
.Value = 1
.Min = 1
.Max = MaxS
.SmallChange = 1
.LargeChange = 10
.LinkedCell = “$L$1”
.Display3DShading = True
End With
End Sub
Thank you very much. I haven’t dabbled in vba, but I will give it a try. 🙂
Hi is there a way to get a scrollbar to simultaneously control two graphs with different data? So when you scroll, both graphs move?
I’ve just tried it and create a scrollbar as normal. When you create your dynamic ranges include the same reference cell from the scrollbar for both data sets
OK I’ll try that – thanks for your help!
Hey there, I now have a lovely chart controlled by a scrollbar, but I want the client to receive it as a pdf rather than an excel sheet, whilst retaining the scrollbar activity. Is this possible?
You can create interactive features in pdf files through the likes of Adobe indesign but I don’t think you can transfer the interactivity of excel into a pdf. If I find anything I’ll let you know. Good luck with that one. Is this a question of restricting visibility of the raw data? Using protection across the worksheets and workbook will stop most people but hacking add ins are easily available to remove passwords. It’s a question of trust after that
Hi thanks for your reply.
The reason why I’m thinking pdf is more that I can make it look more professional (I think), and that I just don’t like the client being able to scroll around. However, if it’s not possible to pdf the chart whilst retaining the functionality, maybe I just need to work on getting the excel workbook looking nice and protecting the relevant parts.
Without programming Excel is the only thing that has the calculation powers for my needs.
Think that’s your best bet. Good luck with the customer