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();
});

getDimensionValues(dimension)

Gets the values from a single dimension of the chart series. These could be either category values or data values, depending on the dimension specified and how the data is mapped for the chart series.

getDimensionValues(dimension: Excel.ChartSeriesDimension): OfficeExtension.ClientResult<string[]>;

Parameters

dimension: Excel.ChartSeriesDimension

The dimension of the axis where the data is from.

Returns

OfficeExtension.ClientResult<string[]>

Remarks

[ API set: ExcelApi 1.12 ]

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) => {
  const sheet = context.workbook.worksheets.getItem("Sample");

  // The sample chart is of type `Excel.ChartType.bubble`.
  const bubbleChart = sheet.charts.getItem("Product Chart");

  // Get the first series in the chart.
  const firstSeries = bubbleChart.series.getItemAt(0);

  // Get the values for the dimensions we're interested in.
  const bubbleSize = firstSeries.getDimensionValues(Excel.ChartSeriesDimension.bubbleSizes);
  const xValues = firstSeries.getDimensionValues(Excel.ChartSeriesDimension.xvalues);
  const yValues = firstSeries.getDimensionValues(Excel.ChartSeriesDimension.yvalues);
  const category = firstSeries.getDimensionValues(Excel.ChartSeriesDimension.categories);

  await context.sync();

  // Log the information.
  console.log(`Series ${category.value} - X:${xValues.value},Y:${yValues.value},Bubble:${bubbleSize.value}`);
});

getDimensionValues(dimensionString)

getDimensionValues(dimensionString: "Categories" | "Values" | "XValues" | "YValues" | "BubbleSizes"): OfficeExtension.ClientResult<string[]>;

Parameters

dimensionString: "Categories" | "Values" | "XValues" | "YValues" | "BubbleSizes"

The dimension of the axis where the data is from.

Returns

OfficeExtension.ClientResult<string[]>

Remarks

[ API set: ExcelApi 1.12 ]

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