Peltier Technical Services, Inc.
 

Excel Chart Add-Ins | Training | Charts and Tutorials | Peltier Tech Blog


Peltier Tech Chart Utility

 

Books at Amazon.com

 

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  

 

Column Chart

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 (Dyno) 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

Share/Save/Bookmark
 

Page copy protected against web site content infringement by Copyscape

Peltier Tech Chart Utility


Peltier Technical Services, Inc.

Excel Chart Add-Ins | Training | Charts and Tutorials | PTS Blog

Peltier Technical Services, Inc., Copyright © 2017. 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

Microsoft Most Valuable Professional

My MVP Profile