excel-subtotal-audit

Excel Subtotal Audit

Use this workflow after generating or modifying an Excel report to catch silent spreadsheet defects that simple file-exists or sheet-exists checks will miss.

The core idea is:

1. Read back the finished workbook.
2. Print every populated output row, including formulas.
3. Identify section subtotal rows.
4. Independently verify that each subtotal formula includes all visible detail rows intended for that section.
5. Treat any mismatch as a report defect, even if the workbook opens successfully and looks formatted correctly.

This is especially useful for generated financial statements, operational summaries, rollups, grouped exports, and any worksheet where formulas summarize nearby detail rows.

When to use

Use this skill when:

• You generate Excel workbooks programmatically.
• The workbook contains subtotal, total, or rollup formulas.
• Sections may contain blank rows, hidden rows, filtered rows, or varying detail lengths.
• A mistake in a formula range would produce a plausible but wrong spreadsheet.
• Basic validation only checks whether the file was created, whether sheets exist, or whether formulas are present.

Goal

Do not stop at "the report was written successfully."

Instead, prove that:

• every expected output row is present,
• formulas were written into the intended cells,
• each subtotal references the correct detail range,
• no visible detail rows are omitted from the subtotal span.

Workflow

1. Save the workbook, then reopen it for audit

Always audit the workbook after writing it, using a fresh read from disk if possible. This verifies the actual persisted artifact rather than in-memory assumptions.

Checklist:

• Save workbook.
• Reopen workbook from disk.
• Select the target worksheet.
• Use formula view or raw formulas, not only calculated values.

Why: a writer may have inserted the wrong range, overwritten cells, or shifted rows during formatting.

2. Enumerate every populated row in the output region