Microsoft Office Tutorials and References

In Depth Information

**Chapter 17: Charting Techniques**

h
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.

h
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 they always include the sheet

name. For 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

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.)