Using Scroll Bar in Excel Charts

Aha! That eternal problem of accommodating lot of data in an Excel Chart.Lets just give it a little push in the right direction

Key Concepts:

1. Offset Function
2. Named Range
3. Scroll Bar

Requirements:

• Lots of data!

Let us zoom through them with an example

    1.Offset Function

Syntax : Offset(Reference,Row,Cols,[Height],[Width])

For Example:

Offset(E6,0,1,3,1)

In order of occurrence in the formula:

• E6: Referenced Cell.This Input specifies which is the starting point of data range.For example,In our case the cell with value "January".This is useful when we want to look quarter wise data with starting display month changing.Can be said to be the anchor for relative reference.

• 0:Number of rows to shift from the anchor cell.This option helps to shift data range on scroll bar change

• 1:Number of columns to shift from the anchor cell.

• 3:Number of row data to display. Example:We will display  quarter sales for a Toy Shop and hence 3 here specifies Data of 3 rows i.e 3 months

• 1: Number of column data to display. Example:We will display Sales column data

So what does the formula say finally,huh?

Get me data range which is starting from column beside the cell E6 i.e F6 and extending up to  data of 3 rows i.e F8.Hence the dynamic data range built is F6:F8.This shows sales data for first three months.

Now repeat the same for months range corresponding to range.The formula will minutely change and will be :Offset(E6,0,0,3,1)

    2.Named Range : Named Ranges help to encapsulate dynamic data reference.

      

     Prepare two named ranges

• Month_Data_Range :Formula=> Offset(E6,Scroll_Bar_Test!$A$2,0,3,1)
• Sales_Data_Range : Formula=> Offset(E6, Scroll_Bar_Test!$A$2 ,1,3,1)

  3.Scroll Bar:

• Insert a scroll bar from Developer->Insert->Form Controls->Scroll Bar
• Right click on Scroll Bar and click on Format Control
• In the cell link write $A$2.The cell value determines which month to display by shifting data from anchor cell by rows specified in the cell $A$2
• Also,put minimum value as 0 and maximum value as 10 in the format control tab of the scroll bar.This is because we want to display data from Jan(1 )to Dec(12) only.
• Maximum Value is 10 because in cell $A$2 it specifies to the offset formula to move data range from anchor by 10 rows and include 3 row data from $F$14 which brings the last data month to be viewed as December
• Incremental Change in format control of Scroll Bar means incremented $A$2 i.e cell linked to scroll bar by 3 when clicked on the scroll bar arrowheads.This is optional though!

Almost done!

Now, last but not the least lets insert a chart...say a bar chart for  proof of concept.

Steps 

1. Insert a bar chart
2. Right click on the chart to select data
3. Click on Add Legend Entries : 

• Series Name : Sales Value (whatever suits well ,no issues)  
• Series Values:Excel_Workbook_Name.xlsx!Named Range Name
• For Example:Series Value in the bar chart will be

          Dynamic_Dashboard.xlsx!Sales_Data_Range

     4.Click on edit Horizontal Axis Labels for axis label data

• Axis Label Range: Excel_Workbook_Name.xlsx!Axis Named Range Name
• For example:Axis label range in the bar chart will be

         Dynamic_Dashboard.xlsx!Month_Data_Range

Tryst with destiny!!! with a background of a heavy metal. Phew its done .

Launch your creation with OK button click of Select Data Source of the chart

         

Looks something like this?

And this on scroll bar change?

Please leave your email id in the comment box for me to send you the actual excel workbook.

Hyper Smash.