This post will elaborate on how to add the Scrolling functionality in an Excel Chart and convert a static chart in to something more dynamic and more user interactive. The Scrolling functionality lets you zip through the X-axis.It comes in handy when there are too many data-points (FW01,FW02…Fw30 and so on) and you want to surf through them.
So how it’s done??
Answer: Offset Function, ActiveX Controls and some user attentiveness.
First, a background on what exactly is an OFFSET Function in Excel.The OFFSET function returns a reference to a range that is offset a number of rows and columns from another range or cell.
The syntax of the OFFSET Function is explained below:
=Offset( range, rows, columns, height, width )
range is the starting range from which the offset will be applied.
rows is the number of rows to apply as the offset to the range. This can be a positive or negative number.
columns is the number of columns to apply as the offset to the range. (This can be a positive or negative number)
height is the number of rows that you want the returned range to be.
width is the number of columns that you want the returned range to be.
Based on the Excel Sheet example above
Formula: =Offset(A1, 1, 2, 1, 1)
Result: The above would return the reference to cell C2 and thus would display $7.23
So, hope you got hold of the power of the OFFSET Function. Now starting with the creation of the Chart.
1) Open Excel and fill three columns like the below Format
Here i have filled sample Revenue Figures over each month, You can replace this with your data points (Visits,Sales,Orders etc) over a period of weeks or months or specific days.
2)Now in the ‘Name Manager’ you would have to create Dynamic Ranges which would fuel these Charts and would expand or contract over user Selection.
If you have never ever heard of ‘Name Manager’. Here is a short video to explain what it is all about:
3) We will define two names here in the Name Manager:
Click on ‘Formulas’ Tab –> ‘Name Manager’ –> ‘New’
LastX: The Data selection the Chart will take
Choose the Name as ‘LastX’ and keep the scope of the Range to Workbook (The Range can be accessed through the whole workbook(any sheet in the workbook))
The Formula gos like this:
- Sheet1!$C$2 is nothing but from where you want the Offset to start. (In our case referring to the image above = $14,245)
- Now comes the important part, For the Rows to be dynamically selected we use a Count Function on Column C (where our data resides) to see how many rows are filled
- Insert a Scroll Bar (Vertical ) and horizontal Spin Button for us to understand the function of Cells E3 and E4 in the formula above
- You can go to ‘Developer’ –> Insert –> ActiveX Controls (Form Controls would also work fine, but not for Horizontal Scroll). Now Right click on the Active X Control and select ‘Properties’
- Change the ‘Linked Cell’ property to ‘E3’ along with the ‘Min’ property to 1 and ‘Max’ property to the maximum number of Bars you want to be visible at a single time. In my example i have selected it as ’24’
- Now for the Horizontal Spin Button, Keep the Linked Cell to ‘E5’ (not E4) and change the Max value to 0 and Min Value to -10. That is the advantage of Active X Controls, You can have negative values as minimum values as opposed to Form Controls.
- Now when i click on Left Spin Button, When we look at E5(which is linked to Spin Button), we see the number 65535 (and not -1), We can change this by inserting this formula in cell E4: =IF(E5=0,0,E5-65536)
- The Height is the cell E3, we want to keep the height according to the Vertical Scroll Bar selection made by user and the width will be 1 as we only have one series to cover in this example
4)LastXDate: The X-axis selection the Chart will showcase when scrolled through:
The formula for the ‘LastXDate’ in the Name Manger goes like this:
=OFFSET(‘Scroll Bar Example.xlsx’!LastX,0,-2,Sheet1!$E$3,2)
Here the Reference would be the Name Range we defined earlier with the Workbook Name preceding it. As the LastX Range starts at C2, we are giving the Column as -2 for it to return to A2 and catch the X Axis names. The height will be the same as the selection by the user (Cell E3).
Here i wanted to combine the two columns ‘Month and Year’ to make it look like Jan 2009, Hence i selected Column Width as 2 to combine the two columns. You can keep it as the same.
5) Now, we are ready to insert the Chart.
Go to ‘Insert’ –> Charts –> select a Bar Chart or whichever you like for that matter
Right Click and select ‘Select Data’ and paste the below in the Series Values:
=’Scroll Bar Example.xlsx’!LastX (‘Your Workbook name’! & The name you defined in the Name Manager earlier)
Similarly in the Horizontal Axis Labels, Paste the below Formula:
=’Scroll Bar Example.xlsx’!LastXDate (‘Your Workbook name’! & The name you defined in the Name Manager earlier for X Axis)
And Voila, Your Chart is ready to go.
I have also attached a Sample Workbook with the same working example here
Download and play around to get a better Understanding.
Thanks for reading.