Subtotal Dialog

• Jul 14, 2021
• 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. Before subtotaling, it is necessary to ensure that the range to be subtotaled contains similar data in each column, has column headings in the first row and does not include blank rows or columns.

To provide end-users with the capability to invoke the Subtotal dialog, create the Outline group within the Ribbon UI’s Data tab.

For an example on how to provide a Ribbon UI for the SpreadsheetControl, refer to the Getting Started topic.

Insert Subtotals

To insert subtotals for the selected range, an end-user should invoke the Subtotal dialog and specify the required subtotal options.

The At each change in drop-down list allows an end-user to select the heading of the column by which the data should be grouped. Each time a value in this column changes, a new subtotal row is inserted. So sorting your data by this column is recommended to ensure that the same column values will be in one group.

In the Use function drop-down list, an end-user can select one of the eleven functions available for calculating subtotals: Sum, Count, Average, Max, Min, etc.

In the Add subtotals to box, an end-user can select the check boxes corresponding to the columns for which the SUBTOTAL function should be calculated.

The Subtotal dialog also allows end-users to specify the following additional options that influence how data groups are displayed in a worksheet.

• Replace current subtotals. Select this check box to replace the existing subtotals with the recently specified. If you wish to create more than one level of subtotals (for example, to insert subtotals for the inner groups using different summary functions), clear this check box.
• Page break between groups. Select this check box to locate 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. Select this check box to display a summary row below detail rows in each group. Otherwise, the summary row will be displayed at the top of the group.

Since subtotals are specified, an end-user can change the SUBTOTAL function directly in the cell or formula bar to ignore values in the collapsed groups. To do this, add 100 to the first numeric function’s argument, which specifies the code of the function used in calculating subtotals. In this case, the hidden values in the collapsed group will not be taken into account during calculations. For more information about the SUBTOTAL function, refer to the Excel SUBTOTAL function topic.

To programmatically specify subtotals for a cell range, use the Worksheet.Subtotal or RangeExtensions.Subtotal method. For an example, refer to How to: Insert Subtotals in a Data Range.