In this excel 2007 tutorial I am going to show you how to create a drop down combo box to chart source data. We will create a drop down box in excel, which will show the name of the cities in the drop down list. On selecting a city in the drop down list, it will automatically create and display sales data in the form of chart in excel. So let's get started.
Create Sample Source Data.
Open excel 2007 and make a table similar to this.
Create Drop Down Combo box.
1. Go to Developer tab.
2. Click " Insert " in Controls
3. Select " Combo Box " in Form Control.
4.Hold down "Alt " and drag towards right to fill cell " H2 " completely.
Format Control for Combo Box
1. Right click on combo box and select "Format Control".
Checking Combo Box
1. Click on black triangle on combo box.
2. Select any city from drop down box.
Creating Chart with Source Data
1. Click " A10 " and type " =INDEX(A3:A6, $G$1) "and hit Enter.
The city you selected earlier from drop down box will appear in cell A10.
2. Again click A10.
3. Click on the bottom right corner of the cell and drag horizontally to auto fill all the months in row 10.
4. Click A9, hold shift on keyboard and then click on cell F10 to select all the data for charting.
3. Select " Combo Box " in Form Control.
Format Control for Combo Box
1. Right click on combo box and select "Format Control".
2. Select City Names in Input range in format object dialogue box.
3. Click " Cell link " and select cell " G1 ".
4. Click OK.
1. Click on black triangle on combo box.
2. Select any city from drop down box.
Creating Chart with Source Data
1. Click " A10 " and type " =INDEX(A3:A6, $G$1) "and hit Enter.
The city you selected earlier from drop down box will appear in cell A10.
2. Again click A10.
3. Click on the bottom right corner of the cell and drag horizontally to auto fill all the months in row 10.
4. Click A9, hold shift on keyboard and then click on cell F10 to select all the data for charting.
5. Go to Insert > Charts > Column and Select Clustered Cylinder chart.
6. Click on chart and Move it Just below the combo box.
Now try changing city from the drop down box and you will observe that it charts data for a particular city selected from the drop down box. Comments regarding improving the tutorials will be highly appreciated.
Referred Links
Combo Box Animation (Flash Lite Tutorial)
This was exactly what I needed, Thanks a lot
ReplyDeleteI have turned every stone over the internet to find this function. Thanks for this and for making super easy to apply.
ReplyDelete