Python Excel AutomationPractical guides for developers
How-to
Automating Reporting WorkflowsDeep dive

Link Dashboard Cells to Data Sheets with Formulas

Build an Excel dashboard whose figures are live formulas pointing at the data sheets — SUMIFS and COUNTIFS over a table, a named report-month cell readers can change, quoted sheet names, errors handled with IFERROR — so the dashboard updates when the data does, and check the results without Excel.
Report layouts & templatesFormulas in workbooksopenpyxl

A dashboard sheet computed in pandas and written as values is a snapshot: correct the day it was produced, wrong as soon as someone adds a row to the data sheet or wants to see last month instead. A dashboard made of formulas that point at the data sheets stays consistent with the data it sits next to, and lets readers change inputs — the report month, a region — and see every figure update. This guide, part of Building Multi-Sheet Excel Dashboards, writes such a dashboard with openpyxl, using table references and a named input cell, and verifies the numbers without opening Excel.

Dashboard figures computed from the data sheet The dashboard's revenue, order count, average order and North revenue cells are formulas. Each reads the Sales table on the Sales Data sheet and the ReportMonth input cell on the dashboard itself, so changing the month or adding rows updates every figure. Sales table 'Sales Data'!A1:E7 Revenue this month SUMIFS(Sales[Revenue], …) Orders this month COUNTIFS(Sales[Date], …) Average order IFERROR(B3/B4, 0) North revenue SUMIFS(… "North" …)

Prerequisites

Bash
pip install openpyxl pandas

A data sheet with a named table — the dashboard's formulas will refer to it by name:

Python
import datetime as dt

from openpyxl import Workbook
from openpyxl.workbook.defined_name import DefinedName
from openpyxl.worksheet.table import Table, TableStyleInfo

wb = Workbook()
dash = wb.active
dash.title = "Dashboard"
data = wb.create_sheet("Sales Data")
data.append(["Date", "Region", "Product", "Revenue", "Units"])
for row in [(dt.date(2026, 8, 3), "North", "Tea", 1200, 40), (dt.date(2026, 8, 9), "South", "Coffee", 950, 25),
            (dt.date(2026, 9, 2), "North", "Coffee", 1400, 35), (dt.date(2026, 9, 5), "East", "Tea", 600, 20),
            (dt.date(2026, 9, 12), "North", "Tea", 800, 30), (dt.date(2026, 9, 20), "South", "Tea", 700, 28)]:
    data.append(row)
sales = Table(displayName="Sales", ref=f"A1:E{data.max_row}")
sales.tableStyleInfo = TableStyleInfo(name="TableStyleLight9", showRowStripes=True)
data.add_table(sales)

Step 1 — A named input cell

The report month is an input: readers should be able to change it. Put it in one cell on the dashboard and give that cell a name, so formulas read ReportMonth rather than Dashboard!$B$1:

Python
dash["A1"] = "Report month"
dash["B1"] = dt.date(2026, 9, 1)
dash["B1"].number_format = "mmm yyyy"
wb.defined_names["ReportMonth"] = DefinedName("ReportMonth", attr_text="Dashboard!$B$1")
print(wb.defined_names["ReportMonth"].attr_text)       # Dashboard!$B$1

Named inputs make formulas self-explanatory and keep working if the cell moves when someone inserts a row above it. A data validation list on the input cell restricts it to valid months; filters of that kind are covered in Add a Dropdown Filter That Drives an Excel Dashboard. Named ranges in general are covered in Create a Named Range in Excel with openpyxl.

Step 2 — Figures as formulas over the table

Each figure is a conditional aggregate over table columns. SUMIFS and COUNTIFS with date bounds pick the report month; EOMONTH finds its last day:

Python
MONTH = '">="&ReportMonth, Sales[Date], "<="&EOMONTH(ReportMonth,0)'
figures = [
    ("Revenue this month", f"=SUMIFS(Sales[Revenue], Sales[Date], {MONTH})", "#,##0"),
    ("Orders this month", f"=COUNTIFS(Sales[Date], {MONTH})", "0"),
    ("Average order", "=IFERROR(B3/B4, 0)", "#,##0"),
    ("Last order date", "=MAX(Sales[Date])", "yyyy-mm-dd"),
]
for row, (label, formula, fmt) in enumerate(figures, start=3):
    dash.cell(row, 1, label)
    dash.cell(row, 2, formula).number_format = fmt
wb.save("dashboard.xlsx")
print(dash["B3"].value)

Structured references — Sales[Revenue] rather than 'Sales Data'!D2:D7 — name the column, follow the table as it grows and survive inserted columns. IFERROR keeps the average from showing #DIV/0! for a month with no orders. The pandas equivalents of these functions are covered in SUMIF and SUMIFS Equivalent in pandas and AVERAGEIFS, MAXIFS and MINIFS Equivalents in pandas.

Step 3 — Plain references to other sheets

Not everything is a table. When a formula refers to an ordinary range on another sheet, the sheet name needs single quotes if it contains spaces or punctuation — and those quotes sit happily inside a Python string built with double quotes:

Python
dash["A8"] = "North revenue this month"
dash["B8"] = ("=SUMIFS('Sales Data'!$D:$D, 'Sales Data'!$B:$B, \"North\", "
              "'Sales Data'!$A:$A, \">=\"&ReportMonth, 'Sales Data'!$A:$A, \"<=\"&EOMONTH(ReportMonth,0))")
dash["B8"].number_format = "#,##0"
wb.save("dashboard.xlsx")

Whole-column references such as $D:$D pick up new rows automatically and ignore the text header for sums. Quote every sheet name in generated formulas, even ones without spaces — 'Summary'!A1 is valid — so the code keeps working when a sheet is later given a name like "Summary 2026".

The dashboard after calculation With the report month set to September 2026, the dashboard shows revenue of 3,500, four orders, an average order of 875, a last order date of 20 September 2026 and North revenue of 2,200, all calculated by formulas from the Sales table. fx =SUMIFS(Sales[Revenue],…,ReportMonth) A B 1 Report month Sep 2026 2 3 Revenue this month 3,500 4 Orders this month 4 5 Average order 875 6 Last order date 2026-09-20 7 8 North revenue this month 2,200 Dashboard Sales Data the gold cell is the input; the blue cells recalculate from it

Step 4 — Check the figures without Excel

openpyxl writes formulas but no results, so reading the saved file back with pandas shows empty dashboard cells. Recalculate with headless LibreOffice to check the numbers, and compare them with the same figures computed in pandas:

Python
import subprocess

import pandas as pd

subprocess.run(["soffice", "--headless", "--convert-to",
                "csv:Text - txt - csv (StarCalc):44,34,76,1,,0,false,true,true", "dashboard.xlsx"],
               check=True, capture_output=True, timeout=120)
shown = dict(line.split(",", 1) for line in open("dashboard.csv").read().splitlines() if "," in line)
print(shown["Revenue this month"], shown["Orders this month"], shown["North revenue this month"])
# "3,500" 4 "2,200"

Recalculated, September shows revenue of 3,500 from four orders — an average of 875 — and North revenue of 2,200. Compute the same figures independently in pandas from the data sheet, and the two must agree:

Python
import pandas as pd

frame = pd.read_excel("dashboard.xlsx", sheet_name="Sales Data")
month = frame[(frame["Date"] >= "2026-09-01") & (frame["Date"] <= "2026-09-30")]
expected = {
    "Revenue this month": month["Revenue"].sum(),
    "Orders this month": len(month),
    "North revenue this month": month.loc[month["Region"] == "North", "Revenue"].sum(),
}
print(expected)
# {'Revenue this month': np.int64(3500), 'Orders this month': 4, 'North revenue this month': np.int64(2200)}

Wrapped in a test that compares expected with the recalculated values, this check runs every time the dashboard layout changes. A mismatch points at a formula with a wrong column, date bound or sheet reference. Other ways to recalculate are covered in Recalculate Excel Formulas Without Excel in Python.

A dashboard of formulas or a dashboard of values A formula dashboard updates when the data or the report month changes and stays consistent with the data sheet, but other programs see empty cells unless the file is recalculated. A value dashboard is a fixed snapshot that any program can read, but goes stale when data is added or readers want another period. Formulas updates with the data readers change inputs empty for non-calculating readers extend tables as data grows Values from pandas readable by any program exactly what was computed stale after edits one period per file

Step 5 — Keep the table in step with new data

Structured references follow the table — but the table itself only grows when Excel adds rows to it. When a script appends rows to the data sheet with openpyxl, extend the table's range as well, or the new rows fall outside every formula:

Python
from openpyxl import load_workbook

wb = load_workbook("dashboard.xlsx")
data = wb["Sales Data"]
data.append([dt.date(2026, 9, 28), "East", "Coffee", 1100, 22])
table = data.tables["Sales"]
table.ref = f"A1:E{data.max_row}"
table.autoFilter.ref = table.ref
wb.save("dashboard.xlsx")
print(table.ref)                                       # A1:E8

After this, recalculating shows September revenue of 4,600 from five orders. Forgetting the range update is the most common reason a formula-driven dashboard "stops counting" new data.

Step 6 — Compare with the previous month

Once the month is an input, comparisons come almost free. EDATE(ReportMonth,-1) is the first day of the previous month, so last month's revenue and the change are two more formulas:

Python
dash = wb["Dashboard"]
dash["A10"] = "Revenue last month"
dash["B10"] = ('=SUMIFS(Sales[Revenue], Sales[Date], ">="&EDATE(ReportMonth,-1), '
               'Sales[Date], "<"&ReportMonth)')
dash["A11"] = "Change vs last month"
dash["B11"] = '=IFERROR(B3/B10-1, "")'
dash["B10"].number_format = "#,##0"
dash["B11"].number_format = "+0.0%;-0.0%;0.0%"
wb.save("dashboard.xlsx")

Recalculated with the extra September row from Step 5, August revenue is 2,150 and the change shows +114.0%. Change the report month to August and every figure — including the comparison, which now looks at July — follows. The IFERROR returns an empty string when there is no previous month to compare with, rather than a division error. Growth calculations in pandas are covered in Running Totals and Year-over-Year Growth in pandas.

Common pitfalls

  • Unquoted sheet names. =SUM(Sales Data!D:D) is a syntax error; quote names with spaces, and preferably all names.
  • Hard-coded months in formulas. ">=2026-09-01" in every formula means editing every formula next month. Reference one input cell.
  • Volatile helpers. INDIRECT and OFFSET recalculate on every change and break silently when sheets are renamed. Table references and named cells do the same jobs more robustly.
  • Readers of the file that do not calculate. If another program reads the dashboard, recalculate the file or store the values too.

Conclusion

A formula-driven dashboard stays consistent with its data and lets readers change inputs. Put inputs such as the report month in named cells, compute figures with SUMIFS, COUNTIFS and friends over structured table references, quote sheet names in plain references, guard divisions with IFERROR, extend the table's range whenever a script adds data, and check the results by recalculating the file with LibreOffice.

Frequently asked questions

Should dashboard figures be formulas or values? Formulas when readers will change inputs such as the report month, or when data is added to the workbook after it is generated. Values when the dashboard is a fixed snapshot. Formulas keep the dashboard and data consistent; values are simpler for other programs to read.

How do I reference a sheet name with spaces in a formula? Wrap it in single quotes, as in ='Sales Data'!D2. Inside a Python string that contains double quotes, keep the single quotes as they are.

Why are my dashboard cells empty when pandas reads the file? openpyxl writes formulas without calculated results. Excel calculates them when the file is opened, but pandas and other readers see nothing. Recalculate the file, for example with LibreOffice, or also store the values if other programs must read them.

What is the advantage of table references like SalesRevenue? They follow the table as it grows, name the column instead of a letter and survive columns being inserted. A formula over SalesRevenue keeps working when rows are added inside the table.

More on report layouts & templates

More on report layouts & templates →