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)