Microsoft Office Tutorials and References
In Depth Information
Changing the Data Used in a Chart
The arguments that you can use in the SERIES formula are
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 ).
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 a comma 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.
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.
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. For example, in a stacked column
chart, this parameter determines the stacking order. 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
ranges’ addresses are separated by a comma and enclosed in parentheses. The argument
could 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)
You can substitute range names for the range references. If you do so (and the name is a
workbook-level name), Excel changes the reference in the SERIES formula to include the workbook.
For example:
=SERIES(Sheet1!$B$1,,budget.xlsx!CurrentData,1)
Changing chart data based on the active cell
Figure 18-7 shows a chart that’s based on the data in the row of the active cell. When the user
moves the cell pointer, the chart is updated automatically.
 
Search JabSto ::




Custom Search