Using a Block Format
By default, a subtotal line is captioned with member names, holds a SUM, and looks like the cells around it. To design the line yourself, give the Subtotal Rule a Block Format.
The Block Format only changes what is on each line. The lines are still inserted in the same places.
Choosing a Block Format
In the rule's Block Format section, pick a Block Format from the dropdown. Leave it on Default subtotal line, or clear it, to keep the default.
The Block Format must match the rule's axis:
| Axis | Block Format |
|---|---|
| Rows | Row block |
| Columns | Column block |
The dropdown only lists Block Formats that match. If none do, it says so. Mark a Row or Column space as a Block Format first.
If the selected Block Format is deleted, or changed to the other orientation, the rule falls back to the default subtotal line and the editor shows a warning. Choose another Block Format, or clear it.
Where the block lands
The block is laid along each subtotal line starting from the beginning of the dataset, header cells included, and stops at the end of the dataset. Its first templates therefore fall on the line's header cells, and later templates on its data cells.
So, for a Rows rule on a dataset with three row header columns and one Sales column, a Row block's templates line up like this:
| Template | Lands on |
|---|---|
| 1st | Year header |
| 2nd | Region header |
| 3rd | Product header |
| 4th | Sales |
Extends on the block's last template repeats it across the rest of the dataset, as it does anywhere else a Block Format is used.
Apply content and Apply style
Once a Block Format is chosen, two checkboxes decide what it replaces. Both are checked by default.
Apply content
The block's labels and formulas replace the default caption and SUM on the line.
A blank template, one with no label and no formula, leaves the cell underneath it alone. That lets a block replace part of a line and keep the default for the rest. For example, a block whose first template is labelled "Total" and whose other templates are blank captions the line "Total" and keeps the default SUM in every data cell.
With Apply content unchecked, the default captions and SUMs stay as they are.
Apply style
The block's styling replaces the line's styling: colors, number format, borders, and so on.
With only Apply style checked, a block restyles the subtotal lines, for example making them bold with a shaded background, without changing what they say.
Formulas in a Block Format
A formula in the block is written onto every subtotal line exactly as you authored it. It isn't adjusted to point at the group each line totals. Only the repeats produced by Extends are shifted, along the line.
Because of this, a block is best for captions and styling. To keep each line totalling its own group, leave the block's data templates blank and let the default SUM stay.
An outer total still sums the lines one level inside it, even when those lines use a Block Format. If an inner line shows something other than a total, the outer total sums whatever that line shows.
Using different Block Formats for different levels
A Block Format belongs to the rule, not to the dimension. To style each level of a nested total differently, split them into separate rules:
- Rule 1: breaks on Region, with a light "Region total" block
- Rule 2: breaks on Year, with a bold "Year total" block
The two rules combine exactly as a single rule breaking on both would. See More than one rule.