Add a Data Table Below an Excel Chart

A chart shows the shape of the numbers; a table shows the numbers. Excel can do both in one object: a chart data table is a small grid drawn under the category axis of a column, bar, line or area chart, with one row per series and one column per category, lined up with the bars above it. Readers who want the exact figure for South in the third quarter read it off the grid instead of guessing from the bar height, and the chart can drop its legend, because the data table's rows carry the series names and colour keys. This page adds a data table to a chart with openpyxl and with XlsxWriter, sets its borders and keys, makes it show the numbers in the right format, and renders both versions to check them. It belongs to Creating Charts in Excel with openpyxl.

Prerequisites

pip install openpyxl xlsxwriter

Data tables are a feature of charts with a category axis: column, bar, line and area charts and their combinations. Pie, doughnut, scatter and bubble charts cannot have one. LibreOffice is used at the end of each step to render the chart for checking.

Step 1 — A data table with openpyxl

from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference
from openpyxl.chart.plotarea import DataTable

wb = Workbook()
ws = wb.active
ws.title = "Sales"
ws.append(["Quarter", "North", "South"])
for row in [("Q1", 120_500, 98_200), ("Q2", 135_250, 101_900), ("Q3", 128_100, 111_400), ("Q4", 150_750, 120_300)]:
    ws.append(list(row))
for row in ws.iter_rows(min_row=2, min_col=2, max_col=3):
    for cell in row:
        cell.number_format = "£#,##0"

chart = BarChart()
chart.type = "col"
chart.title = "Sales by quarter"
chart.add_data(Reference(ws, min_col=2, max_col=3, min_row=1, max_row=5), titles_from_data=True)
chart.set_categories(Reference(ws, min_col=1, min_row=2, max_row=5))
chart.x_axis.delete = chart.y_axis.delete = False
chart.y_axis.numFmt = "£#,##0"
chart.plot_area.dTable = DataTable(showHorzBorder=True, showVertBorder=True, showOutline=True, showKeys=True)
chart.legend = None                                   # the table's keys replace the legend
chart.width, chart.height = 17, 10
ws.add_chart(chart, "A8")
wb.save("sales_with_table.xlsx")
print("saved")

In openpyxl the data table belongs to the chart's plot area: assigning a DataTable to chart.plot_area.dTable switches it on. Its four flags control the look. showHorzBorder and showVertBorder draw the lines between rows and between columns, showOutline draws the border around the whole table, and showKeys puts a small colour swatch next to each series name — the same swatch a legend would show. With keys on, the legend is redundant, so it is removed by setting chart.legend to None, which also gives the plot area the width the legend used to take.

The values in the table come straight from the cells the series refer to, with their number formats. Formatting the source cells as £#,##0 is therefore what makes the table show £135,250 instead of 135250. The value axis does not inherit the cell format automatically, so it gets the same format through numFmt, and both now read in pounds.

Step 2 — Render and check

soffice --headless --convert-to pdf sales_with_table.xlsx > /dev/null 2>&1
pdftoppm -r 70 -f 1 -l 1 -png sales_with_table.pdf preview && ls preview*
python - <<'EOF'
import zipfile, re
xml = zipfile.ZipFile("sales_with_table.xlsx").read("xl/charts/chart1.xml").decode()
print(re.search(r"<dTable>.*?</dTable>", xml).group())
print("legend present:", "<legend>" in xml)
EOF

Rendering the workbook shows the grid under the axis: a row for North and a row for South, each with its colour key, and the four quarters' values in pounds aligned under their columns. The chart XML confirms what was written — a dTable element with the four flags set and no legend element. Reading the XML is a quick, dependable test for a chart option that has no other visible trace in openpyxl once the file is saved.

Step 3 — The same with XlsxWriter

import xlsxwriter

wb = xlsxwriter.Workbook("sales_with_table_xw.xlsx")
ws = wb.add_worksheet("Sales")
money = wb.add_format({"num_format": "£#,##0"})
ws.write_row("A1", ["Quarter", "North", "South"])
for i, (q, n, s) in enumerate([("Q1", 120_500, 98_200), ("Q2", 135_250, 101_900), ("Q3", 128_100, 111_400), ("Q4", 150_750, 120_300)], start=1):
    ws.write(i, 0, q)
    ws.write(i, 1, n, money)
    ws.write(i, 2, s, money)

chart = wb.add_chart({"type": "column"})
for col in (1, 2):
    chart.add_series({"name": ["Sales", 0, col], "categories": ["Sales", 1, 0, 4, 0], "values": ["Sales", 1, col, 4, col]})
chart.set_title({"name": "Sales by quarter"})
chart.set_table({"show_keys": True, "horizontal": True, "vertical": True, "outline": True,
                 "font": {"size": 9}})
chart.set_legend({"none": True})
chart.set_y_axis({"num_format": "£#,##0"})
chart.set_size({"width": 640, "height": 380})
ws.insert_chart("A8", chart)
wb.close()
print("saved")

XlsxWriter calls it set_table, with options named after what they do: horizontal, vertical and outline for the three kinds of border, show_keys for the colour swatches, and a font dictionary for the table's text, which is useful because the default size can be large on a small chart. The legend is switched off with set_legend({"none": True}). Everything else — series, categories, axis formats — is unchanged from a chart without a table. The XlsxWriter version renders the same way, with the pound values coming from the cell formats. In both libraries the table is set when the chart is created. Neither can add one to a chart that already exists in a workbook: XlsxWriter writes new files only, and openpyxl does not keep existing charts when it loads a file, so the chart has to be regenerated with the table included.

Fitting the table into the chart

The data table takes its space from the plot area, so a chart that looked well proportioned without one can end up with squat columns once the table is added. Three adjustments help. Make the chart taller — two series add roughly two rows of text, so an extra centimetre or two of height in openpyxl, or forty to sixty pixels in XlsxWriter, keeps the columns readable. Shorten the category labels, because they head the table's columns as well as labelling the axis: Q1 fits where First quarter 2026 would wrap. And keep the table's font a size or two smaller than the axis labels, which XlsxWriter's font option makes easy and which in openpyxl comes from the chart's text properties. For line charts the table works the same way, with each value sitting under the point it belongs to, which makes it particularly clear for monthly trends of one or two measures.

When a data table is the right choice

A data table works best on charts with a handful of series and up to about a dozen categories — quarterly figures for two or three regions, monthly figures for one or two measures. Every additional series adds a row and squeezes the plot area; every additional category makes the columns of the table narrower until the numbers no longer fit and Excel starts wrapping or shrinking them. For a single series, data labels on the bars are usually neater, because the number sits right on the bar it describes; see Label Chart Points from Cell Values with XlsxWriter. For many series or long periods, a separate formatted table next to the chart is more readable than squeezing everything under the axis.

Data tables also suit reports that are printed or turned into PDFs. A reader of a printed page cannot hover over a bar to see its value, and a table under the chart answers the question without sending them to another sheet. Keep the number format short — thousands with a k, or no decimals — so the values fit their columns; the techniques in Format Numbers in Thousands and Millions in Excel apply directly, because the table uses the cells' formats.

Common pitfalls

• Unformatted source cells. The table shows the cells' number formats; format them first.
• A legend as well as keys. Remove the legend when the table shows keys.
• Pie or scatter charts. Data tables need a category axis.
• Too many categories. The columns get too narrow; use data labels or a separate table.
• Axis format forgotten. The value axis does not take the cell format automatically.

Conclusion

To add a data table below an Excel chart, assign DataTable(showHorzBorder=True, showVertBorder=True, showOutline=True, showKeys=True) to chart.plot_area.dTable in openpyxl, or call chart.set_table({...}) in XlsxWriter. Remove the legend when the table shows keys, format the source cells so the table shows the right number format, set the axis format to match, and render the file once to check the layout.

Frequently asked questions

How do I add a data table to a chart with openpyxl? Import DataTable from openpyxl.chart.plotarea and assign an instance to chart.plot_area.dTable.

How do I add a data table to a chart with XlsxWriter? Call chart.set_table(), optionally with show_keys, horizontal, vertical, outline and font options.

How do I change the number format in a chart data table? The table uses the number format of the source cells, so format those cells.

Which chart types support a data table? Charts with a category axis — column, bar, line and area charts; not pie, doughnut, scatter or bubble charts.

Related

• Up one level: Creating Charts in Excel with openpyxl — native Excel charts from Python.
• Create a Bar Chart in Excel with openpyxl — the chart underneath.
• Add a Chart to an Excel File with xlsxwriter — XlsxWriter charts.
• Format Chart Axes with openpyxl — axis number formats.