[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]Property 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]Formulas
    Defined Names
    Data Binding
    Data Validation
   [Expand]Data Presentation
    Data Grouping
   [Expand]Pivot Tables
   [Expand]Charts and Graphics
    Printing
    Events
   [Expand]Mail Merge
    Protection
    Find and Replace
   [Expand]Visual Elements
    Services
   [Expand]Keyboard 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]Sunburst Control
 [Expand]Common Features
  Get More Help
 [Expand]API Reference
[Expand]ASP.NET Controls and MVC Extensions
[Expand]ASP.NET Bootstrap Controls
[Expand]ASP.NET Core Bootstrap Controls
[Expand]WPF Controls
[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)

Data Grouping

The SpreadsheetControl enables you to split a large amount of data into groups and display summary rows and columns for each group.

End users can create an outline of rows and columns, expand or collapse groups, and change group settings.

Expanded Group and Ungroup Data

To enable end users to group data in a worksheet, add the Outline ribbon group to the SpreadsheetControl. Refer to the Getting Started topic for details on how to provide a ribbon UI for the SpreadsheetControl.

The table below lists the basic group operations in the UI, and the corresponding API methods and properties.

Action

End-User Capabilities

API methods/properties

Group rows and columns

  • Select rows or columns you want to group.
  • Click the Group button on the ribbon, and select Group in the drop-down menu.

    You can also use a key combination to group data quickly. Press SHIFT+ALT+RIGHT ARROW KEY to create an outline of the selected rows or columns.

ColumnCollection.Group

RowCollection.Group

How to: Outline Data Manually

Ungroup rows and columns

  • Select rows or columns you want to remove from the group. To remove the entire group, select all its rows or columns.
  • Click the Ungroup button on the ribbon, and select Ungroup in the drop-down menu.

    You can also use a key combination to ungroup data quickly. Press SHIFT+ALT+LEFT ARROW KEY to clear an outline for the selected rows or columns.

ColumnCollection.UnGroup

RowCollection.UnGroup

How to: Outline Data Manually

Outline data automatically

  • Specify summary rows/columns containing formulas that reference cells in detail rows/columns.
  • Click the Group button on the ribbon, and select Auto Outline in the drop-down menu.

Worksheet.AutoOutline

ColumnCollection.AutoOutline

RowCollection.AutoOutline

How to: Outline Data Automatically

Remove an outline

Click the Ungroup button on the ribbon, and select Clear Outline in the button's drop-down menu.

Worksheet.ClearOutline

ColumnCollection.ClearOutline

RowCollection.ClearOutline

RangeExtensions.ClearOutline

Outline data and calculate subtotals for related rows

  • Select the cell range you want to subtotal. Make sure it has column labels in the first row, and its data is sorted to show the same values in one group.
  • Click the Subtotal button on the ribbon.

  • In the invoked Subtotal dialog, configure subtotal options.

Worksheet.Subtotal

RangeExtensions.Subtotal

How to: Insert Subtotals in a Data Range

Clear an outline and remove the subtotals

Invoke the Subtotal dialog and click the Remove All button.

Worksheet.RemoveSubtotal

RangeExtensions.RemoveSubtotal

Expanded Collapse or Expand Groups

  • Use the outline symbols

    When you group data in a worksheet, the SpreadsheetControl draws outline bars next to column and row groups. Each outline bar has a plus or minus symbol that indicates the group state. To collapse a group, click the minus sign or the outline bar itself. To expand the group, click the plus sign.

    You can also use the Hide Detail and Show Detail buttons in the Outline group to hide or display a group's detail rows and columns.

  • Use the outline buttons

    If you divide your data into different group levels, you can use the outline buttons to collapse or expand multiple groups at once. Each outline button has a number that indicates the group level. Click the outline button 1 to collapse all groups. Click the outline button with the largest number to display all detail data.

    For instance, there are two levels of row groups in the image at the beginning of this article.

    • Click outline button 1 to collapse all inner groups and display only the grand total row.

    • Click number 2 to collapse all the groups except the first level and display subtotal rows for each inner group.

    • Click outline button 3 to expand all the groups.

    The number of outline buttons changes depending on the levels of grouping in a worksheet. The largest number is 8, because you can create up to seven group levels.

  • In code

    Use the following methods to expand or collapse row/column groups programmatically:

  • Hide outline symbols

    Use the WorksheetView.ShowOutlineSymbols property to show or hide outline symbols and bars for a worksheet with grouped data.

Expanded Group Settings

You can specify a group's direction using the Settings dialog. To invoke this dialog, click the Outline dialog box launcher on the Data tab.

Select the required check boxes depending on where your summary rows or columns are located. For instance, if you insert summary rows above detail rows in each group, clear the Summary rows below detail check box.

Summary rows below detail Summary rows above detail

To specify group settings in code, use the Worksheet.OutlineOptions property.

Expanded End-User Restrictions

You can use the SpreadsheetBehaviorOptions.Group property to specify restrictions on operations with groups in the UI. The following options are available:

Restriction Description
SpreadsheetGroupBehaviorOptions.Group Gets or sets whether end users can group data in a worksheet.
SpreadsheetGroupBehaviorOptions.Ungroup Gets or sets whether end users can ungroup data in a worksheet.
SpreadsheetGroupBehaviorOptions.Expand Gets or sets whether end users can expand groups.
SpreadsheetGroupBehaviorOptions.Collapse Gets or sets whether end users can collapse groups.
SpreadsheetGroupBehaviorOptions.ChangeSettings Gets or sets whether end users can change group settings.
SpreadsheetGroupBehaviorOptions.CollapseExpandOnProtectedSheet Gets or sets whether end users can collapse or expand groups located on a protected worksheet.
SpreadsheetGroupBehaviorOptions.CollapseExpandOnReadOnlyControl Gets or sets whether end users can collapse or expand groups when the SpreadsheetControl's ReadOnly property is true.

You can set these properties to the DocumentCapability.Disabled or DocumentCapability.Hidden value, to disable or hide the corresponding commands in the ribbon UI and deactivate the outline buttons and symbols.

Expanded See Also

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