The magic of scrollbar control in Excel

 Feb 15, 2016

By adding a scrollbar control to a Microsoft Excel sheet you can bind it to a range of values. As you scroll, the values in that range will be returned into a cell. You can now use this dynamically generated value to do something with it. Let’s take an example;
Scrollbar Control Excel
Here’s a table of annual sales per model numbers. We would like to create a dynamic chart to show the trend of sales for each year as we scroll through the years.
Scrollbar Control Excel
To do this, we need a scrollbar on the sheet first. From the Developers tab drop down Insert and select Scrollbar. Draw it in an appropriate place on your sheet. Then from the Developers tab select Properties. Set the Min and Max values and the Incremental Change. In our case we set them to 2010, 2015 and 1. In the Cell Link nominate a cell where you want the values to be returned in. In our example we set it to $H$3. As we use the scroll bar now, the values in H3 will take on any of the values 2010, 2011, …, 2015. We can now get this value and make use of it. As an example, we are going to Chart the sales for each year. This chart should be automatically updated as we scroll the scrollbar. So what we do is that we chart column H which should be a replica of the year column that appears in cell H3. So assuming I have year 2010 in H3, I type this formula in H4: =INDEX($B$4:$G$14,ROW()-3,MATCH($H$3,$B$3:$G$3,0)) And Autofill it down to get a copy of the column 2010;
Scrollbar Control Excel
As we now scroll the bar, the value in H3 changes to different years and so do the data in column H. We will now select columns Model No: and the last column (H) and insert a Line Chart. As we scroll using the Scrollbar, my chart will be updated automatically because the column H is updated. The development of the formula is up to you. Here, I just wanted to demonstrate that the value returned by the scrollbar is usually being used in one way or another.

How do your Excel skills stack up?   

Test Now  

About the Author:

Cyrus Mohseni  

Having worked within the education and training industry for over 15 years as well as the IT industry for 10 years, Cyrus brings to New Horizons a wealth of experience and skill. Throughout his career he has been involved in the development and facilitation of numerous training programs aimed at providing individuals with the skills they require to achieve formal qualification status. Cyrus is a uniquely capable trainer who possesses the ability to connect with students at all levels of skill and experience.

Read full bio
top