Leading the way in Microsoft Office Development

Step 1 - What is a Data Series ?

A data series is just a group of related data representing a row or column from the worksheet.

When you select a particular data series on a chart its corresponding series formula will appear in the formula bar.

Step 2 - What is the Series Formula ?

Excel uses a series formula or function to define a data series for a chart. There is one series formula for each data series.

Although this resembles a typical worksheet formula there are a few important differences.

1) The SERIES function cannot be used directly from another cell.

2) This formula cannot contain any worksheet functions.

It is possible though to edit this formula and to manually change the arguments.

Step 3 - Series Formula Arguments

[=SERIES(Name, X-Values, Y-Values, Order, Bubble Sizes) ]

The data used in each series in a chart is determined by its SERIES formula. Press Enter to apply any changes.

Name – (Optional) - This is the name of the series and is displayed in the legend. If the chart has only one series this name is used as the title. This can be blank, a text string in double quotes, a reference to a worksheet range or a named range. If this is left blank, then Excel will provide a default name (Series 1, 2, 3 etc)

X-Values – (Optional) - These are the labels that are used on the category axes. If this is left blank then consecutive integers are used 1,2,3 ... This can be blank, a literal array of numeric values or text labels enclosed in curly brackets, a reference to a worksheet range or a named range. It is also possible to have a non-continuous range reference. The separate ranges must be put inside a bracket and must be separated by commas (eg ??)

Y-Values – These are the values you want to plot. This can be blank, a literal array of numeric values enclosed in curly brackets, a reference to a worksheet range or a named range. It is also possible to have a non-continuous range reference. The separate ranges must be put inside a bracket and must be separated by a commas (eg ??).

Order – This is the plotting order for the data series. The Series tab displays the series in the order in which they are plotted. This is also the order in which the series names will appear in the legend. When there is only one series then this is omitted. This must be a whole number between 1 and the number of series on the chart. If you enter zero then 1 is used. If you enter a number greater than the number of series then the total number of series is used. This cannot be a cell reference.

Bubble Sizes – (Optional) - These are the values of the bubble sizes. This can be blank, a literal array of numeric values enclosed in curley brackets, a reference to a worksheet range or a named range. It is also possible to have a non-continuous range reference. The separate ranges must be put inside a bracket and must be separated by a commas (eg ??).

Step 4 - Series Formula Rules and Facts

Range references in a SERIES formula are always absolute and always contain a worksheet name. If you have defined named ranges then the series formula can contain a named range in place of a range.

Any range references used are always prefixed with the worksheet name.

Any range references used are always absolute references (as opposed to relative).

If you accidently remove the worksheet name or use a relative reference, these will be changed automatically.

Each data series has a corresponding series formula and the number of arguments will depend on the chart type.

Step 5 - Examples of Valid Series Formula

=SERIES(Name, X-Values, Y-Values, Order, Bubble Sizes)

=SERIES(Sheet1!$C$2, Sheet1!$B$3:$B$9, Sheet1!$C$3:$C$9, 1)

=SERIES("North", {"Mon","Tue","Wed","Thu","Fri","Sat","Sun"}, {0.65,0.21,0.86,0.97,0.05,0.34,0.74}, 1)

=SERIES(Sheet1!$C$2, , Sheet1!$C$3:$C$9, 2)

=SERIES(Sheet1!$C$2, Sheet1!$B$3:$B$9, Sheet1!$C$3:$C$9, 3, Sheet1!$D$3:$D$9)

=SERIES(, (Sheet1!$B$3:$B$9, Sheet1!$B$20:$B$30), (Sheet1!$C$3:$C$9, Sheet1!$C$20:$C$30), 4)

Step 6 - Changing the Arguments

You can change any of the series formula arguments in four ways you can change the series name of a data series:

1) Overwriting the cell(s) which the argument refers to.

2) Using the Automatic Range Finders to move the source data.

3) Using the (Chart > Source Data)(Series tab).

4) Editing the Series Formula.

The only way to change the order of the data series is to either use the (Chart > Source Data)(Series tab) or to edit the series formula directly.

Step 7 - Things to Remember

Excel’s normal practice is to assume that all the series share the same X-Values in the first column or row and that each successive column or row holds the Y-data for a separate series.

Surface charts do not have series formulas.

Pie charts can only have a single series unless .....

All the different parts of a series formula (except Plot Order) can be linked to a worksheet (in any workbook).

The maximum number of data points on a normal 2D chart is 32,000.

Bubble charts need three sets of values representing the x value, y value and the size of the bubbles.

You can display as many as 255 different data series on a chart (except Pie Charts).

Try to avoid having too many data series on a single chart.

You can have a chart object with no series, although I can’t see any point

There is a direct link between the chart and the worksheet containing the data. It is possible to change the underlying data in the worksheet by dragging a individual chart point to a new position.

It is important to remember that any of the parameters in the SERIES formula can be a constant, a literal arrway a reference to a worksheet range or a named range.