This article will demonstrate how to add a basic scroll bar in Excel with an example of how it could be used on your dashboard.
We’ve taken a full year of ATP tennis matches from 2015 (credit to www.tennis-data.co.uk for the source data). The dashboard is going to show tennis stats by month and the scroll bar will allow the user to flick between months. In the example we’ll just setup one pie chart to work with the scroll bar, but you would setup all objects on the dashboard to link to the scroll bar in the same way.
Here is a summary of the source data for this pie chart, to show the number of matches by surface type:
Cell J3, highlighted yellow, is the cell we’re going to link the scroll bar to for the selected month. This has to be numeric, but you can have a lookup which matches up your numbers to text. In this case, 7 = July. The values in column K then depend on J3 for their value, so it will return figures for whichever month is selected. As an example the formula for Clay courts is =VLOOKUP($J$3,$B$3:$F$15,3,0).
You can then insert a chart which looks up the values for each court for the active month.
Now all you need to do is build the scroll bar to navigate between months. If you set it to update cell J3 then the chart will automatically update as you scroll.
From the Developer tab, in the Controls menu go to Insert and select Scroll Bar (Form Control). Note: if you want the scroll bar to be displayed horizontally you should drag the mouse pointer to draw the shape rather than just clicking to insert a standard vertical scroll bar. In this example, we’ve drawn a horizontal bar.
Right-click and Format Control. Set your min and max values, what you want the incremental change to be and which cell you’re linking it to:
Now when you click the left or right buttons, or scroll the bar along, the numbers 1 to 12 will update the month. You can VLOOKUP the numbers against some more user-friendly text (the month name) and create a shape to show which month is currently selected. In this case we’ve put the shape over the scroll bar to hide it, so only the left and right buttons are visible. Giving the text shape a similar background colour makes it look like part of the bar. Here is an example scrolled for June.
Hope you found this article helpful. Please leave a comment to let us know if you’d have built this a different way!