How to Build Dynamic Charts in Excel using OFFSET Function

Applies to Excel 2007 and 2010

Report writers having to edit the Source Data of their graphs every time they want to update their graphs face quite a daunting task especially if the the columns to be updated are many. More important is building  a chart that is automatically updated as you add new information to an existing chart range in Microsoft Excel.

This can be done by using defined names that dynamically change as you add or remove data with the OFFSET Function.

Suppose we use TB Cases (Type in Column A header "Month" with Jan - April) and  Column B "TB Cases" with 9, 11, 6, 25

Download the Workbook

Step I:  Create an Offset function that will dynamically update values in both Column A and B.

1. On the Formulas tab, click Define Name in the Defined Names group (Press F3 and click new) 
2. In the Name box, type Date.
3. In the Refers to box, type =OFFSET($A$2,0,0,COUNTA($A:$A)-1), and then click OK
4. On the Formulas tab, click Define Name in the Defined Names group. (Press CTRL+F3 and click new) 
5. In the Name box, type TB_Cases (Use an underscore - no spaces or special characters) 
6. In the Refers to box, type =OFFSET($B$2,0,0,COUNTA($B:$B)-1), and then click OK
7. Clear cell B2, and then type the following formula: =RAND()*0+9

This formula uses the volatile RAND function. The formula automatically updates the OFFSET formula that is used in the defined name "TB_Cases" when you enter new data into column B. The value 9, which is used in this formula, is the original value of cell B2.

Step II: Insert Graph 

1. On the Insert tab, click a chart, and then click a chart type.
2. Click the Design tab, click the Select Data in the Data group
3. Under Legend Entries (Series), click Edit.
4. In the Series values box, type =Sheet1!TB_Cases, and then click OK.
5. Under Horizontal (Category) Axis Labels, click Edit.
6. In the Axis label range box, type =Sheet1!Date, and then click OK.

Step III - Test the graph

 Enter in Cell A6 the date "5/1/2012" and 30 on  Cell B6 the press enter - the graph dynamically add the Month and the data. If you delete the data, the graph shrinks and vice-verse

 ---------------------------------------------------------------------------------------------------------

Training your staff today email us info(at)opencastcast-labs.com go to www.opencast-labs.com.

Download Free books here

--------------------------------

David Kandie is founder and managing partner at OpenCastLabs. He provides training and consulting services to emerging and midsize businesses, helping them achieve a higher level of success through custom report development and productivity training.