Microsoft Office Tutorials and References

In Depth Information

A SERIES formula uses the following syntax:

=SERIES(name,category_labels,values,order)

●

name
(Optional) The name used in the legend. If the chart has only one series, the

name argument is used as the title.

●

category_labels
(Optional) The range that contains the labels for the category axis.

If omitted, Excel uses consecutive integers beginning with 1.

●

values

The range that contains the values.

●

order
An integer that specifies the plotting order of the series (relevant only if the

chart has more than one series).

Range references in a SERIES formula are always absolute, and they always include the

sheet name, as in the following example:

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

A range reference can consist of a noncontiguous range. If so, each range is separated by

a comma, and the argument is enclosed in parentheses. In the following SERIES formula,

the values’ ranges consist of B2:B3 and B5:B7:

=SERIES(,,(Sheet1!$B$2,Sheet1!$B$5:$B$7),1)

You can substitute range names for the range references. If you do so, Excel changes the

reference in the SERIES formula to include the workbook, as in the following example:

=SERIES(Sheet1$B$1,,budget.xls!MyData,1)

Modifying a Chart to Use Data from Arrays

A chart series can be defined by assigning a VBA array to its
Va flues
property. This is useful if

you need to generate a chart that is not linked to the original data. The chart can be distrib

uted in a separate workbook that’s independent of the source data.

Figure 15-3 displays the Garden Company Product Sales Chart with the
Catalog
series

selected. Notice the difference between this graphic and the graphic located within the

Inside Out: “How the Chart SERIES Formula Works” that displayed the
Catalog
series using

information from
Sheet1
. You can see the definition of the first data series in the formula bar

above the worksheet. The values on the y-axis are defined by an Excel array. The category

names have been assigned as text to the series names.