Create a Dynamic Chart.

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

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:

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

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

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.

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.

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

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

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