Creating a selfexpanding chart

One of the most common questions related to charting is: How can I create a chart that will expand automatically when I add new data to the worksheet?

To understand this issue, examine Figure 18-25, which shows a worksheet set up to store sales information that is updated daily. The chart displays all the data in the worksheet. When new data is entered, the chart series must be expanded to include the new data. On the other hand, if data is deleted, the chart series should also be contracted to exclude the deleted cells. It's certainly possible — if you're willing to do a little up-front work.

Figure 18-25: If this were a self-expanding chart, it would update automatically when new data is entered.

If you use Excel 2003, this trick is not necessary.You can choose the Data ^ List ^ Create List command to designate your data as a list.When you create a chart from the data, the chart will expand automatically when you add new data to the list.

One option, of course, is to specify a larger-than-required range for the chart data series. The problem with this approach is that the chart plots the empty cells, and the result is a lopsided chart that displays lots of empty space. In the majority of situations, this solution is not satisfactory.

Because this technique can be tricky, this section presents a step-by-step example. I start with a standard chart and then make the changes necessary to make the chart expand automatically when new data is added as well as to contract when data is deleted.

The example makes use of a simple worksheet that has dates in column A and sales amounts in column B. The assumption is that a new date and sales figure are each added daily and that the chart should display all the data.


The first step is to create a standard chart that uses the data that currently exists. Figure 18-25, in the preceding section, shows the data and a column chart created from the data. The chart contains a single series and its SERIES formula is as follows:


This SERIES formula specifies that

♦ The category labels are in A2:A11.

So far, this is just a common chart. If you add a new date and value, the chart will not display the new data. But that will soon change.


In this step, you create two named formulas. The names will eventually serve as arguments in the SERIES formula. In case you're not familiar with the concept of a named formula, I explain this later in this section. To create the named formulas:

1. Choose Insert ^ Name ^ Define to bring up the Define Name dialog box.

2. In the Names in Workbook field, enter Date. In the Refers To field, enter this formula:

3. Click Add to create the formula named Date.

Notice that the OFFSET function refers to the first category label (cell A2) and uses the COUNTA function to determine the number of labels in the column. Because column A has a heading in row 1, the formula subtracts 1 from the number.

4. Type Sales in the Names in Workbook field. Enter this formula in the Refers To field:

In this case, the OFFSET function refers to the first data point (cell B2). Again, the COUNTA function is used to get the number of data points, and it is adjusted to account for the label in cell B1.

5. Click Add to create the formula named Sales.

6. Click Close to close the Define Name dialog box.

After you perform these steps, the workbook contains two new names, Date and Sales.


The final step is to modify the chart so that it makes use of the two new names rather than the hard-coded range references.

1. Activate the chart and choose Chart ^ Source Data to bring up the Source Data dialog box.

2. In the Values field, enter Sheet1!Sales.

3. In the Category (x) Axis Labels field, enter Sheet1!Dates.

4. Verify that the dialog box looks like Figure 18-26 and then click OK.

Figure 18-26: Specifying the named formulas in the Source Data dialog box.

In Steps 2 and 3, note that the name was preceded by the worksheet name and an exclamation point. Because named formulas are workbook-level names (as opposed to sheet-level names), you should (technically) enter the workbook name, an exclamation point, and the name. However, Excel is very accommodating in this regard and changes it for you. If you access the Source Data dialog box again, you'll discover that Excel substituted the workbook's name for the sheet reference that you entered:


Bottom line? When using these named formulas, you can precede the name with either the worksheet name or the workbook name. (I find it easier to use the worksheet name.) But keep in mind that if the sheet name or workbook name includes a space character, you must enclose it in single quotation marks, like this:

='daily sales.xls'!Sales or

='sales data'!sales

For more information about names, refer to the sidebar, "How Excel Handles Names."

How Excel Handles Names

Excel supports two types of names: workbook-level names and worksheet-level names. The scope of a workbook-level name is the entire workbook. Normally, when you create a name for a cell or range, that name can be used in any worksheet.

You can also create sheet-level names. A sheet-level name incorporates the sheet name as part of its name. For example, Sheet1!Data is a sheet-level name. When you create this name, you can use it in formulas in Sheet1 without the sheet qualifier. For example:


But if you enter this formula in a different worksheet, Excel will not recognize the name unless you fully qualify it:


Sheet-level names are useful because they enable you to use the same name on different worksheets. For example, you might create sheet-level names such as Sheet1!Interest, Sheet2!Interest, and Sheet3!Interest. Each name refers to a cell on its own sheet. A formula that uses the name Interest uses the definition for its own sheet.

The named formulas used in this chapter are workbook-level names because they are not preceded by a sheet name. But when you enter a name in a field in the Source Data dialog box, Excel (for some reason) requires that you qualify the name with either the sheet name or the workbook name.

An alternative to using the Source Data dialog box is to edit the chart's SERIES formula directly.


To test the results of your efforts, either enter new data in columns A and B or delete data from the bottom of the columns. If you performed the preceding steps correctly, the chart will update automatically. If you receive an error message or the chart doesn't update itself, review the preceding steps carefully. This method does work!


Many people use this self-expanding chart technique without fully understanding how it works. There's certainly nothing wrong with that. If you go through the hands-on exercise that I previously describe, you should be able to adapt the procedures to your own charts. But understanding how it works will make it possible to go beyond the basic concept and create more powerful types of dynamic charts.


The self-expanding chart that I describe in this chapter takes advantage of a powerful feature called named formulas. You're probably familiar with the concept of named cells and ranges. But did you know that naming cells and ranges is really a misnomer? When you create a name for a range, you are really creating a named formula.

When you work with the Define Name dialog box, the Refers To field contains the formula, and the Names in Workbook field contains the formula's name. You'll find that the contents of the Refers to field always begin with an equal sign — a sure sign that it's a formula.

Unlike a normal formula, a named formula doesn't exist in a cell. Rather, it exists in Excel's memory and does not have a cell address. But you can access the result of a named formula by referring to its name, either in a standard formula or in a chart's SERIES formula.

After defining the two named formulas, Excel evaluates these formulas every time that the worksheet is calculated. But these named formulas aren't used in any cells, so there is no visible effect of creating these named formulas until you use them to define the chart series.

To get a better handle on named formulas, use the Define Name dialog box to create the following formula, and name it Sum12Cells.

After you create the named formula, enter the following formula into any cell: =Sum12Cells

This formula will return the sum of A1:A12.


The key to mastering self-expanding charts is understanding the OFFSET function. This function returns a range that is offset from a specified reference cell. Arguments for the OFFSET function let you specify the distance from the reference cell and the dimensions of the range (the number of rows and columns). The OFFSET function has five arguments, as follows:

♦ reference: The first argument for the OFFSET function is essentially the anchor cell, used by the second and third argument.

♦ rows: This argument indicates how many rows to move from the reference address to begin the range.

♦ cols: This argument indicates how many columns to move from the reference address to begin the range.

♦ height: This argument indicates the number of rows to be included in the range.

♦ width: The final argument indicates the number of columns to be included in the range.

If the columns used for the data contain any other entries, COUNTA will return an incorrect value.To keep things simple, don't put any other data in the column. If the column contains additional information, you'll need to adjust the height argument in the COUNTA function.

Recall that the named formula Sales was defined as

If there are 11 entries in column B, the COUNTA function returns 11. This result is adjusted by one to account for the column heading. Therefore, the named formula can be expressed as

This formula uses cell B2 as the anchor cell and returns a reference to the range that is

♦ Offset from cell B2 by 0 rows (second argument, rows)

♦ Offset from cell B2 by 0 columns (third argument, cols)

♦ Ten cells high (fourth argument, height)

♦ One cell wide (fifth argument, width)

In other words, the OFFSET function returns a reference to range B2:B11, and this is the range used by the chart series. When a new data point is added, the OFFSET function returns a reference to range B2:B12.

Subsequent examples in this chapter use the same basic concept but vary in the arguments supplied to the OFFSET function.

To keep things simple, this example makes use of a single data series. However, these techniques can be applied to charts with any number of data series. You will, however, have to make the necessary adjustments for each series.

0 0

Post a comment