Log In
Home
Support
Demos
Documentation
Blogs
Training
Webinars
[Expand]General Information
[Collapse]WinForms Controls
  Prerequisites
 [Expand]What's Installed
 [Expand]Build an Application
 [Collapse]Controls and Libraries
  [Expand]Forms and User Controls
  [Expand]Messages, Notifications and Dialogs
  [Expand]Editors and Simple Controls
  [Expand]Ribbon, Bars and Menu
  [Expand]Application UI Manager
  [Expand]Docking Library
  [Expand]Data Grid
  [Expand]Vertical Grid
  [Expand]Pivot Grid
  [Expand]Tree List
  [Expand]Chart Control
  [Expand]Diagrams
  [Expand]Gauges
  [Expand]Map Control
  [Expand]Scheduler
  [Collapse]Spreadsheet
   [Expand]Product Information
    Product Structure
    Getting Started
   [Expand]Spreadsheet Document
    Supported Formats
   [Expand]Cell Basics
   [Expand]Spreadsheet Formulas
    Defined Names
    Data Binding
    Data Validation
   [Expand]Data Presentation
    Data Grouping
   [Expand]Pivot Table Overview
   [Expand]Charting Overview
    Printing
    Events
   [Expand]Mail Merge Overview
    Protection
    Find and Replace
   [Collapse]Visual Elements
     Workbook
     Worksheet
    [Expand]Cells and Cell Ranges
     Rows and Columns
     Formulas
     Name Box
     Pictures
    [Collapse]Dialogs
      Format Cells Dialog
      Paste Special Dialog
      Edit Hyperlink Dialog
      Symbol Dialog
      Create Table Dialog
      Change Chart Type Dialog
      Find and Replace Dialog
      Encrypt Document Dialog
      Password Dialog
      Protect Workbook Dialog
      Protect Sheet Dialog
      User Permissions Dialog
      Insert Function Dialog
      Function Arguments Dialog
      Name Manager
      New Name Dialog
      Create Names from Selection Dialog
      Subtotal Dialog
      Data Validation Dialog
      Properties Dialog
      Page Setup Dialog
      Header and Footer Dialog
      Create PivotTable Dialog
      Move PivotTable Dialog
      PivotTable Field List
      PivotTable Field Settings Dialog
      PivotTable Value Field Settings Dialog
      PivotTable Sort Dialog
      Insert Calculated Field Dialog
      Insert Calculated Item Dialog
      PivotTable Options Dialog
      PivotTable Solve Order Dialog
      New Formatting Rule Dialog
      Conditional Formatting Rules Manager
    Services
   [Expand]Shortcuts
   [Expand]Examples
  [Expand]Rich Text Editor
  [Expand]Spell Checker
  [Expand]Form Layout Managers
  [Expand]Navigation Controls
  [Expand]Printing-Exporting
  [Expand]PDF Viewer
   Reporting
  [Expand]Snap
  [Expand]TreeMap Control
 [Expand]Common Features
  Get More Help
 [Expand]API Reference
[Expand]ASP.NET Controls and MVC Extensions
[Expand]ASP.NET Bootstrap Controls
[Expand]WPF Controls
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Expand]Document Server
[Expand]Reporting
[Expand]Report Server
[Expand]Dashboard
[Expand]eXpressApp Framework
[Expand]CodeRush
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation

Subtotal Dialog

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.

Expanded 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.

How would you rate this topic?​​​​​​​