Adding Subtotals is a great way to organise your data so that it is more useful.
In this article you will learn how to add one or more Subtotals.
Setting up Subtotals is slightly different in different report style:
Report Style
- Simple Subtotal - Group by
- Advanced Subtotals - Using the Report Designer
- Grand Total Position in Reports - Top, Bottom or None
- Expand or Collapse Rows
Pivot Style
- Subtotals in Pivots - Using the Report Designer
- Subtotals in Pivots - Layout
- Grand Totals in Pivots - Display or Hide
Simple Subtotal - Group by
When in a Report style worksheet and in View or Edit Mode a simple Subtotal can be added using the Group by field.
Here we add a Subtotal by Customer State to a list of Customer sales using the Group by field.
The resulting report will show Subtotals by State.
Advanced Subtotals - Using the Report Designer
When in a Report style worksheet and in Edit Mode more advanced and multiple subtotals can be added and changed. Select the Edit button to open the Report Designer.
In the Group box, Select Add a Field to see Mapping Fields that are available to add to the report.
Drag the Customer State field to the Group box.
This will create the same report as above, Customer Subtotalled by State.
To add Multiple subtotals select another Field e.g Location (or Country etc)
To Subtotal by Location place that field first, then Indent the State field under it to change the Group level.
If you do not indent the State field then the Location will appear along side each State.
The resulting report will show Customers Grouped first by Location (or Country) and then by State.
Grand Total Position in Reports - Top, Bottom or None
When in a Report style worksheet Grand Totals can set to display on Top, at the Bottom or Not to or not display.
When in Edit Mode select the Settings Tab then choose the Grand total Position.
Expand or Collapse Rows
Once you have added subtotals you have the option on the Settings menu to show:
- show every Customer (rows expanded); or
- just show totals (rows collapsed), see example below
Pivot Style
Subtotals in Pivots - Using the Report Designer
When in a Pivot style worksheet subtotals can only be added and changed when in Edit Mode. Select the Edit button to open the Report Designer.
Select Add Field to see Mapping Fields that are available to add to the report.
Add the Customer State field to the Fields box.
Note: in Pivots there is no Group by box as the second box is used to define the Columns to Pivot on.
To Subtotal by State place that field first, then Indent the Customer Name field under it to change the Group level. If you do not indent the Customer Name field then the State will appear along side each customer name.
The resulting report will show Customers Grouped by State with a Subtotal.
Subtotals in Pivots - Layout Choices
Pivot Style worksheets have two Row Header Layout choices Standard or Tree.
a. Standard Row Header Layout
The Standard has a separate column for each field and adds the subtotal at the bottom. This is great when the data in the subtotals is fairly compact.
b. Tree Row Header Layout
The Tree adds a subtotal at the top and does not have a separate column for each field.
This is great when the data in the subtotals is longer and when space is at a premium.
Grand Totals in Pivots - Display or Hide
When in a Pivot style worksheet Grand Totals can set to display or not display. When in Edit Mode. select the Settings Tab then choose to Show or Hide Row Grand Totals