Build a Rolling 12-Month Chart on an Excel Dashboard

A trend chart on a dashboard usually needs to show the latest twelve months — not January to December of a fixed year, and not every month since records began. Done by hand, someone edits the chart's source range every month; done carelessly in code, the script regenerates the chart with hard-coded ranges, and the workbook breaks as soon as someone pastes in another month of data. A sturdier design puts the moving window into formulas: a small helper block works out which twelve months to show from the newest date in the data, totals each one with SUMIFS, and the chart points at that block, which never moves. This page builds that with openpyxl, adds an override cell so readers can look back to an earlier period, plots the same months a year earlier for comparison, and checks with LibreOffice that the window follows new data without the chart being touched. It belongs to Building Multi-Sheet Excel Dashboards.

Prerequisites

pip install openpyxl pandas numpy

LibreOffice (soffice) is used only to recalculate the saved workbook in the checks, because openpyxl writes formulas but does not calculate them. The data is thirty months of revenue, one row per month, held in an Excel table so formulas can refer to its columns by name — the same structure as in Link Dashboard Cells to Data Sheets with Formulas.

Step 1 — The data sheet

import numpy as np
import pandas as pd
from openpyxl import Workbook
from openpyxl.worksheet.table import Table, TableStyleInfo

def sales_data(months):
    idx = pd.date_range("2024-01-01", periods=months, freq="MS")
    t = np.arange(months)
    rng = np.random.default_rng(7)
    revenue = (40000 + 900 * t + 6000 * np.sin(t / 12 * 2 * np.pi) + rng.normal(0, 1500, months)).round(-2)
    return pd.DataFrame({"Month": idx, "Revenue": revenue})

def data_sheet(wb, df):
    ws = wb.create_sheet("Data")
    ws.append(["Month", "Revenue"])
    for m, r in df.itertuples(index=False):
        ws.append([m.to_pydatetime(), float(r)])
        ws.cell(ws.max_row, 1).number_format = "mmm yyyy"
    table = Table(displayName="Sales", ref=f"A1:B{ws.max_row}")
    table.tableStyleInfo = TableStyleInfo(name="TableStyleLight9", showRowStripes=True)
    ws.add_table(table)
    return ws

sales = sales_data(30)
print(sales.iloc[[0, -1]].to_string(index=False))

Thirty months from January 2024 to June 2026, with growth, a seasonal swing and noise. Storing the months as real dates on the first of the month — not as text like "Jun 2026" — is what lets the formulas below do date arithmetic on them. In a real report this sheet is written from your source data each run, or appended to by people; either way the dashboard below adapts.

Step 2 — A helper block that works out the window

The dashboard has an end-month cell, an optional override, and twelve rows of formulas. Everything the chart shows comes from those rows:

from openpyxl.worksheet.datavalidation import DataValidation
from openpyxl.styles import Font

def dashboard(wb, df):
    ws = wb.active
    ws.title = "Dashboard"
    ws["A1"], ws["A1"].font = "Revenue — rolling 12 months", Font(size=14, bold=True)
    ws["A2"], ws["A3"] = "Show 12 months ending", "Override (optional)"
    ws["B2"] = '=IF(B3="",MAX(Sales[Month]),B3)'
    for c in ("B2", "B3"):
        ws[c].number_format = "mmm yyyy"
    dv = DataValidation(type="list", formula1="=Data!$A$2:$A$500", allow_blank=True)
    ws.add_data_validation(dv)
    dv.add("B3")
    ws.append([])
    ws["A5"], ws["B5"], ws["C5"] = "Month", "Revenue", "Same month last year"
    for i in range(12):
        r = 6 + i
        ws[f"A{r}"] = f"=EDATE($B$2,{i - 11})"
        ws[f"B{r}"] = f"=SUMIFS(Sales[Revenue],Sales[Month],A{r})"
        ws[f"C{r}"] = f'=IF(COUNTIFS(Sales[Month],EDATE(A{r},-12))=0,NA(),SUMIFS(Sales[Revenue],Sales[Month],EDATE(A{r},-12)))'
        ws[f"A{r}"].number_format = "mmm yy"
        ws[f"B{r}"].number_format = ws[f"C{r}"].number_format = "#,##0"
    ws.column_dimensions["A"].width = 22
    ws.column_dimensions["B"].width = ws.column_dimensions["C"].width = 20
    return ws

B2 is the anchor: the newest month in the data, unless the override cell B3 holds a month, in which case that wins. The month rows count back from the anchor with EDATE, which adds whole months and handles year boundaries — EDATE(June 2026, -11) is July 2025. Each month's revenue is a SUMIFS on the table, so it picks up the matching row wherever it sits in the data. The prior-year column uses NA() where there is no data a year earlier: a chart skips #N/A instead of drawing a misleading drop to zero, which is what a plain SUMIFS returning 0 would show. The override is a dropdown restricted to the data's months, so a reader cannot type a date that does not exist in the data; Add a Dropdown Filter That Drives an Excel Dashboard covers dropdowns in more detail.

Step 3 — The chart reads the fixed block

The chart's series refer to B6:B17 and C6:C17 — cells that never move — so it never needs editing:

from openpyxl.chart import BarChart, LineChart, Reference

def add_chart(ws):
    cats = Reference(ws, min_col=1, min_row=6, max_row=17)
    bars = BarChart()
    bars.add_data(Reference(ws, min_col=2, min_row=5, max_row=17), titles_from_data=True)
    bars.set_categories(cats)
    line = LineChart()
    line.add_data(Reference(ws, min_col=3, min_row=5, max_row=17), titles_from_data=True)
    line.series[0].marker.symbol = "none"
    bars += line
    bars.title, bars.y_axis.title = "Revenue, latest 12 months", "Revenue"
    bars.y_axis.numFmt = "#,##0"
    bars.x_axis.number_format = "mmm yy"
    bars.x_axis.delete = bars.y_axis.delete = False
    bars.legend.position = "b"
    bars.width, bars.height = 22, 9
    ws.add_chart(bars, "E2")

def build(path, months):
    wb = Workbook()
    df = sales_data(months)
    ws = dashboard(wb, df)
    data_sheet(wb, df)
    add_chart(ws)
    wb.save(path)

build("rolling.xlsx", 30)

The bars are this year's revenue and the line the same months a year earlier, combined in one chart with bars += line, so seasonality and growth are visible at a glance. Setting delete = False on both axes matters: without it, recent Excel versions hide the axes of charts written by openpyxl. More on combining chart types is in Add a Combo Chart with a Secondary Axis in openpyxl.

Step 4 — Check that the window moves

Recalculate the workbook with LibreOffice, read the helper block, and compare it with the same window computed in pandas. Then build again with three more months of data, and once more with an override, and see where the window lands:

import subprocess
from openpyxl import load_workbook

def recalc(path):
    subprocess.run(["soffice", "--headless", "--convert-to", "xlsx", "--outdir", "calc", path], check=True, capture_output=True)
    return load_workbook(f"calc/{path}", data_only=True)["Dashboard"]

def window(ws):
    return [(ws[f"A{r}"].value, ws[f"B{r}"].value, ws[f"C{r}"].value) for r in range(6, 18)]

w = window(recalc("rolling.xlsx"))
print("window:", w[0][0].strftime("%b %Y"), "to", w[-1][0].strftime("%b %Y"))
expected = sales.set_index("Month")["Revenue"].iloc[-12:]
print("matches pandas:", [b for _, b, _ in w] == expected.tolist())
print("prior year, first and last:", w[0][2], w[-1][2])

build("rolling.xlsx", 33)
w = window(recalc("rolling.xlsx"))
print("after 3 more months:", w[0][0].strftime("%b %Y"), "to", w[-1][0].strftime("%b %Y"))

wb = load_workbook("rolling.xlsx")
wb["Dashboard"]["B3"] = pd.Timestamp("2025-06-01").to_pydatetime()
wb.save("rolling.xlsx")
w = window(recalc("rolling.xlsx"))
print("with override:", w[0][0].strftime("%b %Y"), "to", w[-1][0].strftime("%b %Y"), "| first prior-year cell:", w[0][2])

With thirty months, the window runs from July 2025 to June 2026 and matches the pandas calculation exactly; the prior-year line starts at 45,500 for July 2024 and ends at 57,600 for June 2025. With three more months of data the same formulas show October 2025 to September 2026 — the chart moved without being touched. With the override set to June 2025 the window shows July 2024 to June 2025, and the first prior-year cell, July 2023, is #N/A (read back as the error string), because the data starts in January 2024: the chart simply leaves that point out. Recalculating with LibreOffice this way is a cheap regression test for any formula-driven dashboard; the general technique is covered in Recalculate Excel Formulas Without Excel in Python.

Why not a dynamic named range?

A common alternative is to define a name with OFFSET — something like =OFFSET(Data!$B$1,COUNT(Data!$B:$B)-11,0,12,1) — and point the chart series at the name. It works in desktop Excel, but it has drawbacks for generated workbooks. OFFSET is volatile, recalculating on every change anywhere in the workbook. Charts that refer to names are not supported the same way by every application that might open the file, including some web and mobile viewers and LibreOffice. And the window then depends on the physical order of the data rows, so a sort or an out-of-order paste silently changes what is plotted. The helper block avoids all three: its formulas are ordinary, its range is fixed, and SUMIFS finds each month by value, wherever the row is. It also gives readers something visible to check the chart against.

Common pitfalls

• Months stored as text. EDATE and MAX need real dates.
• Zeros for missing months. Return NA() so the chart skips the point.
• Hidden axes. Set delete = False on openpyxl chart axes.
• Hard-coded chart ranges in the data sheet. Point the chart at the helper block.
• Duplicate rows for a month. SUMIFS adds them together; deduplicate the data first.

Conclusion

To give a dashboard a chart that always shows the latest twelve months, anchor a helper block on MAX of the data's month column (or an override month), count back with EDATE, total each month with SUMIFS and use NA() where a comparison month has no data. Point the chart at the helper block's fixed cells. New data moves the window by itself, readers can look back by picking a month, and a LibreOffice recalculation confirms the window lands where it should.

Frequently asked questions

How do I make an Excel chart show the last 12 months automatically? Build a helper block whose month cells count back from the latest date with EDATE, total each month with SUMIFS, and point the chart at that block; new data moves the window without editing the chart.

How do I stop a chart dropping to zero for months with no data? Return NA() instead of 0 for those cells; charts skip #N/A values rather than plotting them.

Can a dynamic named range with OFFSET drive the chart instead? In desktop Excel, yes, but it is volatile, depends on row order and is not supported equally by all viewers; a fixed helper block is more robust.

How do I let readers look at an earlier 12-month period? Add an override cell with a dropdown of months and make the end month IF(override="", MAX(months), override).

Related

• Up one level: Building Multi-Sheet Excel Dashboards — dashboards from Python.
• Build a Dashboard Sheet with Charts from Multiple Tabs — charts on a summary sheet.
• Add a KPI Summary Block to an Excel Dashboard — headline figures above the chart.
• Calculate Month-to-Date and Year-to-Date Totals with pandas — period totals in Python.