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 Mapping Fields, see the fields available then select a Field to add to the Group box, .
Drag the Customer State field to the Group box or use the >I arrow tome it across.
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 be set to display on Top, at the Bottom or None to not display a Grand Total.
When in Edit Mode select the Settings > Properties Tab then choose the Grand total Position. Do the same to control where the SubTotals are positioned.
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), where you can easily click on a subtotal (State) to expand the report out to show all customers under that subtotal, 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.
Double click to 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 be set to either display or not display, you can't change the location of the Grand Total like you can in a Report Styl ( see above). When in Edit Mode. select the Settings > Properties Tab then choose to Show or Hide Row Grand Totals