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 guide 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 formatting, then the chart, then navigation, then the finishing touches that make a tab order feel intentional. The recurring rhythm you will see is write with pandas, reopen with openpyxl, save — pandas is excellent at laying tabular data into cells, but the chart, the hyperlinks, the frozen panes, and the number formats are all openpyxl operations applied to the finished file. Getting that two-phase pattern right is most of the work, and it is why every stage here reads from the same source DataFrames rather than re-querying or re-reading the sheet.

Anatomy of a multi-sheet Excel dashboard A front Summary sheet showing KPIs and a bar chart sits leftmost, backed by several detail sheets; the worksheet tabs run along the bottom with Summary first. Summary $2.4M Revenue +18% Growth 312 Orders KPI chart Detail rows feed the KPIs and chart Summary By Region By Month By Product tabs

Structure the workbook as Summary plus detail

Decide the layout before you write a cell. A dashboard is easiest to reason about when every sheet has one job: a Summary sheet at the front for the headline numbers, and one detail sheet per dimension you want a reader to be able to drill into — here, sales by region, sales by month, and sales by product. Pick your dimensions from the questions the report is meant to answer, not from every column you happen to have; three focused detail sheets read better than nine thin ones. Build the source DataFrames once and keep them around, because every later stage — the KPIs, the chart, the frozen headers — reads from these rather than from the file on disk:

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()
by_product = sales.groupby("product", 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 into column A — the same detail you have to manage any time you are writing DataFrames to Excel with pandas. Sort each detail frame the way a reader expects to see it — by_region.sort_values("revenue", ascending=False) puts the biggest region first, which also makes the chart you build later read top-down. You now have three tables to place plus a summary you will compute next; keeping them as named variables means the KPI block and the chart both trace back to one authoritative set of numbers.

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" here — it is the engine you can reopen later with load_workbook() to add a chart, hyperlinks, and frozen panes. xlsxwriter is faster and has a richer native charting API, but it can only write a new file, never reopen one, so a dashboard built in two phases has to standardize on openpyxl. Keep sheet names to Excel's 31-character limit and clear of the characters Excel forbids (\ / ? * [ ] :); pandas will raise if you break the rule, but it is easier to name sheets defensively up front. The dedicated guide Write Multiple DataFrames to One Excel File covers same-sheet stacking with startrow, append mode, and the sheet-name rules in depth, and the broader multi-sheet fundamentals explain how a workbook holds several sheets at once.

The two-phase dashboard build pipeline Phase one uses a pandas ExcelWriter to write the data sheets into dashboard.xlsx. That same file is reopened in phase two with openpyxl load_workbook to add number formats, an embedded chart, cross-sheet hyperlinks and frozen panes, and a save arrow loops back to the same file, showing the file itself is the handoff between the phases. Phase 1 · pandas with pd.ExcelWriter(...) • Summary → sheet • By Region → sheet • By Month → sheet dashboard.xlsx Phase 2 · openpyxl load_workbook(...) • number formats • embedded chart • cross-sheet links • frozen panes write reopen wb.save() writes back to the same file

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. Compute the KPIs from the DataFrame in this same run rather than reading them back out of the detail sheet — reading formatted cells back is slower and can hand you a string like "$12,500" where you expected a number. Because total_rev and friends come from the same sales frame the detail sheets were built from, the Summary can never silently disagree with the pages behind it. 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.

Format the KPI block so it reads as a front page

Raw numbers on a white grid do not look like a dashboard. Two openpyxl passes fix that: a number format so revenue reads as currency and ratios round predictably, and a little cell styling so the title and the KPI headers stand apart from the body. Both are applied after pandas has written the values, on the reopened workbook, so nothing you style gets overwritten by a later to_excel():

Python
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill, Alignment

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

# A title in the row we left free with startrow=1
ws["A1"] = "Q2 Sales Dashboard"
ws["A1"].font = Font(size=14, bold=True, color="1F2937")

# Header row of the KPI table (row 2 after startrow=1)
for cell in ws[2]:
    cell.font = Font(bold=True, color="FFFFFF")
    cell.fill = PatternFill("solid", fgColor="4338CA")
    cell.alignment = Alignment(horizontal="left")

# Currency format on the Value column for the revenue row
ws["B3"].number_format = '$#,##0'
ws.column_dimensions["A"].width = 18
ws.column_dimensions["B"].width = 14

wb.save("dashboard.xlsx")
print("Styled the Summary front page")

The number_format string is Excel's own format language — '$#,##0' for whole-dollar currency, '0.0%' for a percentage, '#,##0.00' for two-decimal thousands — and it changes only how the cell displays, never the underlying number, so the chart still sees a real value. Applying number and date formats in Excel works through the format codes in detail, and Styling Excel cells with openpyxl covers fonts, fills, borders, and alignment for the header treatment above. A common finishing touch is dropping a company mark into the corner of the Summary — see inserting images and logos into Excel for that.

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")

A couple of touches make the embedded chart look deliberate. Size it in centimetres with chart.width and chart.height so it fills the space next to the KPI table rather than defaulting to a squat 15x7.5, and set chart.y_axis.title / chart.x_axis.title to label the axes:

Python
chart.width = 14      # centimetres
chart.height = 8
chart.y_axis.title = "Revenue"
chart.x_axis.title = "Region"
chart.legend = None   # a single-series bar chart needs no legend

Because the chart references cells on By Region, regenerating that sheet with fresh numbers redraws the bars automatically — that live binding is the whole point of anchoring a Reference rather than pasting a static image. Build the chart from the aggregated detail rather than the raw sales rows, so the bars match the numbers a reader sees when they click through to the detail tab. For deeper chart configuration — line charts, axis titles, stacked and 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. For navigation that feels complete, add a return link at the top of each detail sheet that points back to #'Summary'!A1, so a reader who drills in can get home in one click without hunting for the tab. Loop over the detail sheets and write the same "Back to Summary" cell into A1 of each — a few lines that turn a pile of tabs into something a reader can actually move around in.

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. One more cue costs a single line: wb["Summary"].sheet_properties.tabColor = "4338CA" colours the Summary tab so it stands out from the detail tabs at a glance. With the layers stacked, the script now produces a finished workbook every time it runs — which is exactly the shape of thing you want to hand to a scheduler. Point cron at this script to rebuild the dashboard on a timer, then have the same job email the workbook so the finished file lands in an inbox without anyone opening Python.

Let the tabs tell a story

A dashboard's tab order is its table of contents. Summary first, then the detail sheets in the order a reader would ask for them, then reference data hidden at the end. Setting the active sheet to the summary and turning gridlines off there costs two lines and makes the difference between a workbook that reads as a report and one that reads as an export somebody forgot to finish.

The tab order is the contents page

How a dashboard's tabs should be ordered The summary comes first and opens by default. Detail sheets follow in the order a reader would ask for them. Reference and lookup data is hidden at the end. 1 · Summary opens here gridlines off KPIs and a chart 2 · Detail one per audience filterable consistent formatting 3 · Lookups hidden feeds formulas not for reading

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 guide 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, and the order matters. One pd.ExcelWriter writes every DataFrame into a single file in one with block; pandas computes the KPIs for a Summary sheet you write first so it lands leftmost; openpyxl reopens the file to format the KPI block, embed a chart bound to a detail sheet, wire up cross-sheet hyperlinks, and — in a final pass — fix tab order, the active sheet, the tab colour, and frozen headers. The single idea underneath all of it is the two-phase pattern: write the data with pandas, then reopen the finished file with load_workbook() to add everything Excel-specific, so nothing you style is ever clobbered by a later data write. Because every layer reads from the source DataFrames and the chart binds to live cells, the whole workbook regenerates correctly each time the upstream numbers change — which is exactly what you want from a report a scheduler runs unattended.

Where to go next