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