Build a chart that updates with your data

by Kara Hiltz

Application:

Microsoft Excel 2000/2002/2003/2004/2007

Operating Systems:

Microsoft Windows, Macintosh

 

Charts are a great way to visually demonstrate trends in your data, but they also require a lot of maintenance if you’re constantly adding or deleting entries from their source data. You can use self-adjusting named ranges as a chart’s source data so it always displays the right data without any more effort on your part.

 

To help you set up a chart that adjusts automatically whenever your source data expands or contracts, we’ll:

•     Define two self-adjusting named ranges: one for the chart series and one for its values.

•     Employ the Chart Wizard to create a chart that draws its series and values from the named ranges.

•     Test the technique by adding and removing entries from the chart.

 

At times, it probably seems like charts are more of a hassle than they’re worth. There’s a lot that can go wrong — formatting, readability, or incomplete data, to name a few. You can help eliminate one of your many chart worries by using a self-adjusting named range to create a chart that behaves the same way. It automatically refreshes itself to include or exclude information when you make changes to its source data.

 

 

Take a shortcut in Excel 2003/2007

If you’re using Excel 2003, you can accomplish the same effect without defining named ranges. All you need to do is transform your data into a list with the List feature (Table feature in Excel 2007). When you create a chart based on a list, which automatically adjusts to include all adjacent non-blank cells, the chart updates as well.

 

Set up the sample data

In our example, we’ll chart the data shown in Figure A. You can follow along with us by downloading and extracting the file hourslogged.xls from the URL given at the beginning of the article.

 

A:

We’ll use the hours logged by each employee during September 2009 to create a dynamic chart.

 

Define flexible named ranges

In our September 2009 issue, the article “Create named ranges that grow or shrink with your data automatically” discussed flexible named ranges based on a formula including both the OFFSET and COUNTA functions. We’ll create two of these self-adjusting named ranges: one for our Employee data, which will appear as a series on the x-axis, and the other for the Hours column, the plotted values in the chart. Then, we’ll briefly explain how the formulas automatically include just the right data — even after you make changes.

 

To create a self-adjusting named range for the Employees column:

1.       Launch Excel and open the sample worksheet, or create one based on Figure A.

2.      Select cell A3, right below the Employee column header and choose Insert | Name | Define from the menu bar to access the Define Name dialog box.

3.      Type Employee in the Names In Workbook text box.

4.