Python Excel AutomationPractical guides for developers
How-to
Formatting And Charting Excel Reports With PythonDeep dive

Label Chart Points from Cell Values with XlsxWriter

Put product names, regions or comments on chart points instead of bare numbers: XlsxWriter custom data labels that reference cells (shown by Excel, ignored by LibreOffice), a portable one-series-per-point technique whose series-name labels render everywhere, labelling only the points that matter, labels from a different column on a column chart, and a test that the labels are really drawn.
Charts & imagesxlsxwriter

A scatter chart of units sold against margin shows six dots — and every reader's first question is "which product is that?". Excel's default labels show the plotted numbers, which the axes already show. What readers want is a label taken from another cell: the product name next to each point, or the margin printed on top of a sales column. XlsxWriter supports custom data labels whose text comes from a cell reference, which Excel 2013 and later display. That feature is not universal, though — LibreOffice ignores such labels — so this page also shows a portable construction that labels points in every application, and a way to label only the points worth pointing out. It belongs to Building Excel Reports with XlsxWriter.

Prerequisites

Bash
pip install xlsxwriter pandas pypdf

products.xlsx lists six products with units sold and margin. LibreOffice (soffice) renders the charts to PDF in Step 5, where pypdf reads the text back to check the labels; both are optional.

Step 1 — Custom labels that reference cells

XlsxWriter's data_labels option accepts a custom list with one entry per point. An entry's value can be a formula pointing at a cell:

Python
import pandas as pd
import xlsxwriter

products = pd.read_excel("products.xlsx")
n = len(products)

def write_table(book, name="Products"):
    sheet = book.add_worksheet(name)
    sheet.write_row(0, 0, list(products.columns))
    for r, row in enumerate(products.itertuples(index=False), start=1):
        sheet.write_row(r, 0, row)
    return sheet

book = xlsxwriter.Workbook("labels_custom.xlsx")
sheet = write_table(book)
chart = book.add_chart({"type": "scatter"})
chart.add_series({
    "categories": ["Products", 1, 1, n, 1],
    "values": ["Products", 1, 2, n, 2],
    "marker": {"type": "circle", "size": 8, "fill": {"color": "#4338CA"}, "border": {"color": "#4338CA"}},
    "data_labels": {"custom": [{"value": f"=Products!$A${r}"} for r in range(2, n + 2)], "position": "right"},
})
chart.set_x_axis({"name": "Units sold", "min": 0})
chart.set_y_axis({"name": "Margin %", "min": 0})
chart.set_legend({"none": True})
sheet.insert_chart("E2", chart)
book.close()
print("custom labels:", [f"=Products!$A${r}" for r in range(2, n + 2)])

Each label's text is linked to a product-name cell, so renaming a product in the table renames its label. XlsxWriter writes the reference together with a cached copy of the text, and Excel 2013 and later display it; XlsxWriter's documentation covers the options, including deleting individual labels. In LibreOffice (tested with version 26.2), however, these custom labels are not drawn at all — the points appear unlabelled. If any of your readers use LibreOffice, or the chart is rendered on a server with it, use the construction in Step 2.

Two ways to put names on chart points Custom data labels reference a cell per point and are shown by Excel 2013 and later, but LibreOffice did not draw them. One series per point with series-name labels uses only standard chart features and rendered in LibreOffice as well; it costs one series per point. custom labels (=Sheet!$A$2) one series, linked to cells shown by Excel 2013+ not drawn by LibreOffice needs a recent Excel one series per point standard series-name labels renders in Excel and LibreOffice easy to label only some points one series per point

Step 2 — The portable way: one series per point

Every chart application understands a data label that shows the series name. Make each point its own series, named after its product cell, and turn on series-name labels:

Python
book = xlsxwriter.Workbook("labels_portable.xlsx")
sheet = write_table(book)
chart = book.add_chart({"type": "scatter"})
for r in range(1, n + 1):
    chart.add_series({
        "name": ["Products", r, 0],                       # the product name cell
        "categories": ["Products", r, 1, r, 1],
        "values": ["Products", r, 2, r, 2],
        "marker": {"type": "circle", "size": 8, "fill": {"color": "#4338CA"}, "border": {"color": "#4338CA"}},
        "data_labels": {"series_name": True, "position": "right"},
    })
chart.set_x_axis({"name": "Units sold", "min": 0})
chart.set_y_axis({"name": "Margin %", "min": 0})
chart.set_legend({"none": True})
sheet.insert_chart("E2", chart)
book.close()
print(n, "single-point series")

Each series has exactly one point and takes its name from the product cell, so the label still updates when the name changes. Giving every series the same marker style keeps the chart looking like one data set; the legend is turned off because it would list every product. The cost is one series per point, which is fine for the dozens of points a labelled chart can show legibly — and a chart with hundreds of labelled points would be unreadable anyway. Scatter charts in general are covered in Create a Scatter Chart in Excel with openpyxl.

Six rows become six single-point series On the left the product table with names, units and margins. On the right six chart series, each named by one product cell and holding one point, labelled with its series name. A B C 1 Product Units Margin % 2 Drill 120 32 3 Saw 85 18 4 Ladder 35 41 series name point label =Products!A2 (120, 32) Drill =Products!A3 (85, 18) Saw =Products!A6 (35, 41) Ladder table chart series one add_series per row

Step 3 — Label only the points that matter

With many points, labelling all of them clutters the chart. The series-per-point method makes selective labelling easy: give labels only to the series that deserve attention — here the products with the highest margin and the highest volume:

Python
top_margin = set(products.nlargest(2, "Margin %")["Product"])
top_units = set(products.nlargest(1, "Units")["Product"])
highlight = top_margin | top_units

book = xlsxwriter.Workbook("labels_selected.xlsx")
sheet = write_table(book)
chart = book.add_chart({"type": "scatter"})
for r, product in enumerate(products["Product"], start=1):
    picked = product in highlight
    series = {
        "name": ["Products", r, 0],
        "categories": ["Products", r, 1, r, 1],
        "values": ["Products", r, 2, r, 2],
        "marker": {"type": "circle", "size": 9 if picked else 7,
                   "fill": {"color": "#4338CA" if picked else "#A5B4FC"}, "border": {"none": True}},
    }
    if picked:
        series["data_labels"] = {"series_name": True, "position": "above"}
    chart.add_series(series)
chart.set_legend({"none": True})
chart.set_x_axis({"name": "Units sold", "min": 0})
chart.set_y_axis({"name": "Margin %", "min": 0})
sheet.insert_chart("E2", chart)
book.close()
print("labelled:", sorted(highlight))

Ladder and Drill (highest margins) and Gloves (highest volume) get dark markers and names; the rest are pale and unlabelled, so the eye goes straight to the story. The selection rule lives in code, so next month's chart highlights next month's leaders without anyone editing labels by hand.

Step 4 — Labels from another column on a column chart

The same need appears on column charts: show units as the bars and the margin as the label on top. With custom labels that is one list of cell references; portably, it is the same series-per-category trick, using a label text column:

Python
book = xlsxwriter.Workbook("labels_columns.xlsx")
sheet = write_table(book)
sheet.write(0, 3, "Label")
for r, m in enumerate(products["Margin %"], start=1):
    sheet.write(r, 3, f"{m}% margin")
cols = book.add_chart({"type": "column"})
cols.add_series({
    "categories": ["Products", 1, 0, n, 0], "values": ["Products", 1, 1, n, 1],
    "fill": {"color": "#C7D2FE"}, "border": {"color": "#4338CA"},
    "data_labels": {"custom": [{"value": f"=Products!$D${r}"} for r in range(2, n + 2)]},
})
cols.set_legend({"none": True})
cols.set_title({"name": "Units sold, labelled with margin"})
sheet.insert_chart("F2", cols)
book.close()
print("label column:", [f"{m}% margin" for m in products["Margin %"]][:3], "…")

A helper column holds the formatted label text, and each bar's custom label points at its row. In Excel the bars show "32% margin", "18% margin" and so on; for a LibreOffice-safe version, use one series per bar with series names taken from the label column, exactly as in Step 2. Custom labels can also carry fixed text, a font and a position per point — see Style Chart Colours and Data Labels with openpyxl for the equivalent openpyxl options.

Step 5 — Check that the labels are drawn

Labels that render in one application and not another are easy to miss. Render each file to PDF and look for the product names on the page:

Python
import subprocess
from pypdf import PdfReader

def label_count(xlsx, words):
    subprocess.run(["soffice", "--headless", "--convert-to", "pdf", xlsx], check=True, capture_output=True)
    text = PdfReader(xlsx.replace(".xlsx", ".pdf")).pages[0].extract_text()
    return {w: text.count(w) for w in words}

names = list(products["Product"])
for f in ("labels_custom.xlsx", "labels_portable.xlsx", "labels_selected.xlsx"):
    print(f"{f:22}", label_count(f, names))

Each product name appears once in the table on every page. In the custom-label file it appears only once — LibreOffice drew no labels — while in the portable file every name appears twice, once in the table and once on the chart, and in the selected file only the three highlighted names appear twice. A check of this kind in a test catches a chart whose labels silently vanished, in whichever application your pipeline renders with.

Product names found in the rendered page Number of times the name Drill appears in each rendered PDF. In the custom-label file it appears once, in the table only. In the portable and selected-label files it appears twice, in the table and as a chart label. custom labels (LibreOffice) 1 · table only one series per point 2 · table + label selected labels 2 · Drill is highlighted

Common pitfalls

  • Assuming custom labels render everywhere. Excel 2013+ shows them; LibreOffice did not.
  • Labels showing values instead of names. Default labels show the y value; use custom or series-name labels.
  • A legend listing every point. With one series per point, turn the legend off.
  • Labelling everything. Highlight the few points that matter; dense labels overlap.
  • Hard-coded label text. Reference cells or derive the text in code so labels stay in step with the data.

Conclusion

To name chart points, XlsxWriter's custom data labels can reference a cell per point ({"value": "=Products!$A$2"}), which Excel 2013 and later display but LibreOffice ignores. For labels that render everywhere, give each point its own series named after its cell and turn on series-name labels — a construction that also makes it easy to label only the points that matter. Use a helper column for composed label text, and check rendered output for the names so missing labels are caught.

Frequently asked questions

How do I label Excel chart points with names from cells using XlsxWriter? Add data_labels with a custom list to the series, one entry per point, each with a value formula such as =Products!$A$2. Excel 2013 and later show the text of the referenced cell.

Why don't my XlsxWriter custom data labels show in LibreOffice? LibreOffice did not draw custom data labels that reference cells in our test. Use one series per point with series_name labels, which renders in both Excel and LibreOffice.

How do I label only some points of a chart? Create each point as its own series and add data labels only to the series you want to highlight, for example the top values selected with nlargest in pandas.

Can a column chart show a label from a different column? Yes. Write the label text in a helper column and use custom data labels that reference it, or, for full portability, one series per category named from that column with series_name labels.

More on charts & images

More on charts & images →