Check That Totals in a Report Add Up

The most embarrassing error in a report is a total that does not add up. A reader runs a calculator down a column, gets a different number, and from then on trusts nothing in the document. It happens more often than it should: a total typed over a formula during a late correction, a row added below a SUM range, a summary sheet copied from last month's file, a rounding difference that grew. Checking every total by hand is exactly the kind of job people skip under deadline pressure, and exactly the kind a script does well. This page builds a checker that reads a finished report workbook, recomputes every total row, total column and grand total, compares the summary sheet with the detail it summarises, accepts differences within a rounding tolerance, and lists each failure with its sheet and cell so it can be found in seconds. It belongs to Validating Excel Data with Python.

Prerequisites

pip install pandas openpyxl

The report under test has a detail sheet, By region, with three quarters per region, a Total column on the right and a Total row at the bottom, and a Summary sheet quoting a few figures from it. The workbook holds values, as a finished report sent to readers usually does; for a workbook with live formulas, read it with data_only=True after it has been calculated, or recalculate it with LibreOffice first.

Step 1 — Read the table with cell addresses kept

import pandas as pd
from openpyxl import load_workbook

wb = load_workbook("quarterly_report.xlsx", data_only=True)
ws = wb["By region"]
header = [c.value for c in ws[1]]
rows = [[c for c in r] for r in ws.iter_rows(min_row=2)]
table = pd.DataFrame([[c.value for c in r] for r in rows], columns=header).set_index("Region")
addr = pd.DataFrame([[c.coordinate for c in r] for r in rows], columns=header).set_index(pd.Index(table.index))
print(table.to_string())

The checker needs both the values and where they live, so a failure can be reported as E3 rather than row 2, column 4. Reading the sheet with openpyxl gives cell objects that carry both; two parallel DataFrames — one of values, one of cell addresses, with the same index and columns — keep them together through every calculation that follows. data_only=True returns calculated results rather than formula text, which is what a check of a finished report needs.

Step 2 — Total rows, total columns and the grand total

TOL = 0.015                                         # accept differences up to 1.5p from rounding
detail = table.drop(index="Total")
quarters = ["Q1", "Q2", "Q3"]
failures = []

def check(where, expected, shown, label):
    if shown is None or pd.isna(shown):
        failures.append({"Cell": where, "Check": label, "Shown": None, "Expected": round(expected, 2), "Difference": None})
    elif abs(shown - expected) > TOL:
        failures.append({"Cell": where, "Check": label, "Shown": shown, "Expected": round(expected, 2),
                         "Difference": round(shown - expected, 2)})

for region, row in detail.iterrows():                       # each region's Total column
    check(f"'By region'!{addr.loc[region, 'Total']}", row[quarters].sum(), row["Total"], f"{region}: row total")
for q in quarters:                                          # each quarter's Total row
    check(f"'By region'!{addr.loc['Total', q]}", detail[q].sum(), table.loc["Total", q], f"{q}: column total")
grand = table.loc["Total", "Total"]
check(f"'By region'!{addr.loc['Total', 'Total']}", detail[quarters].to_numpy().sum(), grand, "grand total vs all cells")
check(f"'By region'!{addr.loc['Total', 'Total']}", table.loc["Total", quarters].sum(), grand, "grand total vs total row")
check(f"'By region'!{addr.loc['Total', 'Total']}", detail["Total"].sum(), grand, "grand total vs total column")
print(pd.DataFrame(failures).to_string(index=False))

Each check recomputes a figure from the cells it summarises and compares it with the figure the report shows. Row totals are the sum across the quarters, column totals the sum down the regions. The grand total gets three checks — against the sum of every detail cell, against the sum of the total row and against the sum of the total column — because a report can be internally inconsistent in ways that only one of them catches. Accountants call this cross-footing. Differences up to a penny and a half pass, because figures rounded to the penny legitimately disagree by a penny when summed; anything larger is a failure. A missing total is reported too.

The run finds two problems. South's row total shows 311.50 against a computed 311.55 — the kind of five-pence slip left by a total typed over a formula. The Q2 column total is a penny out, which the tolerance accepts. And because South's row total is wrong, the grand total no longer agrees with the sum of the total column, even though it agrees with the detail cells — the cross-footing check pinpoints that it is a row total, not the grand total, that is wrong.

Step 3 — The summary sheet against the detail

summary = {r[0].value: (r[1].value, r[1].coordinate) for r in wb["Summary"].iter_rows(min_row=2)}
expected = {"Revenue Q1–Q3": detail[quarters].to_numpy().sum(),
            "North Q3": detail.loc["North", "Q3"]}
for measure, value in expected.items():
    shown, cell = summary[measure]
    check(f"'Summary'!{cell}", value, shown, f"summary: {measure}")
best = detail["Total"].idxmax()
if summary["Best region"][0] != best:
    failures.append({"Cell": f"'Summary'!{summary['Best region'][1]}", "Check": "summary: best region",
                     "Shown": summary["Best region"][0], "Expected": best, "Difference": None})
report = pd.DataFrame(failures)
print(report.to_string(index=False))

Summary sheets are where stale numbers hide: a figure copied by hand from the detail, or left over from last month's file. Each summary figure is recomputed from the detail and compared in the same way, including non-numeric facts such as which region was best. The summary's revenue figure is the one that would have embarrassed someone: it shows 1,146.08 against 1,147.08 in the detail — exactly a pound short, the signature of a figure retyped by hand with one digit wrong. North Q3 matches exactly. The best region is checked against the row totals — which also means that if a row total were wrong enough, this check would flag it too.

Step 4 — Make it a gate before sending

def check_report(path):
    """Return a DataFrame of failed checks; empty means the report adds up."""
    # (the checks above, wrapped in a function)
    return report

if len(report):
    report.to_excel("quarterly_report_CHECKS_FAILED.xlsx", index=False)
    print(f"{len(report)} check(s) failed — report not sent")
else:
    print("all totals agree — OK to send")

The checks are most useful as a gate: the report job runs them after building the workbook and before emailing it, and refuses to send if anything fails, writing the failures to a file the person on duty can open. A failure list with sheet-qualified cell addresses — 'By region'!E3 — can be pasted into Excel's Go To box, which takes the reader straight to the cell. The same gate fits the workflow in Validate an Excel Report Before Sending It.

Beyond simple sums

Not every total in a report is a sum, and the same approach extends to the others. Averages and percentages should be recomputed from their parts rather than averaged again — an average of regional margins is not the overall margin unless the regions are the same size. Balances should follow their movements: an opening balance plus the month's movements equals the closing balance, the check used for bank statements in Build a Cash Position Report from Bank Statements. And figures that appear in two places — a revenue number in the summary, the chart title and the email text — should all be compared with the one source. Each of these is one more call to the same check function with a different expected value, so the list of checks grows with the report without the code becoming complicated.

Common pitfalls

• Reading formulas instead of values. Use data_only=True on a calculated workbook.
• No tolerance. Rounded figures legitimately differ by a penny; allow for it, but no more.
• Checking the grand total one way. Cross-foot it against detail, total row and total column.
• Ignoring the summary sheet. Recompute every summary figure from the detail.
• Failures without addresses. Report sheet and cell so the error can be found at once.

Conclusion

To check that a report's totals add up, read the sheet with openpyxl keeping cell addresses alongside the values, recompute every row total, column total and the grand total — cross-footed three ways — and every figure on the summary sheet, and compare each with what the report shows within a small rounding tolerance. List the failures by sheet and cell, and run the checks as a gate that stops the report being sent until they pass.

Frequently asked questions

How do I check that Excel totals are correct with Python? Read the values, recompute each total from the cells it covers, and compare with the stored total within a small tolerance.

What is cross-footing? Checking that the sum of the row totals and the sum of the column totals both equal the grand total, and that it equals the sum of all detail cells.

Why do my totals differ by a penny? Each figure was rounded before summing; allow a tolerance of about a penny for such differences.

How do I report which cell is wrong? Keep each cell's coordinate alongside its value when reading the sheet and include it in the failure list.

Related

• Up one level: Validating Excel Data with Python — checking data quality.
• Validate an Excel Report Before Sending It — the sending gate.
• Write Grouped Data with Blank Rows Between Groups — totals written as formulas.
• Match Excel Rounding in pandas — why rounded figures disagree.