Chapter 21: Using Advanced Charting Techniques
21
The arguments that you can use in the SERIES formula include the following:■ 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 commas and enclosed in paren-
theses. 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 refer-
ence is also valid. The range addresses are separated by a comma and enclosed in
parentheses. The argument could also consist of an array of comma-separated val-
ues 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 addresses are separated by commas and enclosed in parenthe-
ses. 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, note the following:
=SERIES(Sheet1!$B$1,,Sheet1!$B$2:$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 name (if it’s a workbook-level name) or to include the worksheet name (if it’s a
sheet-level name). For example, if you use a workbook-level range named MyData (in a workbook named budget.
xlsx), the SERIES formula looks like this:
=SERIES(Sheet1!$B$1,,budget.xlsx!MyData,1)
For more information about named ranges, see Chapter 4, “Working with Excel Ranges and Tables.”
Displaying data labels in a chart
Sometimes, you may want your chart to display the actual numerical value for each data
point. To add labels to data series in a chart, select the series and click the Add Elements