Subtotal Dialog
- 3 minutes to read
The Subtotal dialog allows end-users to automatically group related data in a worksheet and add summary rows to each group using the SUBTOTAL function.
Add the Outline ribbon group to enable the Subtotal button, which invokes the target dialog. Refer to the Create a Simple Spreadsheet Application topic for details on how to provide a Ribbon UI for the SpreadsheetControl.
Insert Subtotals
Before subtotaling, make sure that the target range meets the following requirements:
- It contains similar data in each column;
- It has column headings in the first row;
- It does not include blank rows or columns.
The Subtotal dialog allows end-users to set the following subtotal options:
| Option | Description |
|---|---|
| At each change in | Specifies a column heading by which the data should be grouped. Each time a value in this column changes, a new subtotal row is inserted. Sort your data by the target column to ensure that the same column values will be in one group. |
| Use function | Sets the function to calculate subtotals. Eleven functions are available: Sum, Count, Average, Max, Min, Product, Product Numbers, StdDev, StdDevp, Var or Varp. |
| Add subtotals to | Specifies column(s) for which the SUBTOTAL function should be calculated. |
| Replace current subtotals | Sets whether existing subtotals should be replaced with the recently specified ones. Clear this check box if you wish to create more than one level of subtotals (for example, to insert subtotals for the inner groups using different summary functions). |
| Page break between groups | Locates each group on a new page. It can be useful if your groups contain many detail rows and you wish to separate them while printing. |
| Summary below data | Sets whether a summary row should be displayed below detail rows in each group. Without this option specified, the summary row will be displayed at the top of the group. |
As a result, the SUBTOTAL function appears as in the image below:
The number (1-11 or 101-111) specifies a function to use for the subtotal. 1-11 includes hidden rows, while 101-111 excludes them. End-users can change this argument in the cell or formula bar. The table below lists functions and corresponding numbers.
| Function | Number (includes hidden values) | Number (ignores hidden values) |
|---|---|---|
| AVERAGE | 1 | 101 |
| COUNT | 2 | 102 |
| COUNTA | 3 | 103 |
| MAX | 4 | 104 |
| MIN | 5 | 105 |
| PRODUCT | 6 | 106 |
| STDEV | 7 | 107 |
| STDEVP | 8 | 108 |
| SUM | 9 | 109 |
| VAR | 10 | 110 |
| VARP | 11 | 111 |
Tip
To programmatically specify subtotals for a cell range, use the Worksheet.Subtotal or RangeExtensions.Subtotal method. Refer to the How to: Insert Subtotals in a Data Range topic for details.