Combo Box Dynamic Charting in Excel

---

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

2. Select City Names in Input range in format object dialogue box.

3. Click " Cell link " and select cell " G1 ".

4. Click OK.

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.

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)

Comparison Charts With Excel

---

Today we are going to learn how to make a chart which compares two or more data in a table. It is very useful when we want to compare between two values very easily within no time. As you will agree that comparing data with a graph is far more better than digging into a lot of data in a table. So lets get started. this is what we are going to create.

Follow these easy step by step method to create the chart as in the above image. Lets say we want to compare a list of items A to E for number of units in stock, purchased and sold.

Step 1

Create a sample data as below.

Step 2

Go to Insert > Charts > Column and then select Clustered Column chart from 2D Column.

Step 3

Select Layout 5 from Chart Layouts in Chart tools in Design tab.

Step 4

Right click on chart area and click on Select Data.

Step 5

In the Select Data source window click on Add.

Select the title "In Stock" for Series Name and Data below In Stock title for Series Values and click OK.

Notice a series In Stock is added in the Legend Entries (Series) list box. Now click on Edit button.

Select Item names A to E for Axis label range and click OK.

Similarly Add Two more series Purchased and Sold  as illustrated above for Purchase and Sold units.

Step 6

After completing above five steps you should get a graph as shown in the image.

Right click on Axis Title and choose Edit Text. Delete the text and write "Number of Units". Select the text and change the font size to 12 and the result is. . . 

Leave me a comment if you like this post.

Dynamic Chart

---

Today we are going to make our charts dynamic in nature. By dynamic I mean that the chart will update automatically on adding new data to the table. As I always say, just follow these simple steps to make your charts update as you enter new data. You can use this tutorial to create resource chart also.

Step1:- First of all open excel  and create sample data as in the image below.

 Step 2:- Go to Formula tab in excel and click on Define Names. A pop-up window opens up.

              In Name write "Day".
              In Refer to write " =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1)".
              Click OK.

              Again click on Define Names.
              In Name write "Resource".
              In Refer to write "=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1)".
              Now save your book. I saved it as "Dynamic.xls".

Step 3:- Now go to Insert > Charts > Column Chart and select Clustered ColumnType

 

Step 4:-  Go to Chart Layouts and Select Layout 8 from the layouts.  

Step 5:- Create the chart as in my previous tutorial Create a Simple Chart in Excel.

              While Selecting data do follow these steps

              In the series values write "=Dynamic.xls!Resoures". 

              Remember to replace Dynamic.xls with your own file name.

              In the Axis Label Range write "=Dynamic.xls!Day"

Note:- Confusion regarding file extension as pointed out by my friend.

For Excel 2003 "Your_File_Name.xls".

For Excel 2007 "Your_File_Name.xlsx".

Above example is as per 2003 format. 

Step 6:- Change Chart style to Style 16 and it should look like this.

Now try adding new data to your table, you will notice that it automatically updates in your graph. Leave a comment if it works.

Referred Links

Dynamic Charts (Peltier Tech Blog)