Microsoft Office Tutorials and References
In Depth Information
closed in parentheses.) The argument may also consist of an array of comma-separated values (or text in
quotation marks) enclosed in curly brackets.
values: (Required) A reference to the range that contains the values for the series. For XY charts, this argu-
ment specifies the y values. A noncontiguous range reference is also valid. (The range's addresses are separ-
ated by a comma and enclosed in parentheses.) The argument may also consist of an array of comma-separ-
ated values enclosed in curly brackets.
order: (Required) An integer that specifies the plotting order of the series. This argument is relevant only if
the chart has more than one series. Using a reference to a cell is not allowed.
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 range's addresses are separated by a
comma and enclosed in parentheses.) The argument may also consist of an array of values enclosed in curly
brackets.
Range references in a SERIES formula are always absolute, and (with one exception) they always include the
sheet name. Here's an example of a SERIES formula that doesn't use category labels:
=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 ar-
gument is enclosed in parentheses. In the following SERIES formula, the values range consists of B2:B3 and
B5:B7:
=SERIES(,,(Sheet1!\$B\$2:\$B\$3,Sheet1!\$B\$5:\$B\$7),1)
Although a SERIES formula can refer to data in other worksheets, all the data for a series must reside on a
single sheet. The following SERIES formula, for example, is not valid because the data series references two
different worksheets:
=SERIES(,,(Sheet1!\$B\$2,Sheet2!\$B\$2),1)
Using names in a SERIES formula
You can substitute range names for the range references in a SERIES formula. When you do so, Excel changes
the reference in the SERIES formula to include the workbook name. For example, the SERIES formula shown
here uses a range named MyData (located in a workbook named budget.xlsx). Excel added the workbook name
and exclamation point.
=SERIES(Sheet1!\$B\$1,,budget.xlsx!MyData,1)
Using names in a SERIES formula provides a significant advantage: If you change the range reference for the
name, the chart automatically displays the new data. In the preceding SERIES formula, for example, assume the
range named MyData refers to A1:A20. The chart displays the 20 values in that range. You can then use the
Name Manager to redefine MyData as a different range — say, A1:A30. The chart then displays the 30 data
points defined by MyData . (No chart editing is necessary.)
Search JabSto ::

Custom Search