Work with data labels in charts using the Excel JavaScript API

Add data labels to Excel charts to provide a better visualization experience about important aspects of the chart. To learn more about data labels, see Add or remove data labels in a chart.

The following code sample sets up the sample data and Bicycle Part Production chart used in this article.

async function setup() {
  await Excel.run(async (context) => {
    context.workbook.worksheets.getItemOrNullObject("Sample").delete();
    const sheet = context.workbook.worksheets.add("Sample");

    let salesTable = sheet.tables.add("A1:E1", true);
    salesTable.name = "SalesTable";

    salesTable.getHeaderRowRange().values = [["Product", "Qtr1", "Qtr2", "Qtr3", "Qtr4"]];

    salesTable.rows.add(null, [
      ["Frames", 5000, 7000, 6544, 5377],
      ["Saddles", 400, 323, 276, 1451],
      ["Brake levers", 9000, 8766, 8456, 9812],
      ["Chains", 1550, 1088, 692, 2553],
      ["Mirrors", 225, 600, 923, 344],
      ["Spokes", 6005, 7634, 4589, 8765]
    ]);

    sheet.activate();
    createChart(context);
  });
}

async function createChart(context: Excel.RequestContext) {
  const worksheet = context.workbook.worksheets.getActiveWorksheet();
  const chart = worksheet.charts.add(
    Excel.ChartType.lineMarkers,
    worksheet.getRange("A1:E7"),
    Excel.ChartSeriesBy.rows
  );
  chart.axes.categoryAxis.setCategoryNames(worksheet.getRange("B1:E1"));
  chart.name = "PartChart";

  // Place the chart below sample data.
  chart.top = 125;
  chart.left = 5;
  chart.height = 300;
  chart.width = 450;

  chart.title.text = "Bicycle Part Production";
  chart.legend.position = "Bottom";

  await context.sync();
}

This image shows how the chart should display after running the sample code.

Add data labels

To add data labels to a chart, get the series of data points you want to change, and set the hasDataLabels property to true.

async function addDataLabels() {
  await Excel.run(async (context) => {
    const worksheet = context.workbook.worksheets.getActiveWorksheet();
    const chart = worksheet.charts.getItem("PartChart");
    await context.sync();

    // Get spokes data series.
    const series = chart.series.getItemAt(5);

    // Turn on data labels and set location.
    series.hasDataLabels = true;
    series.dataLabels.position = Excel.ChartDataLabelPosition.top;
    await context.sync();
  });
}

Format data label size, shape, and text

You can change attributes on data labels using the following APIs.

• Change data label shapes by setting the geometricShapeType property.
• Change height and width using the setWidth and setHeight methods.
• Change the text using the text property.
• Change the text formatting using the format property. You can change the border, fill, and font properties.

The following code sample shows how to set the shape type, height and width, and font formatting for the data labels.

 await Excel.run(async (context) => {
    const worksheet = context.workbook.worksheets.getActiveWorksheet();
    const chart = worksheet.charts.getItem("PartChart");
    const series = chart.series.getItemAt(5);

    // Set geometric shape of data labels to cubes.
    series.dataLabels.geometricShapeType = Excel.GeometricShapeType.cube;
    series.points.load("count");
    await context.sync();
    let pointsLoaded = series.points.count;

    // Change height, width, and font size of all data labels.
    for (let j = 0; j < pointsLoaded; j++) {
      series.points.getItemAt(j).dataLabel.setWidth(60);
      series.points.getItemAt(j).dataLabel.setHeight(30);
      series.points.getItemAt(j).dataLabel.format.font.size = 12;
    }

    // Set text of a data label.
    series.points.getItemAt(2).dataLabel.setWidth(80);
    series.points.getItemAt(2).dataLabel.setHeight(50);
    series.points.getItemAt(2).dataLabel.text = "Spokes Qtr3: 4589 ↓";

    await context.sync();
});

In the following screenshot, the chart now includes count data labels for the Spokes data, with custom text at the third data point.

You can also change the formatting of text in a data label. The following code sample shows how to use the getSubstring method to get part of data label text and apply font formatting.

await Excel.run(async (context) => {
    const worksheet = context.workbook.worksheets.getActiveWorksheet();
    const chart = worksheet.charts.getItem("PartChart");
    const series = chart.series.getItemAt(5);

    // Get the "Spokes" data label.
    let label = series.points.getItemAt(2).dataLabel;
    label.load();
    await context.sync();

    // Change border weight of this label.
    label.format.border.weight = 2;
    // Format "Qtr3" as bold and italicized. 
    label.getSubstring(7, 4).font.bold = true;
    label.getSubstring(7, 4).font.italic = true;
    // Format "Spokes" as green.
    label.getSubstring(0, 6).font.color = "green";
    // Format "4589" as red.
    label.getSubstring(12).font.color = "red";
    // Move label up by 15 points.
    label.top = label.top - 15;

    await context.sync();
 });

Format leader lines

Leader lines connect data labels to their respective data points and make it easier to see what they refer to in the chart. Turn leader lines on using the showLeaderLines property. You can set the format of leader lines with the leaderLines.format property.

await Excel.run(async (context) => {
    const worksheet = context.workbook.worksheets.getActiveWorksheet();
    const chart = worksheet.charts.getItem("PartChart");
    const series = chart.series.getItemAt(5);
    
    // Show leader lines.
    series.showLeaderLines = true;
    await context.sync();
    
    // Format leader lines as dotted orange lines with weight 2.
    series.dataLabels.leaderLines.format.line.lineStyle = Excel.ChartLineStyle.dot;
    series.dataLabels.leaderLines.format.line.color = "orange";
    series.dataLabels.leaderLines.format.line.weight = 2;
});

Create callouts

A callout is a data label that connects to the data point using a bubble-shaped pointer. A callout has an anchor which can be moved from the data point to other locations on the chart.

The following code sample shows how to change data labels in a series to use Excel.GeometricShapeType.wedgeRectCallout. Note that leader lines are turned off to avoid showing two indicators to the same data label.

 await Excel.run(async (context) => {
    const worksheet = context.workbook.worksheets.getActiveWorksheet();
    const chart = worksheet.charts.getItem("PartChart");
    const series = chart.series.getItemAt(5);

    // Change to a wedge rectangle style callout.
    series.dataLabels.geometricShapeType = Excel.GeometricShapeType.wedgeRectCallout;
    series.showLeaderLines = false;
    await context.sync();
});

The following code sample shows how to change the anchor location of a data label.

await Excel.run(async (context) => {
    const worksheet = context.workbook.worksheets.getActiveWorksheet();
    const chart = worksheet.charts.getItem("PartChart");
    const series = chart.series.getItemAt(5);

    let label = series.points.getItemAt(2).dataLabel;
    let point = series.points.getItemAt(2);
    label.load();
    await context.sync();

    let anchor = label.getTailAnchor();
    anchor.load();
    await context.sync();

    anchor.top = anchor.top - 10;
    anchor.left = 40;
});

This screenshot demonstrates how the anchor of the third data label is adjusted by the preceding code sample.

See also

• Excel JavaScript object model in Office Add-ins
• Work with charts using the Excel JavaScript API