Microsoft Office Tutorials and References

In Depth Information

**Creating the chart**

Creating the chart

The first step is to create a standard chart, using the data that currently exists.

Figure 7-1, presented earlier, shows the data and a column chart created from the

data. The chart contains a single series and its SERIES formula is as follows:

=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$11,Sheet1!$B$2:$B$11,1)

This SERIES formula specifies that:

The series name is in cell B1.

The category labels are in A2:A11.

The values are in B2:B11.

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.

Creating named formulas

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, it is explained later in this section. To create the named formulas:

1.
Select 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:

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

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:

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

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.