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
   [Expand]Visual Elements
    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]ASP.NET Core 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]CodeRush Classic
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation

Data Grouping

The SpreadsheetControl provides you with the capability to split a large amount of data into separate groups and display summary rows and columns for each group. Data grouping is useful when you wish to temporally hide unnecessary rows or columns to display only significant information.

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

Expanded Group and Ungroup Data

To enable end-users to group data in a worksheet, add the Outline Ribbon page group to the SpreadsheetControl. For an example on how to provide a Ribbon UI for the SpreadsheetControl, see the Getting Started topic.

The table below lists the basic grouping operations available to end-users in the user interface, and the corresponding API methods and properties.

Action

End-User Capabilities

API methods/properties

Group rows and columns

  • Select the rows or columns you wish to group.

  • On the Data tab, in the Outline group, click the Group button, and select the Group item in the button's 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 the rows or columns you wish to remove from the group. To remove the entire group, select all its rows or columns.

  • On the Data tab, in the Outline group, click the Ungroup button, and select the Ungroup item in the button's 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 the summary rows or columns containing formulas that reference cells to be outlined.

  • On the Data tab, in the Outline group, click the Group button, and select the Auto Outline item in the button's drop-down menu.


Worksheet.AutoOutline

ColumnCollection.AutoOutline

RowCollection.AutoOutline

How to: Outline Data Automatically

Remove an outline

On the Data tab, in the Outline group, click the Ungroup button, and select the Clear Outline item in the button's drop-down menu.


Worksheet.ClearOutline

ColumnCollection.ClearOutline

RowCollection.ClearOutline

RangeExtensions.ClearOutline

Outline data and calculate subtotals for the related rows

  • Select the range you wish to subtotal. Make sure that this range contains column labels and it is sorted by a column that will be used for grouping.

  • On the Data tab, in the Outline group, click the Subtotal button.


  • In the invoked Subtotal dialog, configure the subtotals and options needed. For more information, refer to Subtotal Dialog.

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 the Group

  • Using the outline symbols

    Since the group is created, it is marked by the grouping bar displayed along the grouped rows to the left side of a worksheet and along the grouped columns at the top of a worksheet. Each grouping bar is accompanied by a plus or minus outline symbol (depending on the group state). To collapse the required group, click the minus sign, which automatically changes into a plus sign after the group is collapsed, or just click the grouping bar itself. To expand the collapsed group, click the plus sign.

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

  • Using the outline buttons

    If your data is divided into different grouping levels , you can collapse or expand multiple groups at once using the outline buttons . The outline buttons for row grouping are displayed above the row headers, while the outline buttons for column grouping are shown to the left of the column headers. Each outline button has a number that indicates a grouping level. For example, in the image at the beginning of this article there are two levels of row grouping. Clicking the outline button 1 collapses all the inner groups and displays only the grand total for all detail rows. Clicking the number 2 collapses all the groups except the first level and displays the subtotal rows for each inner group in the second level. Clicking the last outline button 3 displays the entire worksheet by expanding all the specified groups.

    Thus, to hide all detail data, click the outline button 1. To display all detail data, click the outline button with the largest number.

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

Expanded Group Settings

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

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

In this case, the grouping bar changes direction, so that the minus outline symbol is displayed opposite the summary row at the top of the group.

Summary rows below detail

Summary rows above detail

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

Expanded End-User Restrictions

The SpreadsheetControl allows you to prevent end-users from creating and modifying the outlines of rows and columns in a worksheet. To implement end-user restrictions, use the SpreadsheetBehaviorOptions.Group property, which provides access to the restriction settings. The table below lists possible restrictions you can impose on working with groups of rows and columns in the SpreadsheetControl.

Restriction

Description

SpreadsheetGroupBehaviorOptions.Group Gets or sets whether end-users are allowed to group data in a worksheet.
SpreadsheetGroupBehaviorOptions.Ungroup Gets or sets whether or not end-users are allowed to ungroup data in a worksheet.
SpreadsheetGroupBehaviorOptions.Expand Gets or sets whether or not end-users are allowed to expand groups.
SpreadsheetGroupBehaviorOptions.Collapse Gets or sets whether or not end-users are allowed to collapse groups.
SpreadsheetGroupBehaviorOptions.ChangeSettings Gets or sets whether or not end-users are allowed to change group settings.

Set the desired property to the DocumentCapability.Disabled or DocumentCapability.Hidden value, to disable or hide the corresponding command in the Ribbon UI and deactivate the outline buttons and symbols.

Expanded See Also

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