Create a Dynamic Chart.

This example has been contributed by Debra Dalgleish, Excel MVP.

Excel Dashboards by ExcelUser.com - Instructional eBook and Ready-to-Use Workbooks.

How do you create a chart from a data range that will frequently change in size? Perhaps you collect weekly data, and would like the chart to automatically expand as each week's data is entered.

I took the following sample data and created a column chart. Although there is no data for weeks 5 and 6, the labels are included in the chart's x-axis, because the chart's source data range is $A$1:$B$7.

	A	B
1		Units
2	Week 1	12
3	Week 2	22
4	Week 3	25
5	Week 4	18
6	Week 5
7	Week 6

To limit the chart to existing data, and have it expand automatically, you can create dynamic names.

From the Insert menu, choose Names, then choose Define. This dialog box will pop up:

Define Name Dialog Box

In the Names in Workbook box, type a one-word name for the range, starting with the SheetName, e.g. Sheet1!ChartValues

Note for Excel 2007 users: This technique will fail if your Names begin with the word "Chart". Use a name like ChtValues instead.

In the Refers to box, use the OFFSET function to create a range that is one column wide, and contains the number row equal to the number of entries in column B, minus 1 (for the Column Heading)

In this example, the formula is:

=OFFSET(Sheet1!$B$1,1,0,COUNTA(Sheet1!$B:$B)-1,1)

Next, create a dynamic name for the labels.

From the Insert menu, choose Names, then choose Define. In the Names in Workbook box, type a one-word name for the range, starting with the SheetName, e.g. Sheet1!ChartLabels

Note for Excel 2007 users: This technique will fail if your Names begin with the word "Chart". Use a name like ChtLabels instead.

Define Name Dialog Box

In the Refers to box, use the OFFSET function to create a range that is one column to the left of the ChartValues range:

=OFFSET(ChartValues,0,-1)

Finally, change the chart's data source.

Click on the column series to select it.

In the Formula Bar, change the cell references to the named ranges.

Series Definition in Formula Bar

The new formula in this example is:

=SERIES(Sheet1!$B$1,Sheet1!ChartLabels,Sheet1!ChartValues,1)

The chart will adjust automatically to show the existing data, and their matching labels.

Dynamic Column Chart

There's your dynamic column chart, just the way you wanted. For a zipped workbook that shows this technique, click here.

Here are more Dynamic Chart resources on this web site:

Several examples of dynamic charts:

For a partial list of internet links to dynamic charting, click here.

Alse see Dynamic Charts on the PTS Blog.

Excel MVP Debra Dalgleish has more helpful techniques on her web site, Contextures, including Naming Names and Use Range Names in Formulas: Dynamic Charts

Peltier Technical Services, Inc.
We can customize or automate any example from this site, or build a new one for you. To discuss your project or obtain a quote, please contact me at jonxlmvp@peltiertech.com

PTS Blog: Charts and Things

Jon's Excel and Charting Pages
- Jon's Charts - Tutorials - Excel - Site Index and Search - RSS

Peltier Technical Services, Inc., Copyright © 2008. All rights reserved.
You may link to this article or portions of it on your site, but copying is prohibited without permission of Peltier Technical Services.

Microsoft Most Valuable Professional

My MVP Profile