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.
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.
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.
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.
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
Then press the Okay button. Your Quick Access Toolbar will now look like this:
You will now see the Controls command appear in your Quick Access Toolbar.
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.
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:
Here is what the first day will look like in the clustered column chart:
Update: You can view part 2 here: