How-To Make a Dynamic Excel Scroll Bar Chart Part 2

Excel Scroll Bar Control
Excel Scroll Bar Control

Wow, it has been over 2 years.  I am so sorry if you have been waiting all this time.  Probably not, but I will finish what I started with Part 2 of How-To Make a Dynamic Excel Scroll Bar Chart.

Here is a link to Part 1: how-to-create-a-scroll-bar-in-excel-to-make-your-dashboard-dynamic

 

The Breakdown

1) Insert a Scroll Bar Form Control





2) Create Calculations for Chart Series Offset Formulas

3) Update Scroll Bar Form Control Formats

4) Create Offset Named Formulas

5) Create Column Chart

6) Edit Chart Legend Entry (Series) and Horizontal (Category) Axis Labels



7) Insert Custom Chart Title

 

Step-by-Step

1) Insert a Scroll Bar Form Control

I have created a whole tutorial on this topic.  Check out Part 1 to learn how to do this:





Part 1: how-to-create-a-scroll-bar-in-excel-to-make-your-dashboard-dynamic

After completing this, you will now have a scroll bar displayed on your worksheet.

Excel Scroll Bar Control
Excel Scroll Bar Control

 

2) Create Calculations for Chart Series Offset Formulas

An Excel scroll bar will need an area to store the current selection/value.  We will simply place a few data cells next to our original data set.



Here is the sample data for this tutorial:

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
 I will add a few data points in cells F3:G7 that will make our chart Offset formulas easier to understand.
In Cell G3, we will want to find the minimum of our dates so that we can use this to calculate the Scroll Bar selected date.
We have designated Cell G4 to hold our Scroll Bar selection.  We will use this in Step 3 below.
Cell G5 will equal our selected scroll bar date by adding the Scroll Bar value plus the minimum date in our data.
In Cell G6, we will find the first matching value in our data that contains the Selected Date.
Cell G7 equals a count of the number of instances of the Selected Date in our data .
NOTE: This technique requires your data to be sorted on the Date Column.
Here are the formulas for our data:
FG
3Minimum Date3/1/2013
4Scroll Value1
5Selected Date / Chart Title3/1/2013
6Date Row2
7Row Count3
Worksheet Formulas

CellFormula
G3=MIN($B$2:$B$21)
G5=G3+G4-1
G6=MATCH($G$5,$B:$B,0)
G7=COUNTIF($B$2:$B$21,$G$5)

 

3) Update Scroll Bar Form Control Formats

Now would be a good time to update our scroll bar formats.

To do this, right click on the Scroll Bar and click on the Format Control… menu item:





Scroll Bar Format Control Menu
Scroll Bar Format Control Menu

From there, you will then see a dialog box for the Scroll Bar.  Change the values of the Scroll Bar as follows:

Scroll Bar Format Control Values
Scroll Bar Format Control Values

a) Current Value = Any Number between the Minimum Value and the Maximum Value

b) Minimum Value = I like to set it to 1 or 0.  This will affect our formula for the Selected Date.  If we put in 1, then we will need to subtract 1 from that formula.  If we choose zero, then there is no need to subtract 1 from that formula.

c) Maximum Value = 7 or 8.  This should be equal to number of dates that we have in our data set minus the minimum value we put in b) above.

d) Incremental Change =1 so that we move our dates on a day by day basis.



e) Page Change doesn’t matter in this case as we have a very small data set.  If we had a larger data set, then page change affects how the scroll bar value increments when you click on the bar vs. the arrows.

 

4) Create Offset Named Formulas

Now that you have your scroll bar set, we can create the formulas that we will put in our chart.
Before doing this, I recommend that you check out this video to learn more about the Offset Function.

For our Offset formula, we need to create 3 Defined Name formulas for the Chart Series Ranges and Horizontal Axis.

To do this, go to the Formulas Ribbon and Click on Define Name:





Define Name Button
Define Name Button

Then give your new Defined Name a Name and type in your offset formulas as follows:

ChartSeries1=OFFSET(‘Original Chart’!$A$1,’Original Chart’!$G$6-1,2,’Original Chart’!$G$7,1)

ChartSeries2=OFFSET(‘Original Chart’!$A$1,’Original Chart’!$G$6-1,3,’Original Chart’!$G$7,1)

ChartSeriesNames=OFFSET(‘Original Chart’!$A$1,’Original Chart’!$G$6-1,0,’Original Chart’!$G$7,1)

After you have created the chart series and axis Defined Name Formulas, we are ready to create our chart.



 

5) Create Column Chart

To create our dynamic chart, we want to first create a sample Column Chart that we will modify with our dynamic named formulas.

First, create a sample chart by highlighting the same data that would normally use to create a chart for 1 increment of the scroll bar.  In this case, we will highlight 1 days worth of data as you see here:

Highlight Chart Data Sample
Highlight Chart Data Sample

Then Insert a 2-D Column Chart from the Insert Ribbon:





InsertColumnChart_thumb.png

Your chart will now look like this:

Excel Column Chart Sample
Excel Column Chart Sample

 

6) Edit Chart Legend Entry (Series) and Horizontal (Category) Axis Labels

Now we need to change the static chart series with a dynamic offset defined name formula.
Before you move to this step, I highly recommend that you check out this post and video:
Add Offset Series Defined Name and Offset Series Name to Chart

To edit our static chart series, first click on the chart, then click on the Design Ribbon and then on the Select Data button:



SelectDataMenu.png

Then from the Select Data dialog box, click on the first series and then click on the Edit button on the Legend Entry (Series) area:

Edit Series 1
Edit Series 1

Change the Series Values to the Defined Name Offset Formula for ChartSeries1 you created above.

Edit Series Values
Edit Series Values

Repeat this step for ChartSeries2 you created above.

Edit Series Values2
Edit Series Values 2

Then Change the Horizontal (Category) Axis Labels to the Defined Name Offset Formula for ChartSeriesNames you created above.





Edit Horizontal Category Axis Labels
Edit Horizontal Category Axis Labels
Edit Axis Label Range
Edit Axis Label Range

Your chart should now look like this when you scroll to March 7:

Final Scroll Bar Chart
Final Scroll Bar Chart

 

 

7) Insert Custom Chart Title

Finally, I feel that it is a good idea to tell the chart reader what data they have selected.  I do this by changing the Chart Title to a dynamic link to a cell value.  You may need to insert a Chart Title first from the Layout Ribbon.  Then set the value of the Chart Title = $G$5

Here is a tutorial on this step:

Your chart should now look like this when you scroll to March 7:
Final Chart with Dynamic Title
Final Chart with Dynamic Title

Video Tutorial

Here is a demonstration of the Excel techniques shown above:

 

Sample File





Download the free sample Excel file here:

How-To-Make-a-Dynamic-Scroll-Bar-Chart-Part-2.xlsx

 

Thanks for the wait.  What chart tutorials should we do next?  Let us know in the comments below.

Steve=True





2 COMMENTS

LEAVE A REPLY

Please enter your comment!
Please enter your name here