Python Excel AutomationPractical guides for developers
How-to
Advanced Data Transformation And CleaningDeep dive

Remove Subtotal and Total Rows from an Excel Export

Subtotal and grand-total rows double every sum you compute. Find them by label, by missing detail fields or by SUBTOTAL formulas, remove them, and use the totals to check your own numbers.
Messy spreadsheetsData cleaningpandas

Reports exported from accounting packages, BI tools and Excel's own Data → Subtotal command interleave detail rows with rollups: two rows for the North region, then a North Total, two rows for South, a South Total, and a Grand Total at the bottom. Read that sheet with pandas and sum the revenue column, and the answer is three times the truth — every euro is counted once in its detail row, once more in its region's subtotal, and a third time in the grand total. This guide, part of Extracting Data from Messy Excel Sheets, removes those rows reliably and then puts them to work as a free correctness check.

Detail rows interleaved with subtotal and grand total rows Rows 2 and 3 are North detail rows, row 4 is the North subtotal, rows 5 and 6 are South detail rows, row 7 is the South subtotal, and row 8 is the grand total. summing column C counts every value three times over A B C 1 Region Rep Revenue 2 North Ana 1200 3 North Ben 800 4 North Total 2000 subtotal 5 South Cara 950 6 South Dev 650 7 South Total 1600 subtotal 8 Grand Total 3600 grand total naive sum = 10,800; the real total is 3,600

Prerequisites

Bash
pip install pandas openpyxl

The first file mirrors the diagram, with totals typed as values the way most exports write them:

Python
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "Sales"
ws.append(["Region", "Rep", "Revenue"])
for row in [("North", "Ana", 1200), ("North", "Ben", 800), ("North Total", None, 2000),
            ("South", "Cara", 950), ("South", "Dev", 650), ("South Total", None, 1600),
            ("Grand Total", None, 3600)]:
    ws.append(list(row))
wb.save("sales_subtotals.xlsx")

Step 1 — Confirm the problem with a quick sum

Before removing anything, measure it. Comparing the column sum with the value in the grand-total row tells you immediately whether rollups are present and roughly how many layers there are: a sum of three times the grand total means one layer of subtotals plus the grand total, and each extra layer of grouping — region, then country, then continent — adds another multiple.

Python
import pandas as pd

df = pd.read_excel("sales_subtotals.xlsx", sheet_name="Sales")
column_sum = df["Revenue"].sum()
grand = df.loc[df["Region"] == "Grand Total", "Revenue"].item()
print(column_sum, grand, column_sum / grand)                     # 10800 3600 3.0

A ratio that is not a whole number is informative too. It usually means some groups have subtotals and others do not, or that a filter was applied to the detail after the totals were calculated — both reasons to look at the file before trusting any automatic cleanup.

Step 2 — Remove rows whose label says "total"

Most exports label their rollups, so the simplest filter matches the word in the label column. Use a word boundary so that a customer called Totally Tiles Ltd survives, and allow Subtotal and Sub-total as well as Total.

Python
TOTAL_LABEL = r"\b(?:sub-?)?total\b"

is_total = df["Region"].astype(str).str.contains(TOTAL_LABEL, case=False, regex=True)
detail = df[~is_total].reset_index(drop=True)
print(detail)
print(detail["Revenue"].sum())          # 3600

astype(str) matters more than it looks. A label column that is otherwise empty on detail rows — common when a report prints the region once per group — holds NaN, and .str.contains on a float NaN returns NaN rather than False, which breaks the boolean mask.

Three signals that identify a rollup row A label containing total, a row where the detail columns such as Rep are empty, and a SUBTOTAL formula in the value column each identify rollup rows, with different strengths. label says total North Total, Grand Total simplest filter misses unlabelled rows detail fields empty Rep is blank works without labels needs a mandatory field SUBTOTAL formula =SUBTOTAL(9, C2:C3) exact, never guesses needs openpyxl combine two signals when the export is inconsistent

Step 3 — Detect rollups by their empty detail fields

Some tools do not label totals at all; they print a bold number with nothing next to it. A rollup still has a tell: it has no value in the fields that identify a single record. Every real sale has a rep, so a row with a revenue figure and no rep is not a sale.

Python
is_rollup = df["Rep"].isna() & df["Revenue"].notna()
print(df[is_rollup])
detail_by_shape = df[~is_rollup].reset_index(drop=True)
print(detail_by_shape.equals(detail))   # True for this file

Pick the identifying column carefully. It must be mandatory for every genuine detail row — an order ID, an invoice number, an employee code. A column that is usually filled, such as a comment or a secondary contact, will silently drop real records the day one of them is blank. When no single column is reliable, require several to be empty at once.

Step 4 — Find SUBTOTAL formulas with openpyxl

Excel's Data → Subtotal command and most hand-built reports write the rollups as SUBTOTAL formulas. openpyxl can read the formula text, which identifies rollup rows exactly, without any assumption about labels or empty cells. This file writes the totals as formulas and leaves the group label on the first detail row only, as many reports do:

Python
from openpyxl import Workbook, load_workbook

wb = Workbook()
ws = wb.active
ws.append(["Region", "Rep", "Revenue"])
ws.append(["North", "Ana", 1200])
ws.append([None, "Ben", 800])
ws.append(["North Total", None, "=SUBTOTAL(9,C2:C3)"])
ws.append(["South", "Cara", 950])
ws.append([None, "Dev", 650])
ws.append(["South Total", None, "=SUBTOTAL(9,C5:C6)"])
wb.save("formula_subtotals.xlsx")

book = load_workbook("formula_subtotals.xlsx")          # formulas, not cached values
sheet = book.active
subtotal_rows = {
    cell.row for cell in sheet["C"]
    if isinstance(cell.value, str) and cell.value.upper().startswith("=SUBTOTAL(")
}
print(sorted(subtotal_rows))    # [4, 7]

raw = pd.read_excel("formula_subtotals.xlsx")
excel_row = pd.RangeIndex(len(raw)) + 2
clean = raw[~excel_row.isin(subtotal_rows)].copy()
clean["Region"] = clean["Region"].ffill()                # label printed once per group
print(clean)

The forward fill at the end repairs the other half of this layout: the region is only written on the first row of each group, so without it half of the detail rows would have no region. A file written by openpyxl has no cached results for its formulas, which is why pandas shows NaN in those rows here; a file saved by Excel carries the computed totals.

Step 5 — Reconcile your sums against the removed totals

Deleting the totals throws away information that the export's author computed for you. Keep them in a separate frame and compare them with your own group sums. If they match, you know both that the right rows were removed and that nothing was lost in the read.

Python
totals = df[is_total].copy()
totals["Region"] = totals["Region"].str.replace(r"\s*total$", "", case=False, regex=True)
reported = totals.set_index("Region")["Revenue"]

grand_total = reported.pop("Grand")
computed = detail.groupby("Region")["Revenue"].sum()

mismatch = (computed - reported.reindex(computed.index)).abs() > 0.005
if mismatch.any() or abs(computed.sum() - grand_total) > 0.005:
    raise ValueError(f"totals do not reconcile:\n{pd.concat([computed, reported], axis=1)}")
print("reconciled:", computed.to_dict(), "grand total", grand_total)

The small tolerance absorbs floating-point rounding in currency columns. A mismatch larger than that usually means one of three things: a detail row was filtered out by mistake, the export applied a filter before it calculated its totals, or the totals are formatted as rounded numbers while the detail keeps decimals.

Removing rollups and using them as a check Split the export into detail rows and total rows, sum the detail by group, compare with the reported subtotals and grand total, and only continue when they reconcile. split detail vs totals group sums from detail rows compare with reported subtotals continue only when equal the totals you delete are the cheapest test you will ever get

Step 6 — Wrap it as a reusable cleaner

Put the rules in one function so every export passes through the same logic, and return both halves so the caller can reconcile or log them.

Python
def split_rollups(frame: pd.DataFrame, label_col: str, id_col: str, value_col: str):
    label_hit = frame[label_col].astype(str).str.contains(TOTAL_LABEL, case=False, regex=True)
    shape_hit = frame[id_col].isna() & frame[value_col].notna()
    rollup = label_hit | shape_hit
    return frame[~rollup].reset_index(drop=True), frame[rollup].reset_index(drop=True)

detail_rows, rollup_rows = split_rollups(df, "Region", "Rep", "Revenue")
print(len(detail_rows), "detail rows,", len(rollup_rows), "rollup rows")

Combining the label rule with the shape rule with | catches both labelled and unlabelled rollups. Log the number of rollup rows per file; a sudden change in that count is an early signal that the export's grouping has changed.

Common pitfalls

SymptomCauseFix
A real customer disappearsLabel regex matched inside a nameUse word boundaries: \btotal\b
Boolean mask error on .str.containsLabel column has NaNCall astype(str) first
Detail rows removed by the shape ruleThe identifying column is optionalChoose a mandatory field, or require several empty
Totals do not reconcile by a small amountRounded totals versus unrounded detailCompare with a tolerance, or round both
Subtotal rows read as NaNFormulas written by a script, never calculatedDetect by formula, or recalculate as in Recalculate Excel Formulas Without Excel in Python
Half the rows have no groupGroup label printed once per blockffill() the label column after removing rollups

Performance and scale notes

Both filters are vectorised string and null checks, so they are effectively free next to the read itself. The openpyxl formula scan costs a full workbook load; on a large file, restrict it to the one value column with sheet["C"] as shown, or use read_only=True, where cell values still expose formula strings and the scan streams through the sheet instead of building it in memory. If the same report format arrives every day, run the reconciliation every day too — it is a few milliseconds, and it is the check that notices when an upstream filter changed.

Conclusion

Rollup rows are data about the data, and they have to come out before any aggregation. Find them by their label, by the absence of a record identifier, or — most precisely — by their SUBTOTAL formulas, and use a combination when the export is inconsistent. Then keep the removed rows long enough to reconcile your own group sums against them. It turns a cleaning step into a correctness test that costs nothing.

Frequently asked questions

Why is my pandas sum a whole multiple of the real total? Because the sheet contains rollups that repeat the detail values: one layer of subtotals plus a grand total triples the sum. Removing the rollup rows brings it back to the true figure.

Can I just drop the last row to remove the grand total? Only if you are certain it is always the last row and there are no subtotals. Matching the label or the formula is safer and costs no more.

How do I keep the subtotals as a separate summary table? Select the rollup rows instead of discarding them, strip the word Total from the label, and write them to their own sheet. They make a convenient check against your own groupby.

Does pandas pivot_table produce the same kind of total rows? Only when margins=True, which adds an All row and column. Those are easy to remove by label, but it is simpler not to request margins when the data is going on to further processing.

More on messy spreadsheets

More on messy spreadsheets →