Excel 2010 Bible

Chapter 19: Learning Advanced Charting

459

Editing the Series formula

Every data series in a chart has an associated Series formula, which appears in the Formula bar

when you select a data series in a chart. If you understand how a Series formula is constructed,

you can edit the range references in the Series formula directly to change the data used by the

chart.

Note
The Series formula is not a real formula: In other words, you can’t use it in a cell, and you can’t use work-
sheet functions within the Series formula. You can, however, edit the arguments in the Series formula. n

A Series formula has the following syntax:

=SERIES(series_name, category_labels, values, order, sizes)

The arguments that you can use in the Series formula include

l series_name: (Optional). A reference to the cell that contains the series name used in

the legend. If the chart has only one series, the name argument is used as the title. This

argument can also consist of text in quotation marks. If omitted, Excel creates a default

series name (for example, Series 1 ).

l category_labels: (Optional). A reference to the range that contains the labels for the

category axis. If omitted, Excel uses consecutive integers beginning with 1. For XY charts,

this argument specifies the X values. A noncontiguous range reference is also valid. The

ranges’ addresses are separated by commas and enclosed in parentheses. The argument

could also consist of an array of comma-separated values (or text in quotation marks)

enclosed in curly brackets.

l values: (Required). A reference to the range that contains the values for the series. For

XY charts, this argument specifies the Y values. A noncontiguous range reference is also

valid. The ranges addresses are separated by a comma and enclosed in parentheses. The

argument could also consist of an array of comma-separated values enclosed in curly

brackets.

l (^) order: (Required). An integer that specifies the plotting order of the series. This argu-
ment is relevant only if the chart has more than one series. Using a reference to a cell is
not allowed.
l sizes: (Only for bubble charts). A reference to the range that contains the values for the
size of the bubbles in a bubble chart. A noncontiguous range reference is also valid. The
ranges addresses are separated by commas and enclosed in parentheses. The argument can
also consist of an array of values enclosed in curly brackets.
Range references in a Series formula are always absolute (contain two dollar signs), and they
always include the sheet name. For example
=SERIES(Sheet1!$B$1,,Sheet1!$B$2:$B$7,1)