[Expand]General Information
[Expand]WinForms Controls
[Expand]ASP.NET Controls and MVC Extensions
[Expand]ASP.NET Bootstrap Controls
[Expand]ASP.NET Core Bootstrap Controls
[Collapse]WPF Controls
  Prerequisites
 [Expand]What's Installed
 [Expand]Common Concepts
 [Expand]MVVM Framework
 [Collapse]Controls and Libraries
   Reporting
  [Expand]Data Grid
  [Expand]Ribbon, Bars and Menu
  [Expand]Charts Suite
  [Expand]Pivot Grid
  [Expand]Scheduler
  [Collapse]Spreadsheet
    Product Structure
   [Expand]Getting Started
   [Expand]Spreadsheet Document
    Supported Formats
   [Expand]Cell Basics
   [Expand]Formulas
    Defined Names
    Data Binding
    Data Validation
   [Expand]Data Presentation
   [Expand]Pivot Table Overview
   [Expand]Charting Overview
    Shapes
    Printing
    Protection
    Find and Replace
   [Expand]Mail Merge
   [Collapse]Visual Elements
    [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
      Change PivotTable Data Source Dialog
      PivotTable Field List
      PivotTable Value Field Settings Dialog
      PivotTable Field Settings Dialog
      PivotTable Sort Dialog
      Insert Calculated Field Dialog
      Insert Calculated Item Dialog
      PivotTable Solve Order Dialog
      PivotTable Options Dialog
      New Formatting Rule Dialog
      Conditional Formatting Rules Manager
     Pop-up Menus
    Services
    Themes and Templates
   [Expand]Shortcuts
   [Expand]Examples
  [Expand]Rich Text Editor
  [Expand]Tree List
  [Expand]Gauge Controls
  [Expand]Map Control
  [Expand]Layout Management
  [Expand]Windows Modern UI
  [Expand]Printing-Exporting
  [Expand]Data Editors
  [Expand]Navigation Controls
  [Expand]Spell Checker
  [Expand]Property Grid
  [Expand]PDF Viewer
  [Expand]TreeMap Control
  [Expand]Gantt Control
  [Expand]Diagram Control
  [Expand]Windows and Utility Controls
   Dialogs, Notifications and Panels
  [Expand]Scheduler (legacy)
 [Expand]Scaffolding Wizard
 [Expand]Localization
  Redistribution and Deployment
  Get More Help
 [Expand]API Reference
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Expand]Office File API
[Expand]Reporting
[Expand]Report and Dashboard Server
[Expand]Dashboard
[Expand]eXpressApp Framework
[Expand]eXpress Persistent Objects
[Expand]CodeRush
[Expand]CodeRush Classic
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation
View this topic on docs.devexpress.com (Learn more)

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.

Add the Outline ribbon group to enable the Subtotal button, which invokes the target dialog. Refer to the Lesson 1 - Create a Simple Spreadsheet topic for details on how to provide a Ribbon UI for the SpreadsheetControl.

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

Expanded See Also

Is this topic helpful?​​​​​​​