Totals Row
Excel tables support a native totals row — a pinned row at the bottom of the table that shows aggregate values. Clicking the totals row cell in Excel exposes a dropdown to change the aggregate function. When the user filters or sorts the table, the totals update automatically.
This is the excel-table mode alternative to summary rows in report mode.
Enabling the totals row
Set totalsRow: true on the table input:
import { createExcelSchema, createWorkbook } from "@chronicstone/typed-xlsx";
const schema = createExcelSchema<{
orderId: string;
customer: string;
amount: number;
units: number;
}>({ mode: "excel-table" })
.column("orderId", { header: "Order ID", accessor: "orderId" })
.column("customer", { header: "Customer", accessor: "customer" })
.column("amount", {
header: "Amount",
accessor: "amount",
style: { numFmt: "$#,##0.00" },
})
.column("units", { header: "Units", accessor: "units" })
.build();
const workbook = createWorkbook();
workbook.sheet("Orders").table("orders", {
schema,
rows: [
{ orderId: "ORD-001", customer: "Acme Corp", amount: 4200, units: 12 },
{ orderId: "ORD-002", customer: "Globex Inc", amount: 8750, units: 25 },
],
totalsRow: true, // Enables the totals row
});
const buffer = workbook.toBuffer();
With totalsRow: true and no per-column configuration, the totals row is visible but all cells are blank — Excel shows the row without any aggregate.
Per-column totals
Configure what each column shows in the totals row using the totalsRow option on the column definition:
import { createExcelSchema, createWorkbook } from "@chronicstone/typed-xlsx";
const schema = createExcelSchema<{
dealId: string;
account: string;
stage: string;
amount: number;
probability: number;
}>({ mode: "excel-table" })
.column("dealId", {
header: "Deal ID",
accessor: "dealId",
totalsRow: { label: "Totals" }, // Static label in the totals cell
})
.column("account", {
header: "Account",
accessor: "account",
totalsRow: { function: "count" }, // COUNT of non-empty cells
})
.column("stage", {
accessor: "stage",
// No totalsRow — cell is blank
})
.column("amount", {
header: "Amount",
accessor: "amount",
style: { numFmt: "$#,##0.00" },
totalsRow: { function: "sum" }, // SUM of all amounts
})
.column("probability", {
header: "Probability",
accessor: "probability",
style: { numFmt: "0%" },
totalsRow: { function: "average" }, // AVERAGE probability
})
.build();
const workbook = createWorkbook();
workbook.sheet("Pipeline").table("deals", {
schema,
rows: [
{ dealId: "D-001", account: "Acme", stage: "Proposal", amount: 24000, probability: 0.6 },
{ dealId: "D-002", account: "Globex", stage: "Closed Won", amount: 18500, probability: 1 },
],
totalsRow: true,
style: "TableStyleMedium6",
});
const buffer = workbook.toBuffer();
Aggregate functions
| Function | Description |
|---|---|
"sum" | Sum of all values in the column |
"average" | Average of all values |
"count" | Count of non-empty cells |
"countNums" | Count of cells containing numbers |
"min" | Minimum value |
"max" | Maximum value |
"stdDev" | Standard deviation |
"var" | Variance |
All aggregate functions use Excel's SUBTOTAL() under the hood — they respect any active autoFilter so the total updates when rows are filtered.
Static label
{ label: "string" } places a static text label in the totals cell. This does not aggregate anything — it is purely decorative. Use it to label the row on the first or last text column:
import { createExcelSchema } from "@chronicstone/typed-xlsx";
const schema = createExcelSchema<{ name: string; revenue: number }>({ mode: "excel-table" })
.column("name", {
accessor: "name",
totalsRow: { label: "TOTAL" },
})
.column("revenue", {
accessor: "revenue",
style: { numFmt: "$#,##0.00" },
totalsRow: { function: "sum" },
})
.build();
Worked example: SaaS metrics dashboard
A subscription analytics table with meaningful aggregates on every relevant column:
import { createExcelSchema, createWorkbook } from "@chronicstone/typed-xlsx";
const metricsSchema = createExcelSchema<{
workspace: string;
plan: string;
mrr: number;
seats: number;
activatedSeats: number;
churnRisk: number;
}>({ mode: "excel-table" })
.column("workspace", {
header: "Workspace",
accessor: "workspace",
totalsRow: { label: "Totals" },
})
.column("plan", {
header: "Plan",
accessor: "plan",
totalsRow: { function: "count" },
})
.column("mrr", {
header: "MRR",
accessor: "mrr",
style: { numFmt: "$#,##0.00" },
totalsRow: { function: "sum" },
})
.column("seats", {
header: "Seats",
accessor: "seats",
totalsRow: { function: "sum" },
})
.column("activatedSeats", {
header: "Activated",
accessor: "activatedSeats",
totalsRow: { function: "sum" },
})
.column("churnRisk", {
header: "Churn Risk",
accessor: "churnRisk",
style: { numFmt: "0%" },
totalsRow: { function: "average" },
})
.build();