EDIT.SERIES

Macro Sheets Only

Equivalent to choosing the Edit Series command from the Chart menu in Microsoft Excel version 4.0. Creates or changes chart series by adding a new SERIES formula or modifying an existing SERIES formula in the topmost chart type. Chart types are displayed in the following order from top to bottom: XY (Scatter), Line, Column, Bar, Area.

Syntax

EDIT.SERIES(series_num, name_ref, x_ref, y_ref, z_ref, plot_order)
EDIT.SERIES?(series_num, name_ref, x_ref, y_ref, z_ref, plot_order)

Series_num is the number of the series you want to change. If series_num is 0 or omitted, Microsoft Excel creates a new data series.

Name_ref is the name of the data series. It can be an external reference to a single cell, a name defined as a single cell, or a name defined as a sequence of characters. Name_ref can also be text (for example, "Projected Sales").

X_ref is an external reference to the name of the sheet and the cells that contain one of the following sets of data:

• Category labels for all charts except xy (scatter) charts
• X-coordinate data for xy (scatter) charts

Y_ref is an external reference to the name of the sheet and the cells that contain values (or y-coordinate data in xy (scatter) charts) for all 2-D charts. Y_ref is required in 2-D charts but does not apply to 3-D charts.

Z_ref is an external reference to the name of the sheet and the cells that contain values for all 3-D charts. Z_ref is required in 3-D charts but does not apply to 2-D charts.

Plot_order is a number specifying whether the data series is plotted first, second, and so on, in the chart type.

• If you assign a plot_order to a series, Microsoft Excel plots that series in the order you specify, and the series that previously had that plot order (and any series following it) has its plot order increased by one.
• If you add a series to a chart with an overlay, the number of series in the main chart does not change, so if the series is added to the main chart, then the series that was plotted last in the main chart will be plotted first in the overlay chart. To change which series is plotted first in the overlay chart, use the (chart type) Group command from the Format menu, and then select the Series Order tab in the Format (chart type) Group dialog box. You can also use the FORMAT.OVERLAY function.
• If you omit plot_order when you add a new series, then Microsoft Excel plots that series last and assigns it the correct plot_order value.
• The maximum value for plot_order is 255.

Remarks

To change where a series is plotted within a chart, you can change the chart type, using the FORMAT.CHART function, or the plot order. Plot order affects where the series appears within the chart type only.

X_ref, y_ref, and z_ref can be arrays or references to a nonadjacent selection, although they cannot be names that refer to a nonadjacent selection. If you specify a nonadjacent selection for any of these arguments, make sure to enclose the reference to the selection in parentheses so that Microsoft Excel does not treat the components of the references as separate arguments.

Tip To delete a data series, use the SELECT("Sn") macro function, where n is the series number, followed by the FORMULA("") macro function. You can also use the CLEAR function instead of FORMULA.

Related Functions

FORMAT.CHART

List of Command-Equivalent Functions