Scroll bars or Sliders on an Excel Chart are a great addition if you are building interactive Excel Reports and Dashboards. This guide will walk you through the steps in creating an Excel Chart with a scroll bar.
Why add a Scroll Bar to an Excel Chart?
If you and wondering why make the effort to add a scroll bar or slider to a Chart in Excel and the answer is that an Excel chart with the addition of a scroll bar or slider will provide more interactivity for the end-user and especially when charting many intervals, long time-periods for example, it will empower the end-user to find and focus on solutions that work best for them. As an Excel analyst, it also saves you time as rather than create multiple Excel charts or an unprofessional looking chart you can provide a solution that the client can adapt to their needs.
To demonstrate the concept in a better way, let us take an example of producing a sales chart for past 48 months. A basic Excel Chart covering this time frame would look something like this:
By no means is this a terrible chart, it gives you an indication of the sales trend over the 48 months but unless you plan on making the chart wider than a monitor display, something I strongly advise against, it is not very user-friendly if the end-users has a need to focus more specifically on certain data points. Imagine if there were 120 months to plot or perhaps daily sales for a few years leading to thousands of data points, it would be almost impossible for the user to focus on the detail.
Consider for our example of 48 months if we look at the same chart but just with 12 months in view:
As you can see each of the 12 months has a clear data point on the chart which will make analysing the sales easier for the end-user, and usability is a key factor a good analyst should consider when producing winning Excel Dashboards or reports.
Creating an Excel Chart with a Scrollbar
Step One: Define the Aim
Knowing the aim of a task is always crucial, you can’t begin your journey without knowing your destination. For this example, the aim is to take 48 months of sales data and produce an Excel chart with a scroll bar. The end chart will show 12 months of sales data in view at all times and allow the user to scroll through and move the date period on one-month at a time.
Step Two: Arrange your Excel Workbook
To make things clear the original dataset containing 48 months of sales data is placed on a worksheet called Data. Column A contains the Date Period and Column B contains the corresponding Sales Value for that Date Period.
In addition to the Data worksheet another Worksheet is labelled as Dashboard and this is where the chart and 12 month data table will go when created.
Step Three: Set up the Dashboard Worksheet
To produce a scroll bar on an Excel chart we must take a subset of the data and produce a chart against that subset. The subset will automatically recalculate when the scroll bar position is changed. Taking this into consideration we need to know the following:
- The Date Value from the Subset
- The corresponding Sales Value from the Subset
- The position of the scroll bar
For this example the aim is to chart 12 months of data at all times so we need to create a table for those 12 months to go in. We will also need a reference cell that will capture the position of the scroll bar.
Working on the Dashboard worksheet:
- Cell A1 is the text “Scroll bar Position”, the position will be captured in Cell B1 next to it.
- Cell A3 and B3 are the headers for our data table and to make things neat and tidy I have boxed around 12 cells below that (down to A15 and B15). This makes sure I remember to capture the full 12 months which was part of the aim.
Step Four: Add the Scroll Bar
The next step is to add the Scroll Bar to the Dashboard Worksheet.
- Click on the Developer tab on the Excel Ribbon
- Click on Insert within the Developer tab
- Click on Scroll Bar within the Insert box
- Draw a Scroll bar on the Dashboard Worksheet by holding down the left mouse button and dragging across. Make it a reasonable size to begin with as you can always resize when you complete any final tidying up at the end.
No Developer Tab? If you do not have the Developer tab showing then you can add this via Excel Options in a seconds, this post here will show you how.
Step Five: Set the Scroll Bar Control Options
Right-Click on the Scroll Bar and select Format Control from the pop-up box. This will open the Format Control options box, ensure you are on the tab called Control:
Note about Increments: The controls for a scroll bar work in Increments. Each click of the scroll bar will move the increment up or down depending on the way you scroll.
With the Control tab we will need to change the values, do so as follows:
- Current Value: This is the current increment value and will generally be set to 1 for any work that you do. Set this to a value of 1.
- Minimum Value: This is the lowest increment number in the dataset, again it will often be 1. Set this to a value of 1.
- Maximum Value: This is the maximum increment number in the dataset so logic would dictate that with 48 months it would be set to 48 however this is where we need to take a step back and think.
The aim is to show 12 months of data at all times on the chart so if we allow a user to scroll to position 48 that will be the 48th month in our dataset, there is no more data after so the chart would end up showing 1 month of data followed by 11 blank positions. To get around this problem we can use a simple equation to calculate what the maximum allowed increment should be:
Maximum Value = Total no. of increments – (No. of increments to display on chart – 1)
Applied to this example we have 48 increments and want to display 12 increments at all times on the chart, therefore the equation is:
Maximum Value = 48 – (12 – 1)
Therefore set the Maximum Value to 37.
- Incremental Change: This is the number of increments the scroll bar will move with each click. Set this to a value of 1.
- Page Change: This is the number of increments that will be shown on that chart. Set this to a value of 12.
- Cell Link: This has to be a blank cell on your worksheet, it is where the current increment value will be captured so that you can use it in your formulas to display the right data. Set this to $B$1 as we have already prepared a blank cell for it in step three of the process.
When you have set all the values as quoted above, the options box should look like the below image. If all is correct click on OK.
After you have clicked OK you will see the Dashboard worksheet in full again. Notice that cell B1 has now been populated with the value 1. If you move the scroll bar right or back left you can see this value change depending on what increment you move the scroll bar to.
Step Six: Populate the 12 month subset data table
With the scroll bar created and options set it is time to populate the 12 month data table. Let us consider what we are trying to achieve.
We need the table to contain 12 dates and their corresponding sales value from the Data worksheet, which is the original source, and the 12 months of data must start depending on what increment the scroll bar has been scrolled to.
If for example the scroll bar has been moved to increment number 12 then we need our subset data table to start taking values from the the 12th row of the original source. This is why the scroll bar selection value in cell B1 of the Dashboard worksheet is important, we can use that to tell Excel what row to start picking up data from.
To lookup values from original data range and based on a row number we can use the INDEX function. The INDEX function takes the format:
=INDEX(Array, Row Number, [Column Number])
Note that because the Column Number is in square brackets it means this is an optional argument in the function, we do no need it for this example, we just need the row number.
On the Dashboard worksheet select cell A4 as this is the first cell in the subset data table. In cell A4 type the following formula:
What this is telling Excel is the Array, or range of data we want to use, is on the Data worksheet in cells A2 to A49 and the Row Number we want to use for the is on the Dashboard worksheet in cell B1. We do not lock cell A2 (no dollar signs) as when the formula is dragged down the remaining cells we want that reference to move, this is how we will pick up all 12 months in the table.
When you have entered the formula drag it down the remaining blank cells in the subset table, you should see Jan-13 to Dec-13 populated in the cells.
Note: To drag down a formula into the adjacent cells left-click on the lower right corner of the cell, keep the mouse button pressed down and drag the cursor down the cells in question. This will copy the formula.
Note: The dates may not show in MMM-YY format if you have started this process from new instead of using the downloadable file. This is simply a formatting issue that can be resolved by highlighting the date cells in A4 to A15 and creating a custom format of MMM-YY.
When you have completed the dates the process can be repeated for the corresponding sales values. In Cell B4 of the Dashboard worksheet enter the following formula:
Notice the only change is that in this instance we refer to the sales values that can be found in cells B2 to B49 of theData worksheet. Again drag this formula down and you will be left with a fully populated 12 month subset table on the Dashboard worksheet.
The subset table containing 12 months of data is now complete. You can check everything is working properly by scrolling on the scroll bar. Move it to position 13 (check cell B1 on the Dashboard worksheet shows the value 13 after scrolling to the right) and you will notice that the table has rolled on to show values of Jan-14 to Dec-14.
Step Seven: Create a Chart from the subset table
Now that the scroll bar is complete and the subset data table is populated correctly you can move onto to creating a chart using the subset data table.
By using the subset data table to create the chart it means that the Excel chart will automatically change when the subset data table changes, and that changes when the scroll bar is changed so everything works in conjunction to give you an Excel Chart with a Scroll bar.
As with all things Excel there are many enhancements you can make to the chart. A few things you might consider:
- Hide the scroll bar position cell or place on a separate worksheet to the chart. There is no need for the end-user to see this and you would not want them overwriting the cell, if this stays in view then make sure you lock the cell and protect the worksheet to prevent changes.
- Move the subset data to another worksheet or hiding. When you are creating detailed Excel Dashboards the space you have available, often referred to as real estate, is always at a premium and the Excel chart with a scroll bar is usually enough information to show.
- Dynamic ranges and titles are useful if your dataset will continue to grow on a regular basis. In the long-run it will save you a lot of time especially if you have multiple charts and tables in a Dashboard.
Check out exclusive Course on Excel Charts ; Dashboards here.