Python Excel AutomationPractical guides for developers
Start learning

Guide

Automating Reporting WorkflowsDeep dive

Building Multi-Sheet Excel Dashboards

Build a multi-sheet Excel dashboard in Python: a Summary tab with KPIs and a chart, detail sheets behind it, cross-sheet hyperlinks, frozen headers, and tab order.

A dashboard is more than a pile of tabs. It is a workbook with a deliberate front page — a Summary sheet that shows the headline numbers and a chart — backed by detail sheets a reader can drill into. Building one in Python means writing several DataFrames into a single file, computing the KPIs that go on the front, embedding a chart bound to the underlying data, and wiring up navigation so the workbook feels designed rather than dumped. This cluster sits inside Automating Reporting Workflows: once a script can produce a polished dashboard on its own, you can schedule it and email the result without a human ever opening Excel.

The narrative below builds one dashboard end to end. Each section adds a layer to the same workbook: structure, then data, then the summary, then the chart, then navigation, then the finishing touches that make a tab order feel intentional.

Structure the workbook as Summary plus detail

Decide the layout before you write a cell. A reliable pattern for a sales report is one Summary sheet at the front and one detail sheet per dimension — here, sales by region and sales by month. Build the source DataFrames once and keep them around; every later stage reads from these:

Python
import pandas as pd

sales = pd.DataFrame({
    "region": ["North", "South", "North", "West", "South", "West"],
    "month":  ["Jan", "Jan", "Feb", "Feb", "Mar", "Mar"],
    "product": ["Widget", "Gadget", "Widget", "Gizmo", "Gadget", "Widget"],
    "revenue": [12500, 9800, 14200, 7600, 11100, 8300],
    "units":   [125, 98, 142, 76, 111, 83],
})

by_region = sales.groupby("region", as_index=False)[["revenue", "units"]].sum()
by_month = sales.groupby("month", as_index=False)[["revenue", "units"]].sum()

print(by_region)
print(by_month)

as_index=False keeps the grouping column as an ordinary column so it writes cleanly to Excel without an index leaking in. You now have three tables to place: the two details and a summary you will compute next.

Write every DataFrame to one file with a single ExcelWriter

One workbook means one pd.ExcelWriter, opened once as a context manager. Each to_excel() call targets a distinct sheet_name; the order of the calls becomes the left-to-right tab order. Opening a second writer on the same path would truncate the file and throw away the first writer's sheets, so keep everything inside one with block:

Python
import pandas as pd

sales = pd.DataFrame({
    "region": ["North", "South", "North", "West", "South", "West"],
    "month":  ["Jan", "Jan", "Feb", "Feb", "Mar", "Mar"],
    "revenue": [12500, 9800, 14200, 7600, 11100, 8300],
})
by_region = sales.groupby("region", as_index=False)["revenue"].sum()
by_month = sales.groupby("month", as_index=False)["revenue"].sum()

with pd.ExcelWriter("dashboard.xlsx", engine="openpyxl") as writer:
    by_region.to_excel(writer, sheet_name="By Region", index=False)
    by_month.to_excel(writer, sheet_name="By Month", index=False)

print("Wrote two detail sheets to dashboard.xlsx")

Use engine="openpyxl" for this cluster — it is the engine you can reopen later to add a chart, hyperlinks, and frozen panes. The dedicated long-tail Write Multiple DataFrames to One Excel File covers same-sheet stacking with startrow, append mode, and the sheet-name rules in depth.

Add a Summary sheet with KPIs

The front page carries the numbers a reader wants before any detail: totals and a couple of derived ratios. Compute them with pandas, then place a small KPI table at the top of a sheet you write first so it lands as the leftmost tab. Writing the Summary first is the simplest way to control tab order:

Python
import pandas as pd

sales = pd.DataFrame({
    "region": ["North", "South", "North", "West"],
    "revenue": [12500, 9800, 14200, 7600],
    "units":   [125, 98, 142, 76],
})

total_rev = sales["revenue"].sum()
total_units = sales["units"].sum()
avg_price = round(total_rev / total_units, 2)

kpis = pd.DataFrame({
    "Metric": ["Total Revenue", "Total Units", "Avg Unit Price"],
    "Value":  [total_rev, total_units, avg_price],
})
by_region = sales.groupby("region", as_index=False)["revenue"].sum()

with pd.ExcelWriter("dashboard.xlsx", engine="openpyxl") as writer:
    kpis.to_excel(writer, sheet_name="Summary", index=False, startrow=1)
    by_region.to_excel(writer, sheet_name="By Region", index=False)

print(kpis)

The startrow=1 leaves row 1 free for a title you can drop in with openpyxl afterward. For a fuller treatment — styling the KPI block, keeping it in sync with the detail, and making it the active tab — see Add a Summary Sheet to an Excel Report with Python.

Embed a chart on the dashboard sheet

A KPI table tells; a chart shows. openpyxl's BarChart reads its values straight from cells on a data sheet via Reference, so the chart updates whenever that data does. Reopen the finished workbook, build a chart from the By Region sheet, and anchor it onto the Summary sheet:

Python
import pandas as pd
from openpyxl import load_workbook
from openpyxl.chart import BarChart, Reference

by_region = pd.DataFrame({
    "region": ["North", "South", "West"],
    "revenue": [26700, 20900, 7600],
})
with pd.ExcelWriter("dashboard.xlsx", engine="openpyxl") as writer:
    pd.DataFrame({"Metric": ["Total Revenue"], "Value": [55200]}).to_excel(
        writer, sheet_name="Summary", index=False)
    by_region.to_excel(writer, sheet_name="By Region", index=False)

wb = load_workbook("dashboard.xlsx")
data_ws = wb["By Region"]
summary_ws = wb["Summary"]

chart = BarChart()
chart.title = "Revenue by Region"
chart.type = "col"
n = data_ws.max_row  # header + data rows
data = Reference(data_ws, min_col=2, min_row=1, max_row=n)   # revenue + header
cats = Reference(data_ws, min_col=1, min_row=2, max_row=n)   # region labels
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)

summary_ws.add_chart(chart, "D2")  # anchor top-left at cell D2
wb.save("dashboard.xlsx")
print("Embedded a bar chart on the Summary sheet")

Because the chart references cells on By Region, regenerating that sheet with fresh numbers redraws the bars automatically. For deeper chart configuration — line charts, axis titles, multiple series — see Creating Charts in Excel with openpyxl.

On the Summary sheet, give the reader a way to jump to the detail. Two approaches work. The Excel HYPERLINK formula is portable and recalculates as a live cell; openpyxl's cell.hyperlink attribute writes a stored link. Internal targets use the #'Sheet Name'!A1 syntax — note the quotes around names with spaces:

Python
from openpyxl import load_workbook

wb = load_workbook("dashboard.xlsx")
ws = wb["Summary"]

# Option A: a HYPERLINK formula (a live, recalculating cell)
ws["A10"] = '=HYPERLINK("#\'By Region\'!A1", "Go to By Region")'

# Option B: openpyxl's stored hyperlink on a normal cell
link_cell = ws["A11"]
link_cell.value = "Go to By Month"
link_cell.hyperlink = "#'By Month'!A1"
link_cell.style = "Hyperlink"   # built-in blue/underline style

wb.save("dashboard.xlsx")
print("Added cross-sheet navigation links")

Prefer the HYPERLINK formula when the link text or target is derived from data; prefer the stored hyperlink when you also want the built-in Hyperlink cell style applied in one step.

Order tabs, set the active sheet, and freeze the header

The last layer is presentation. Reorder tabs by rearranging wb._sheets, point the cursor at the Summary on open with wb.active, and freeze the header row on each detail sheet so column titles stay visible while scrolling. freeze_panes = "A2" pins everything above row 2:

Python
from openpyxl import load_workbook

wb = load_workbook("dashboard.xlsx")

# Force tab order: Summary first, then details
order = ["Summary", "By Region", "By Month"]
wb._sheets.sort(key=lambda ws: order.index(ws.title)
                if ws.title in order else len(order))

# Open the workbook on the Summary sheet
wb.active = wb.sheetnames.index("Summary")

# Freeze the header row on each detail sheet
for name in ("By Region", "By Month"):
    if name in wb.sheetnames:
        wb[name].freeze_panes = "A2"

wb.save("dashboard.xlsx")
print("Tabs:", wb.sheetnames, "| active:", wb.active.title)

Setting freeze_panes = "B2" instead would pin both the header row and the first column — useful when a detail sheet has a wide label column you want anchored as the reader scrolls right.

Frequently asked questions

Why does only my last sheet survive when I run the script twice? You almost certainly opened a fresh pd.ExcelWriter on the same path in default write mode, which truncates the file. Keep all to_excel() calls inside one with pd.ExcelWriter(...) block, or reopen with load_workbook() to add to an existing file.

Can I embed a chart with the xlsxwriter engine instead of openpyxl? Yes, but the API differs (workbook.add_chart). This cluster standardizes on openpyxl because you can load_workbook() an existing file to add charts, hyperlinks, and frozen panes after pandas has written the data. Pick one engine per workbook and stay with it.

Will the embedded chart update when the data changes? Yes, as long as the data lives in cells the chart's Reference points to. Rewrite the data sheet with new numbers and the bars redraw on open. A chart built from hard-coded values would not.

How do I make the dashboard open on the Summary tab? Set wb.active to the integer index of the Summary sheet (wb.sheetnames.index("Summary")) before saving. Excel remembers which tab was active and shows it on open.

Conclusion

A multi-sheet dashboard is built in layers: one ExcelWriter writes every DataFrame into a single file, pandas computes the KPIs for a Summary sheet you write first, openpyxl embeds a chart bound to a detail sheet, hyperlinks wire up navigation, and a final pass fixes tab order, the active sheet, and frozen headers. Because each layer reads from the data already in the workbook, the whole thing regenerates correctly every time the upstream numbers change — which is exactly what you want from a scheduled report.

Where to go next