Guide

Automating Reporting WorkflowsDeep dive

Build a Dashboard Sheet with Charts from Multiple Tabs

Assemble one dashboard page whose charts read from other sheets: cross-sheet series references sized from the data, cell-anchored layout, and the openpyxl equivalent.

The point of a dashboard sheet is that a reader never has to visit the tabs behind it. That means charts whose data lives elsewhere in the workbook — one series from the monthly tab, another from the regional one — assembled onto a single page with a deliberate layout. This guide, part of Building Multi-Sheet Excel Dashboards, builds that page with xlsxwriter and shows the openpyxl equivalent.

Prerequisites

Bash

pip install pandas xlsxwriter openpyxl

Python

import pandas as pd

monthly = pd.DataFrame({
    "Month": ["Jan", "Feb", "Mar", "Apr", "May", "Jun"],
    "Revenue": [31200.0, 28400.0, 34100.0, 29800.0, 36500.0, 41200.0],
    "Target": [30000.0, 30000.0, 32000.0, 32000.0, 34000.0, 34000.0],
})

regional = pd.DataFrame({
    "Region": ["North", "South", "West", "East"],
    "Revenue": [86400.0, 52300.0, 38900.0, 23600.0],
})

Write the data tabs first

Charts reference ranges, so the data has to exist before the chart that points at it. Writing each frame to its own sheet and recording where it landed keeps the references derived rather than guessed.

Python

with pd.ExcelWriter("dashboard.xlsx", engine="xlsxwriter") as writer:
    monthly.to_excel(writer, sheet_name="Monthly", index=False)
    regional.to_excel(writer, sheet_name="Regional", index=False)
    book = writer.book
    dashboard = book.add_worksheet("Dashboard")
    book.worksheets_objs.insert(0, book.worksheets_objs.pop())     # put it first

Moving the dashboard to the front matters more than it sounds: a workbook opens on whichever sheet was active when it was saved, and a reader who lands on a raw data tab forms an impression the dashboard was supposed to prevent.

A chart whose data lives on another sheet

Python

rows = len(monthly)

line = book.add_chart({"type": "line"})
line.add_series({
    "name":       "Revenue",
    "categories": ["Monthly", 1, 0, rows, 0],
    "values":     ["Monthly", 1, 1, rows, 1],
    "line":       {"color": "#5B5CF0", "width": 2.25},
})
line.add_series({
    "name":       "Target",
    "categories": ["Monthly", 1, 0, rows, 0],
    "values":     ["Monthly", 1, 2, rows, 2],
    "line":       {"color": "#B4740A", "width": 1.5, "dash_type": "dash"},
})
line.set_title({"name": "Revenue against target"})
line.set_y_axis({"num_format": "#,##0", "major_gridlines": {"visible": True}})
line.set_legend({"position": "bottom"})
line.set_size({"width": 480, "height": 280})
dashboard.insert_chart("B8", line)

The list form of categories and values — sheet name, first row, first column, last row, last column — is worth preferring over the string form because it takes the row count as a variable. rows = len(monthly) is what stops the chart from silently excluding new months, which is the defect that a hard-coded $B$2:$B$7 produces the moment the data grows.

A second chart from a different tab

Python

bar = book.add_chart({"type": "column"})
bar.add_series({
    "name":       "Revenue by region",
    "categories": ["Regional", 1, 0, len(regional), 0],
    "values":     ["Regional", 1, 1, len(regional), 1],
    "fill":       {"color": "#0F9488"},
    "data_labels": {"value": True, "num_format": "#,##0"},
})
bar.set_title({"name": "Revenue by region"})
bar.set_legend({"none": True})
bar.set_size({"width": 420, "height": 280})
dashboard.insert_chart("K8", bar)

Two charts side by side, each reading from its own tab, anchored to cells in the same row so their tops align. Anchoring to cells rather than pixel offsets is what keeps that alignment when a column width changes.

Laying the page out deliberately

Python

dashboard.hide_gridlines(2)
dashboard.set_column("A:A", 2)
dashboard.set_column("B:T", 9)
dashboard.set_row(0, 30)

title = book.add_format({"font_size": 16, "bold": True, "font_color": "#172033"})
subtitle = book.add_format({"font_size": 10, "font_color": "#5B6780"})
dashboard.write("B1", "First half 2026 — performance", title)
dashboard.write("B2", "Generated automatically; figures from the Monthly and Regional tabs", subtitle)
dashboard.set_tab_color("#5B5CF0")

Uniform narrow columns give a grid that charts and tables can both align to, and a coloured tab makes the dashboard identifiable in a workbook with a dozen sheets. Neither is cosmetic in the dismissive sense — a report that looks assembled is one people trust.

The openpyxl version

For a workbook that already exists, openpyxl builds the same charts from Reference objects.

Python

from openpyxl import load_workbook
from openpyxl.chart import BarChart, LineChart, Reference

book = load_workbook("existing.xlsx")
dashboard = book["Dashboard"] if "Dashboard" in book.sheetnames else book.create_sheet("Dashboard", 0)
data_sheet = book["Monthly"]

chart = LineChart()
chart.title = "Revenue against target"
chart.height, chart.width = 8, 16
values = Reference(data_sheet, min_col=2, max_col=3, min_row=1, max_row=data_sheet.max_row)
categories = Reference(data_sheet, min_col=1, min_row=2, max_row=data_sheet.max_row)
chart.add_data(values, titles_from_data=True)
chart.set_categories(categories)
dashboard.add_chart(chart, "B8")

book.save("existing-with-dashboard.xlsx")

Reference takes the worksheet object as its first argument, which is how the cross-sheet reference is expressed — there is no sheet name string to get wrong. titles_from_data=True reads the series names from the header row, so the legend matches the columns without being restated.

Combining several sources into one chart

Two series from two different tabs on the same chart is straightforward, because each series carries its own reference. What is not straightforward is a chart whose categories come from one tab and whose values come from another — the axes have to agree, and nothing checks that they do.

Python

combo = book.add_chart({"type": "column"})
combo.add_series({
    "name":       "This year",
    "categories": ["Monthly", 1, 0, rows, 0],
    "values":     ["Monthly", 1, 1, rows, 1],
    "fill":       {"color": "#5B5CF0"},
})
combo.add_series({
    "name":       "Last year",
    "categories": ["Monthly", 1, 0, rows, 0],
    "values":     ["LastYear", 1, 1, rows, 1],
    "fill":       {"color": "#CDD5E6"},
})
combo.set_title({"name": "This year against last"})
dashboard.insert_chart("B26", combo)

Both series use the same categories deliberately: the months come from one tab, and the second series' values are lined up against them by position rather than by matching month names. That works only if the two tabs really do hold the same months in the same order, which is a property worth asserting in Python before writing either sheet.

Python

assert list(monthly["Month"]) == list(last_year["Month"]), "month axes do not match"

One assertion prevents the class of dashboard bug where two lines are compared month by month and the comparison is off by one — a defect that looks like a business insight rather than an error. The alignment problem itself, and the join that fixes it properly, is covered in Running Totals and Year-Over-Year Growth in pandas.

Making the tabs behind it navigable

A dashboard that points at six tabs benefits from pointing back. A row of hyperlinks under the charts turns the workbook into something a reader can explore rather than a page with hidden dependencies.

Python

link = book.add_format({"font_color": "#4338CA", "underline": 1})
for index, (name, label) in enumerate([("Monthly", "Monthly detail"),
                                       ("Regional", "Regional detail")]):
    dashboard.write_url(24, 1 + index * 4, f"internal:'{name}'!A1", link, label)

internal: is the prefix that makes a link jump within the workbook rather than opening a browser, and quoting the sheet name handles the ones with spaces. Adding the reverse link — a "back to dashboard" cell at the top of each data tab — costs three more lines and is what makes a multi-sheet workbook feel designed. The mechanics are covered in Add Hyperlinks to Excel Cells with Python.

Common pitfalls

Symptom	Cause	Fix
Chart is blank	Sheet name in the reference does not match exactly	Names are case-sensitive; quote names containing spaces
Recent rows missing from the chart	Hard-coded range	Size the reference from `len(frame)` or point at a table
The header row appears as a data point	Range starts at row 0	Start values at row 1, and use `titles_from_data` for the name
Charts overlap	Anchored to the same cell, or sized larger than the gap	Anchor to cells in the layout grid and set sizes explicitly
Charts shift after editing the sheet	Absolute pixel offsets	Anchor to a cell; offsets move with it
`#REF!` in the series after a save	Rows were deleted rather than cleared	Clear contents instead of deleting rows

Keeping references alive as data grows

The most durable arrangement points each series at an Excel table rather than a range, because a table grows with the rows written into it and every reference to it follows.

Python

dashboard_data = writer.sheets["Monthly"]
dashboard_data.add_table(0, 0, len(monthly), 2, {
    "name": "MonthlyData",
    "columns": [{"header": c} for c in monthly.columns],
    "style": "Table Style Light 9",
})

Once the range is a table, a chart series pointed at MonthlyData[Revenue] expands automatically the next time the sheet is regenerated with more rows. That is the same mechanism described in Create an Excel Table with Python, applied to charting rather than filtering.

Performance and scale

Charts are cheap to write and expensive to open. A workbook with thirty charts, each pointing at a few thousand rows, takes noticeably longer for Excel to render than the same data with three charts — and the reader gains nothing from the other twenty-seven. The practical limits worth respecting are a handful of charts per dashboard sheet and a few hundred points per series; beyond that the chart stops communicating before the file stops opening.

Where a series genuinely has tens of thousands of points, aggregate before charting rather than plotting everything: a daily series over three years is 1,100 points that read as noise, while the monthly aggregate is 36 that read as a trend.

Conclusion

Write the data tabs first, derive every chart reference from the frame's length rather than typing a range, and anchor charts to cells in a uniform column grid so the layout survives an edit. Put the dashboard sheet first, hide its gridlines, and keep the chart count low — the sheet exists so that nobody has to open the tabs behind it.

Frequently asked questions

Can a chart on one sheet reference data on another? Yes — that is the normal arrangement for a dashboard. In xlsxwriter the series categories and values take a '=SheetName!$A$2:$A$10' style reference, and in openpyxl the Reference object takes the worksheet as its first argument.

How do I stop the chart breaking when the data grows? Size the reference from the data you just wrote rather than hard-coding a range, or point the series at an Excel table, which grows with its rows. Hard-coded ranges are the single most common cause of a chart that quietly stops including recent data.

Why is my chart blank when the file opens? Usually the series reference names a sheet that does not exist under that exact name, or the range is off by the header row. Sheet names in references are case-sensitive and must be quoted if they contain a space.

Can I position charts precisely? In xlsxwriter, insert_chart takes a cell anchor plus x and y offsets and a scale. Anchoring each chart to a known cell in the layout grid is more maintainable than offsets alone, because inserting a row moves everything together.

Up one level: Building Multi-Sheet Excel Dashboards — the workbook structure this page sits in.
Add a KPI Summary Block to an Excel Dashboard — the headline strip above these charts.
Add a Line Chart to an Excel Report with Python — the openpyxl charting API in depth.
Add a Chart to an Excel File with XlsxWriter — series options, axes and styling.
Write Multiple DataFrames to One Excel File — creating the tabs the charts read from.