Dynamic Excel Charts using Drop Down List

Hello Amigos,
Hope the day has been good so far!Why not make it awesome by learning something new?.

Lets us learn how to make Excel chart Axis labels and Series Values dynamically selected ,using drop down lists.Sounds refreshing ?Hell yeah!

Key Concepts:

1. Offset Function and  Named  Range  : Explained Earlier. Link=>
Offset and Named Range Concepts Background
2. Drop Down List

Key Requirements:

• Good 2 D data spread i.e Just another way of telling lot of data :D ,say Sales of ABC toy shop across different toy categories from January to December

Data Snapshot:

Named Range used:

1. Month_Data_Range : =OFFSET(Drop_Down_List_Test!$L$6,0,0,12,1)
2. Toy_Category_Sales_Data_Range : =OFFSET(Drop_Down_List_Test!$L$6,0,Drop_Down_List_Test!$A$4,12,1)

• Significance (Scroll_Bar_Test!$A$4): This input in the offset function shifts through various toy categories

Steps for creating Dynamic Chart with Drop Down List

1. Inserting Drop Down List (Also called Combo Box):

• Developer->Insert->Form Controls->Combo Box
• Right click on combo box , click on format control and input the following values

               Input range : $A$10:$A$17

               The range specifies distinct text values denoting toy categories

               Cell Link : $A$4  

              The toy category selected is reflected in the cell $A$4 which is then used by
               Named Range 'Toy_Category_Sales_Data_Range' for loading data of the relevant
               category

       

     Format Control Snapshot

    2.Insert a Bar chart

• Right click on the chart to select data
• Click on Add Legend Entries : 

         Series Name : Sales Value by Toy Category (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!Toy_Category_Sales_Data_Range

    3.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

Clap Clap Calp! for yourself .Your done and how? .Now look smart and make some fantastic excel chart,leaving peers far behind

Oops forgot something in the appreciation mood!

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

Chart Views:

1.Option Selected:   Games Toy Category

2.Option Selected:   Puzzles Toy Category

Please comment with your email id for receiving the actual workbook

    

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.

Excel Dynamic Dashboards BI simplified

With the breakthrough Excel power making BI for SME's an easier option.Let me show you one of the dynamic dashboards.This is first in series of many more powerful dashboards to come.Please leave your digital footprint as Email ID if you want to go through the actual dashboard which will be mailed to you.I am an avid excel worshipper.Excel has not only made my data analytic s easier but also lets me go home earlier.So all the dashboarders out there please review it and provide valuable feedback.


The Dashboard show above closely works with VBA and has total sustainable model.It loads its own data and also provides cleaning check.

Awaiting Inputs!!!