Excel. Chart Series class

binOptions

Encapsulates the bin options for histogram charts and pareto charts.

readonly binOptions: Excel.ChartBinOptions;

Property Value

Excel.ChartBinOptions

Remarks

boxwhiskerOptions

Encapsulates the options for the box and whisker charts.

readonly boxwhiskerOptions: Excel.ChartBoxwhiskerOptions;

Property Value

Excel.ChartBoxwhiskerOptions

Remarks

bubbleScale

This can be an integer value from 0 (zero) to 300, representing the percentage of the default size. This property only applies to bubble charts.

bubbleScale: number;

Property Value

number

Remarks

chartType

Represents the chart type of a series. See Excel.ChartType for details.

chartType: Excel.ChartType | "Invalid" | "ColumnClustered" | "ColumnStacked" | "ColumnStacked100" | "3DColumnClustered" | "3DColumnStacked" | "3DColumnStacked100" | "BarClustered" | "BarStacked" | "BarStacked100" | "3DBarClustered" | "3DBarStacked" | "3DBarStacked100" | "LineStacked" | "LineStacked100" | "LineMarkers" | "LineMarkersStacked" | "LineMarkersStacked100" | "PieOfPie" | "PieExploded" | "3DPieExploded" | "BarOfPie" | "XYScatterSmooth" | "XYScatterSmoothNoMarkers" | "XYScatterLines" | "XYScatterLinesNoMarkers" | "AreaStacked" | "AreaStacked100" | "3DAreaStacked" | "3DAreaStacked100" | "DoughnutExploded" | "RadarMarkers" | "RadarFilled" | "Surface" | "SurfaceWireframe" | "SurfaceTopView" | "SurfaceTopViewWireframe" | "Bubble" | "Bubble3DEffect" | "StockHLC" | "StockOHLC" | "StockVHLC" | "StockVOHLC" | "CylinderColClustered" | "CylinderColStacked" | "CylinderColStacked100" | "CylinderBarClustered" | "CylinderBarStacked" | "CylinderBarStacked100" | "CylinderCol" | "ConeColClustered" | "ConeColStacked" | "ConeColStacked100" | "ConeBarClustered" | "ConeBarStacked" | "ConeBarStacked100" | "ConeCol" | "PyramidColClustered" | "PyramidColStacked" | "PyramidColStacked100" | "PyramidBarClustered" | "PyramidBarStacked" | "PyramidBarStacked100" | "PyramidCol" | "3DColumn" | "Line" | "3DLine" | "3DPie" | "Pie" | "XYScatter" | "3DArea" | "Area" | "Doughnut" | "Radar" | "Histogram" | "Boxwhisker" | "Pareto" | "RegionMap" | "Treemap" | "Waterfall" | "Sunburst" | "Funnel";

Property Value

Remarks

context

The request context associated with the object. This connects the add-in's process to the Office host application's process.

context: RequestContext;

Property Value

Excel.RequestContext

dataLabels

Represents a collection of all data labels in the series.

readonly dataLabels: Excel.ChartDataLabels;

Property Value

Excel.ChartDataLabels

Remarks

doughnutHoleSize

Represents the doughnut hole size of a chart series. Only valid on doughnut and doughnut exploded charts. Throws an InvalidArgument error on invalid charts.

doughnutHoleSize: number;

Property Value

number

Remarks

explosion

Specifies the explosion value for a pie-chart or doughnut-chart slice. Returns 0 (zero) if there's no explosion (the tip of the slice is in the center of the pie).

explosion: number;

Property Value

number

Remarks

filtered

Specifies if the series is filtered. Not applicable for surface charts.

filtered: boolean;

Property Value

boolean

Remarks

firstSliceAngle

Specifies the angle of the first pie-chart or doughnut-chart slice, in degrees (clockwise from vertical). Applies only to pie, 3-D pie, and doughnut charts. Can be a value from 0 through 360.

firstSliceAngle: number;

Property Value

number

Remarks

format

Represents the formatting of a chart series, which includes fill and line formatting.

readonly format: Excel.ChartSeriesFormat;

Property Value

Excel.ChartSeriesFormat

Remarks

[ API set: ExcelApi 1.1 ]

gapWidth

Represents the gap width of a chart series. Only valid on bar and column charts, as well as specific classes of line and pie charts. Throws an invalid argument exception on invalid charts.

gapWidth: number;

Property Value

number

Remarks

gradientMaximumColor

Specifies the color for maximum value of a region map chart series.

gradientMaximumColor: string;

Property Value

string

Remarks

gradientMaximumType

Specifies the type for maximum value of a region map chart series.

gradientMaximumType: Excel.ChartGradientStyleType | "ExtremeValue" | "Number" | "Percent";

Property Value

Excel.ChartGradientStyleType | "ExtremeValue" | "Number" | "Percent"

Remarks

gradientMaximumValue

Specifies the maximum value of a region map chart series.

gradientMaximumValue: number;

Property Value

number

Remarks

gradientMidpointColor

Specifies the color for the midpoint value of a region map chart series.

gradientMidpointColor: string;

Property Value

string

Remarks

gradientMidpointType

Specifies the type for the midpoint value of a region map chart series.

gradientMidpointType: Excel.ChartGradientStyleType | "ExtremeValue" | "Number" | "Percent";

Property Value

Excel.ChartGradientStyleType | "ExtremeValue" | "Number" | "Percent"

Remarks

gradientMidpointValue

Specifies the midpoint value of a region map chart series.

gradientMidpointValue: number;

Property Value

number

Remarks

gradientMinimumColor

Specifies the color for the minimum value of a region map chart series.

gradientMinimumColor: string;

Property Value

string

Remarks

gradientMinimumType

Specifies the type for the minimum value of a region map chart series.

gradientMinimumType: Excel.ChartGradientStyleType | "ExtremeValue" | "Number" | "Percent";

Property Value

Excel.ChartGradientStyleType | "ExtremeValue" | "Number" | "Percent"

Remarks

gradientMinimumValue

Specifies the minimum value of a region map chart series.

gradientMinimumValue: number;

Property Value

number

Remarks

gradientStyle

Specifies the series gradient style of a region map chart.

gradientStyle: Excel.ChartGradientStyle | "TwoPhaseColor" | "ThreePhaseColor";

Property Value

Excel.ChartGradientStyle | "TwoPhaseColor" | "ThreePhaseColor"

Remarks

hasDataLabels

Specifies if the series has data labels.

hasDataLabels: boolean;

Property Value

boolean

Remarks

invertColor

Specifies the fill color for negative data points in a series.

invertColor: string;

Property Value

string

Remarks

invertIfNegative

True if Excel inverts the pattern in the item when it corresponds to a negative number.

invertIfNegative: boolean;

Property Value

boolean

Remarks

mapOptions

Encapsulates the options for a region map chart.

readonly mapOptions: Excel.ChartMapOptions;

Property Value

Excel.ChartMapOptions

Remarks

markerBackgroundColor

Specifies the marker background color of a chart series.

markerBackgroundColor: string;

Property Value

string

Remarks

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/10-chart/chart-series-markers.yaml

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Sample");
    let salesTable = sheet.tables.getItem("SalesTable");
    let dataRange = sheet.getRange("A1:E7");

    // Create an XY scatter chart.
    let chart = sheet.charts.add("XYScatterSmooth", dataRange, "Auto");
    chart.title.text = "Bicycle Parts Quarterly Sales"; 
  
    let series = chart.series;
    let series0 = series.getItemAt(0);
    let series1 = series.getItemAt(1);
    let series2 = series.getItemAt(2);
    let series3 = series.getItemAt(3);

    // Set markers.
    series0.markerStyle = "Dash";
    series0.markerForegroundColor = "black";
    series1.markerStyle = "Star";
    series1.markerForegroundColor = "black";
    series2.markerStyle = "X";
    series2.markerSize = 12;
    series3.markerStyle = "Triangle";
    series3.markerBackgroundColor = "purple";

    await context.sync();
});

markerForegroundColor

Specifies the marker foreground color of a chart series.

markerForegroundColor: string;

Property Value

string

Remarks

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/10-chart/chart-series-markers.yaml

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Sample");
    let salesTable = sheet.tables.getItem("SalesTable");
    let dataRange = sheet.getRange("A1:E7");

    // Create an XY scatter chart.
    let chart = sheet.charts.add("XYScatterSmooth", dataRange, "Auto");
    chart.title.text = "Bicycle Parts Quarterly Sales"; 
  
    let series = chart.series;
    let series0 = series.getItemAt(0);
    let series1 = series.getItemAt(1);
    let series2 = series.getItemAt(2);
    let series3 = series.getItemAt(3);

    // Set markers.
    series0.markerStyle = "Dash";
    series0.markerForegroundColor = "black";
    series1.markerStyle = "Star";
    series1.markerForegroundColor = "black";
    series2.markerStyle = "X";
    series2.markerSize = 12;
    series3.markerStyle = "Triangle";
    series3.markerBackgroundColor = "purple";

    await context.sync();
});

markerSize

Specifies the marker size of a chart series. The supported size range is 2 to 72. This method returns an InvalidArgument error if it's set with a size outside of the supported range.

markerSize: number;

Property Value

number

Remarks

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/10-chart/chart-series-markers.yaml

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Sample");
    let salesTable = sheet.tables.getItem("SalesTable");
    let dataRange = sheet.getRange("A1:E7");

    // Create an XY scatter chart.
    let chart = sheet.charts.add("XYScatterSmooth", dataRange, "Auto");
    chart.title.text = "Bicycle Parts Quarterly Sales"; 
  
    let series = chart.series;
    let series0 = series.getItemAt(0);
    let series1 = series.getItemAt(1);
    let series2 = series.getItemAt(2);
    let series3 = series.getItemAt(3);

    // Set markers.
    series0.markerStyle = "Dash";
    series0.markerForegroundColor = "black";
    series1.markerStyle = "Star";
    series1.markerForegroundColor = "black";
    series2.markerStyle = "X";
    series2.markerSize = 12;
    series3.markerStyle = "Triangle";
    series3.markerBackgroundColor = "purple";

    await context.sync();
});

markerStyle

Specifies the marker style of a chart series. See Excel.ChartMarkerStyle for details.

markerStyle: Excel.ChartMarkerStyle | "Invalid" | "Automatic" | "None" | "Square" | "Diamond" | "Triangle" | "X" | "Star" | "Dot" | "Dash" | "Circle" | "Plus" | "Picture";

Property Value

Remarks

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/10-chart/chart-series-markers.yaml

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Sample");
    let salesTable = sheet.tables.getItem("SalesTable");
    let dataRange = sheet.getRange("A1:E7");

    // Create an XY scatter chart.
    let chart = sheet.charts.add("XYScatterSmooth", dataRange, "Auto");
    chart.title.text = "Bicycle Parts Quarterly Sales"; 
  
    let series = chart.series;
    let series0 = series.getItemAt(0);
    let series1 = series.getItemAt(1);
    let series2 = series.getItemAt(2);
    let series3 = series.getItemAt(3);

    // Set markers.
    series0.markerStyle = "Dash";
    series0.markerForegroundColor = "black";
    series1.markerStyle = "Star";
    series1.markerForegroundColor = "black";
    series2.markerStyle = "X";
    series2.markerSize = 12;
    series3.markerStyle = "Triangle";
    series3.markerBackgroundColor = "purple";

    await context.sync();
});

name

Specifies the name of a series in a chart. The name's length should not be greater than 255 characters.

name: string;

Property Value

string

Remarks

[ API set: ExcelApi 1.1 ]

overlap

Specifies how bars and columns are positioned. Can be a value between -100 and 100. Applies only to 2-D bar and 2-D column charts.

overlap: number;

Property Value

number

Remarks

parentLabelStrategy

Specifies the series parent label strategy area for a treemap chart.

parentLabelStrategy: Excel.ChartParentLabelStrategy | "None" | "Banner" | "Overlapping";

Property Value

Excel.ChartParentLabelStrategy | "None" | "Banner" | "Overlapping"

Remarks

plotOrder

Specifies the plot order of a chart series within the chart group.

plotOrder: number;

Property Value

number

Remarks

points

Returns a collection of all points in the series.

readonly points: Excel.ChartPointsCollection;

Property Value

Excel.ChartPointsCollection

Remarks

[ API set: ExcelApi 1.1 ]

secondPlotSize

Specifies the size of the secondary section of either a pie-of-pie chart or a bar-of-pie chart, as a percentage of the size of the primary pie. Can be a value from 5 to 200.

secondPlotSize: number;

Property Value

number

Remarks

showConnectorLines

Specifies whether connector lines are shown in waterfall charts.

showConnectorLines: boolean;

Property Value

boolean

Remarks

showLeaderLines

Specifies whether leader lines are displayed for each data label in the series.

showLeaderLines: boolean;

Property Value

boolean

Remarks

showShadow

Specifies if the series has a shadow.

showShadow: boolean;

Property Value

boolean

Remarks

smooth

Specifies if the series is smooth. Only applicable to line and scatter charts.

smooth: boolean;

Property Value

boolean

Remarks

splitType

Specifies the way the two sections of either a pie-of-pie chart or a bar-of-pie chart are split.

splitType: Excel.ChartSplitType | "SplitByPosition" | "SplitByValue" | "SplitByPercentValue" | "SplitByCustomSplit";

Property Value

Excel.ChartSplitType | "SplitByPosition" | "SplitByValue" | "SplitByPercentValue" | "SplitByCustomSplit"

Remarks

splitValue

Specifies the threshold value that separates two sections of either a pie-of-pie chart or a bar-of-pie chart.

splitValue: number;

Property Value

number

Remarks

trendlines

The collection of trendlines in the series.

readonly trendlines: Excel.ChartTrendlineCollection;

Property Value

Excel.ChartTrendlineCollection

Remarks

varyByCategories

True if Excel assigns a different color or pattern to each data marker. The chart must contain only one series.

varyByCategories: boolean;

Property Value

boolean

Remarks

xErrorBars

Represents the error bar object of a chart series.

readonly xErrorBars: Excel.ChartErrorBars;

Property Value

Excel.ChartErrorBars

Remarks

yErrorBars

Represents the error bar object of a chart series.

readonly yErrorBars: Excel.ChartErrorBars;

Property Value

Excel.ChartErrorBars

Remarks

Method Details

delete()

Deletes the chart series.

delete(): void;

Returns

void

Remarks

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/10-chart/chart-series.yaml

await Excel.run(async (context) => {
  const sheet = context.workbook.worksheets.getItem("Sample");
  const seriesCollection = sheet.charts.getItemAt(0).series;
  seriesCollection.load("count");
  await context.sync();

  if (seriesCollection.count > 0) {
    const series = seriesCollection.getItemAt(0);

    // Delete the first series.
    series.delete();
  }

  await context.sync();
});

load(options)

Queues up a command to load the specified properties of the object. You must call context.sync() before reading the properties.

load(options?: Excel.Interfaces.ChartSeriesLoadOptions): Excel.ChartSeries;

Parameters

options: Excel.Interfaces.ChartSeriesLoadOptions

Provides options for which properties of the object to load.

Returns

Excel.ChartSeries

load(propertyNames)

Queues up a command to load the specified properties of the object. You must call context.sync() before reading the properties.

load(propertyNames?: string | string[]): Excel.ChartSeries;

Parameters

propertyNames: string | string[]

A comma-delimited string or an array of strings that specify the properties to load.

Returns

Excel.ChartSeries

Examples

// Rename the 1st series of Chart1 to "New Series Name".
await Excel.run(async (context) => { 
    const chart = context.workbook.worksheets.getItem("Sheet1").charts.getItem("Chart1");    
    chart.series.getItemAt(0).name = "New Series Name";
    await context.sync();

    console.log("Series1 Renamed");
});

load(propertyNamesAndPaths)

Queues up a command to load the specified properties of the object. You must call context.sync() before reading the properties.

load(propertyNamesAndPaths?: {
            select?: string;
            expand?: string;
        }): Excel.ChartSeries;

Parameters

propertyNamesAndPaths: { select?: string; expand?: string; }

propertyNamesAndPaths.select is a comma-delimited string that specifies the properties to load, and propertyNamesAndPaths.expand is a comma-delimited string that specifies the navigation properties to load.

Returns

Excel.ChartSeries

set(properties, options)

Sets multiple properties of an object at the same time. You can pass either a plain object with the appropriate properties, or another API object of the same type.

set(properties: Interfaces.ChartSeriesUpdateData, options?: OfficeExtension.UpdateOptions): void;

Parameters

properties: Excel.Interfaces.ChartSeriesUpdateData

A JavaScript object with properties that are structured isomorphically to the properties of the object on which the method is called.

options: OfficeExtension.UpdateOptions

Provides an option to suppress errors if the properties object tries to set any read-only properties.

Returns

void

set(properties)

Sets multiple properties on the object at the same time, based on an existing loaded object.

set(properties: Excel.ChartSeries): void;

Parameters

properties: Excel.ChartSeries

Returns

void

setBubbleSizes(sourceData)

Sets the bubble sizes for a chart series. Only works for bubble charts.

setBubbleSizes(sourceData: Range): void;

Parameters

sourceData: Excel.Range

The Range object corresponding to the source data.

Returns

void

Remarks

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/10-chart/chart-bubble-chart.yaml

await Excel.run(async (context) => {
  /*
    The table is expected to look like this:
    Product, Inventory, Price, Current Market Share
    Calamansi, 2000, $2.45, 10%
    ...

    We want each bubble to represent a single row.
  */

  // Get the worksheet and table data.
  const sheet = context.workbook.worksheets.getItem("Sample");
  const table = sheet.tables.getItem("Sales");
  const dataRange = table.getDataBodyRange();

  // Get the table data without the row names.
  const valueRange = dataRange.getOffsetRange(0, 1).getResizedRange(0, -1);

  // Create the chart.
  const bubbleChart = sheet.charts.add(Excel.ChartType.bubble, valueRange);
  bubbleChart.name = "Product Chart";

  // Remove the default series, since we want a unique series for each row.
  bubbleChart.series.getItemAt(0).delete();

  // Load the data necessary to make a chart series.
  dataRange.load(["rowCount", "values"]);
  await context.sync();

  // For each row, create a chart series (a bubble).
  for (let i = 0; i < dataRange.rowCount; i++) {
    const newSeries = bubbleChart.series.add(dataRange.values[i][0], i);
    newSeries.setXAxisValues(dataRange.getCell(i, 1));
    newSeries.setValues(dataRange.getCell(i, 2));
    newSeries.setBubbleSizes(dataRange.getCell(i, 3));

    // Show the product name and market share percentage.
    newSeries.dataLabels.showSeriesName = true;
    newSeries.dataLabels.showBubbleSize = true;
    newSeries.dataLabels.showValue = false;
  }

  await context.sync();
});

setValues(sourceData)

Sets the values for a chart series. For scatter charts, it refers to y-axis values.

setValues(sourceData: Range): void;

Parameters

sourceData: Excel.Range

The Range object corresponding to the source data.

Returns

void

Remarks

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/10-chart/chart-series.yaml

await Excel.run(async (context) => {
  const sheet = context.workbook.worksheets.getItem("Sample");

  let seriesCollection = sheet.charts.getItemAt(0);
  let rangeSelection = sheet.getRange("C2:C7");
  let xRangeSelection = sheet.getRange("A1:A7");

  // Add a series.
  let newSeries = seriesCollection.series.add("Qtr2");
  newSeries.setValues(rangeSelection);
  newSeries.setXAxisValues(xRangeSelection);

  await context.sync();
});

setXAxisValues(sourceData)

Sets the values of the x-axis for a chart series.

setXAxisValues(sourceData: Range): void;

Parameters

sourceData: Excel.Range

The Range object corresponding to the source data.

Returns

void

Remarks