How-to Create a Scroll Bar in Excel to Make Your Dashboard Dynamic

This is part 1 of creating a dynamic Excel dashboard chart using the scroll bar control.

So in the title I said that we would be using a scroll bar to dynamically change the data in the Excel dashboard.  For instance, I saw that a user had daily data for his organization and he wanted to show this data daily in the Excel dashboard chart and let the user select which day they wanted to see.  So that is our use case.  To show Excel dashboard chart data by day and let the user choose the day using a scroll bar.

BUT first, we need to know how to add a scroll bar to the Excel spreadsheet.  Before you read on, take 5 minutes and see if you can find a way to add a scroll bar to your Excel spreadsheet.

Go on, look.  Open up Excel and look for a way to do it.

 





Give up???

 

Okay, now that you tried on your own, lets see how I found it.

 

My first thought is that I am trying to INSERT something.  In this case, a scroll bar.



So lets check out the INSERT Ribbon:image

I don’t see anyway to insert a Scroll Bar, do you?

Then look at every other ribbon, and see if you see anything that says Scroll Bar.  I don’t.  Sad smile

Why don’t we see it?  It is because the default options in Excel are to HIDE the Developer Ribbon.  Read on to see how you can add a Scroll Bar to your Excel spreadsheet.

 





How-to Add a Scroll Bar to your spreadsheet so that you can create a dynamic Excel dashboard:

In order to add a Scroll Bar to your spreadsheet for your Dynamic Dashboard creation, you first need to see the Insert Controls button.  To see this, you need to add the Developer Ribbon or take at a little trick that I do.  That trick is to add a Scroll Bar in your Excel worksheet is to Customize your “Quick Access Toolbar”.

The Quick Access Toolbar is the thing that you see at the top left of of your spreadsheet above above your Ribbons.  This is where you see the Save icon, the Undo and Redo icons.  You can add other tools that you frequently use and in this case the tools that you can’t find in any Ribbon.  image

So you can add any command in this Quick Access Toolbar.  I have a Insert Column Chart or Insert Line Chart as a standard command in my Quick Access Toolbar so that I can quickly create a chart of that type.  You should look through all the choices so that you can add all the commands that you frequently use.

 



How-to Add a Command to the Quick Access Toolbar

So to add a command to your quick access toolbar, you need to select the down arrow next to the Undo command at the top left of your screen:image

Then select the “More Commands…” menu option:image

Now from the Choose Commands From: drop down list, choose Controls from the left menu and then press the “Add>>” button to move it to the right:SNAGHTMLfcefa67

Then press the Okay button.  Your Quick Access Toolbar will now look like this:





image

You will now see the Controls command appear in your Quick Access Toolbar.

To insert a scroll bar, you need to choose the Controls button from the Quick Access Toolbar and then choose the Insert button.  Then choose the Scroll Bar control from the Form Controls section.image

After you choose this control, you need to create the control in your spreadsheet by DRAG image

and DROP a range in the Excel worksheet:image



You should now see this control in your spreadsheet.

Come back for the next installment of Part 2 of “How-to Create a Scroll Bar in Excel to Make Your Dashboard Dynamic” where we will create a chart that will use the scroll bar to let a user dynamically change the Excel dashboard chart.

 

Video Demonstration:

Here is a video tutorial on how to add a scroll bar to your Excel spreadsheet:





 

Also, don’t forget to subscribe to my blog and video channel so that you are sure to get the next installment delivered directly to your inbox.  Also, my video subscribers receive discounts and promotions simply by being a subscriber.  So do it already and subscribe!  Also, let me know if you know of an easier way to add a scroll bar in a spreadsheet in the comments below.

 

Here is the sample data in case you want to try it out before I post my solution:



ABCD
1CTDayINCAACD
23PI_C3/1/2013143
3955CTS3/1/201321
4APPAU3/1/2013128
53PI_C3/2/201390
6APPAU3/2/20133917
73PI_C3/3/20131115
8955CTS3/3/2013122
9APPAU3/3/2013225
10APPAU3/4/2013520
113PI_C3/5/201380
12955CTS3/5/201329
13APPAU3/5/20133917
143PI_C3/6/20134427
15955CTS3/6/20131316
16APPAU3/6/20131639
173PI_C3/7/20131325
18APPAU3/7/20132922
193PI_C3/8/20133241
20955CTS3/8/2013105
21APPAU3/8/20131518

Sheet2

Here is what the first day will look like in the clustered column chart:

Scrolling Chart

 

 





 

 

 

 

 



 

 

Update: You can view part 2 here:

how-to-make-a-dynamic-excel-scroll-bar-chart-part-2

 





Steve=True





14 COMMENTS

  1. I would like to bounce my idea of how to do this off of you…

    I made a dynamic graph with a scroll bar based on your example above. I have 6 columns of information. Days, Value 1, Value 2, Value 3, Value 4,and Value 5, starting in cell A1 (blank). There are 31 rows, for Days 1 through 31. The values were generated with =RANDBETWEEN(1,15), and then copy and pasted as values.

    Value 1 Value 2 Value 3 Value 4 Value 5
    Day 1 3 4 4 13 2
    Day 2 7 11 12 3 4
    Day 3 2 1 1 14 7
    Day 4 7 10 13 6 7
    Day 5 10 8 15 3 11
    Day 6 6 10 3 1 11
    Day 7 8 11 13 8 11
    Day 8 15 12 7 6 12
    Day 9 1 13 1 3 4
    Day 10 14 15 2 2 10
    Day 11 8 11 9 15 5
    Day 12 15 6 2 7 2
    Day 13 15 9 5 4 4
    Day 14 8 12 8 3 8
    Day 15 13 14 1 13 2
    Day 16 6 5 14 13 3
    Day 17 3 11 14 5 12
    Day 18 15 3 3 12 2
    Day 19 6 9 6 7 11
    Day 20 5 14 3 1 9
    Day 21 9 12 13 2 2
    Day 22 1 6 3 5 14
    Day 23 13 7 6 4 14
    Day 24 14 6 5 10 9
    Day 25 2 5 11 4 12
    Day 26 1 7 1 9 7
    Day 27 13 11 1 5 5
    Day 28 2 2 13 10 12
    Day 29 9 6 15 3 4
    Day 30 2 12 15 5 13
    Day 31 3 3 1 15 9

    I added a scroll bar that counted from 1 to 5. I added a cell (H2) for the Scroll Position. Then I made a 32 row by 1 column array with the following formula {=CHOOSE(H2,$B$1:$B$32,$C$1:$C$32,$D$1:$D$32,$E$1:$E$32,$F$1:$F$32)},
    which starts in cell I2. Now my array dynamically changes bases on the scroll position. Then I ran my chart off of the new array with a text box that is linked to the first value of the array…which will be the series title, so the title also changes with the scroll bar.

    Is that a good way to do it, or is there a better way to make this dynamic?

  2. As I re-read your post, I realized the data needed to be dynamic day by day, not value by value. so I made the same type of setup as I posted above, but with a horizontal 1 row, 6 column array that contained the formula {=OFFSET(A1,A37,0,1,6)}, where A37 is the cell that the scroll bar input it’s number (1 through 31). Now I have a chart that is dynamic day by day.

    • Awesome work Pete. I may have to ask you to guest post some time. You really know your stuff. The post that I will be doing is that there are multiple data points per day. I have just edited the original post so that you can try it on your own first 🙂

      Thanks for being a FAN!

      Steve=True

  3. Hi, great tutorial! After I have made my dynamic scroll bar, what if I want to print the graph in its entirety (not just what shows in the window according to dynamic scroll bar?)

    • Hi Jane,

      Thanks for the question. I would recommend creating an additional chart with the entire data set instead of the scroll data range.

      Hope this helps. Steve=True

  4. Hello, Can you please explain how you link the scrollbar to your graph? I have a simple table MONTHS, OPEN, CLOSED and want to show the graph either as per month or every quarter and then the scrollbar can show each quarter in the year. I would really appreciate a detailed explanation of how to do this if possible or could you send me your above graph including any details behind the graph?

    Thanks

    Simon

  5. I would like to find out if it is possible in Excel to insert 2 sliders into a single scroll bar? Therefore, you can control 3 variables (3 spaces in scroll bar due to 2 sliders) to save space.
    Thanks

    • Hi Vidya, it depends on what you mean by annotations? Should they be dynamic? What would they display and where would that information be placed? Need more details.

LEAVE A REPLY

Please enter your comment!
Please enter your name here