# Group Pivot Table Fields

## Content

DsExcel .NET allows you to group pivot table fields to summarize data more effectively. You can group date fields by date or time units, group numeric fields into ranges, and group selected items into custom categories.
Use the [Group](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.IPivotField.Group.html) method of the [IPivotField](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.IPivotField.html) interface to group a pivot table field. DsExcel also supports renaming grouped items and ungrouping when it is no longer needed. Grouping information is preserved after pivot table refresh, update, layout changes, and JSON, SJS or XLSX import and export operations.

>type=warning
> **Important**:
>
> * If a pivot cache contains calculated items, grouping is not supported for fields in that cache.
> * If the source data type changes after refresh, the existing grouping may no longer be retained. For example, if a pure date field becomes a mixed-data field, date grouping is removed because the field no longer satisfies the grouping requirement.
> * When multiple pivot tables share the same [IPivotCache](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.IPivotCache.html), grouping changes remain consistent across all pivot tables that depend on that cache.

## Group Date Fields

Use date grouping to organize a date field by one or more time units such as seconds, minutes, hours, days, months, quarters, or years. You can also define custom ranges, such as a 7‑day interval (for a weekly view) or any other span that fits your scenario. After the grouping is applied, DsExcel .NET creates grouped fields for the selected time units. For example, grouping the `Order Date` field by years and quarters adds grouped fields such as `Years (Order Date)` and `Quarters (Order Date)`.
You can control the grouped range by using the [Start](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.PivotFieldDateGroupOptions.Start.html), [End](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.PivotFieldDateGroupOptions.End.html), [AutoStart](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.PivotFieldDateGroupOptions.AutoStart.html), and [AutoEnd](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.PivotFieldDateGroupOptions.AutoEnd.html) properties of [PivotFieldDateGroupOptions](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.PivotFieldDateGroupOptions.html). If day grouping is used, the `Days` property specifies the number of days in each group.
Refer to the following example code to group a date field by months and years.

```csharp
// Create a new workbook.
var workbook = new GrapeCity.Documents.Excel.Workbook();
workbook.Options.Data.AutomaticGroupDateTimeInPivotTable = false;

// Set data.
object[,] sourceData = new object[,]
{
    { "Order ID", "Product", "Order Date", "Sales" },
    { 1001, "Apple", new DateTime(2024, 1, 5), 120d },
    { 1002, "Banana", new DateTime(2024, 1, 12), 95d },
    { 1003, "Carrot", new DateTime(2024, 2, 3), 150d },
    { 1004, "Dates", new DateTime(2024, 2, 18), 90d },
    { 1005, "Eggplant", new DateTime(2024, 3, 6), 180d },
    { 1006, "Fig", new DateTime(2024, 3, 21), 160d },
    { 1007, "Grapes", new DateTime(2025, 1, 9), 140d },
    { 1008, "Honey", new DateTime(2025, 2, 15), 210d },
    { 1009, "Ice Tea", new DateTime(2025, 3, 11), 80d },
};
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["G1:J10"].Value = sourceData;
worksheet.Range["I2:I10"].NumberFormat = "m/d/yyyy";
worksheet.Range["J2:J10"].NumberFormat = "$#,##0.00";

// Add a pivot table.
IPivotCache pivotcache = workbook.PivotCaches.Create(worksheet.Range["G1:J10"]);
IPivotTable pivottable = worksheet.PivotTables.Add(pivotcache, worksheet.Range["A1"], "dateGroupPivot");
IPivotField fieldOrderDate = pivottable.PivotFields["Order Date"];
fieldOrderDate.Orientation = PivotFieldOrientation.RowField;
IPivotField fieldSales = pivottable.PivotFields["Sales"];
fieldSales.Orientation = PivotFieldOrientation.DataField;
fieldSales.NumberFormat = "$#,##0.00";

// Group the "Order Date" field by Years and Months.
fieldOrderDate.Group(new PivotFieldDateGroupOptions
{
    GroupBy = PivotFieldDateGroupBy.Months | PivotFieldDateGroupBy.Years,
});
worksheet.Range["A:J"].EntireColumn.AutoFit();

// Save to an excel file.
workbook.Save("DateGroup.xlsx");
```

The output is shown in the figure below:
![Document Solutions for Excel displays pivot table dates grouped by months and years, demonstrating PivotFieldDateGroupOptions for developers summarizing time-based records.](https://cdn.mescius.io/document-site-files/images/313b64e2-4e9c-4791-9cf1-0f2eca7d8ca4/image-20260609.860c52.png)
Use day grouping when you want to summarize dates in fixed-length intervals, such as every 7 days for weekly-style analysis.
Refer to the following example code to group a date field into 7-day intervals.

```csharp
// Create a new workbook.
var workbook = new GrapeCity.Documents.Excel.Workbook();
workbook.Options.Data.AutomaticGroupDateTimeInPivotTable = false;

// Set data.
object[,] sourceData = new object[,]
{
    { "Order ID", "Product", "Order Date", "Sales" },
    { 1001, "Apple", new DateTime(2024, 1, 2), 120d },
    { 1002, "Banana", new DateTime(2024, 1, 5), 95d },
    { 1003, "Carrot", new DateTime(2024, 1, 9), 150d },
    { 1004, "Dates", new DateTime(2024, 1, 12), 90d },
    { 1005, "Eggplant", new DateTime(2024, 1, 16), 180d },
    { 1006, "Fig", new DateTime(2024, 1, 19), 160d },
    { 1007, "Grapes", new DateTime(2024, 1, 24), 140d },
    { 1008, "Honey", new DateTime(2024, 1, 27), 210d },
    { 1009, "Ice Tea", new DateTime(2024, 1, 30), 80d },
};

IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["G1:J10"].Value = sourceData;
worksheet.Range["I2:I10"].NumberFormat = "m/d/yyyy";
worksheet.Range["J2:J10"].NumberFormat = "$#,##0.00";

// Add a pivot table.
IPivotCache pivotcache = workbook.PivotCaches.Create(worksheet.Range["G1:J10"]);
IPivotTable pivottable = worksheet.PivotTables.Add(pivotcache, worksheet.Range["A1"], "dayGroupPivot");
IPivotField fieldOrderDate = pivottable.PivotFields["Order Date"];
fieldOrderDate.Orientation = PivotFieldOrientation.RowField;
IPivotField fieldSales = pivottable.PivotFields["Sales"];
fieldSales.Orientation = PivotFieldOrientation.DataField;
fieldSales.NumberFormat = "$#,##0.00";

// Group the "Order Date" field into 7-day intervals.
fieldOrderDate.Group(new PivotFieldDateGroupOptions
{
    GroupBy = PivotFieldDateGroupBy.Days,
    Days = 7,
    Start = new DateTime(2024, 1, 1),
    End = new DateTime(2024, 1, 31),
    AutoStart = false,
    AutoEnd = false,
});

worksheet.Range["A:J"].EntireColumn.AutoFit();

// Save to an excel file.
workbook.Save("DayGroup.xlsx");
```

The output is shown in the figure below:
![Document Solutions for Excel displays pivot table dates grouped into day intervals, demonstrating PivotFieldDateGroupOptions for developers creating fixed-length date summaries.](https://cdn.mescius.io/document-site-files/images/313b64e2-4e9c-4791-9cf1-0f2eca7d8ca4/image-20260610.facf02.png)
Date grouping creates generated fields for the selected date or time units. By default, if the base field is placed in the Row or Column area, DsExcel .NET automatically adds the generated fields to the same layout area.
You can use the [AddGeneratedFieldsToLayout](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.PivotFieldDateGroupOptions.AddGeneratedFieldsToLayout.html) property of [PivotFieldDateGroupOptions](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.PivotFieldDateGroupOptions.html) to control this behavior. The default value is true. If the base field is outside the row or column area, this property has no effect and the generated fields are not added to the layout.
Set this property to false when you want to prevent the generated field from being inserted into the pivot table layout automatically after grouping.

>type=note
> **Note**: This option is useful when multiple pivot tables share the same [IPivotCache](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.IPivotCache.html). It prevents the layout of other pivot tables from being modified when a field is grouped in the current pivot table.

```csharp
// Group the "Order Date" field by years and months, but do not add the generated fields to the layout automatically.
fieldOrderDate.Group(new PivotFieldDateGroupOptions
{
    GroupBy = PivotFieldDateGroupBy.Years | PivotFieldDateGroupBy.Months,
    AddGeneratedFieldsToLayout = false,
});
```

### Automatic Grouping

DsExcel .NET also supports automatic date grouping through the [AutomaticGroupDateTimeInPivotTable](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.IDataOptions.AutomaticGroupDateTimeInPivotTable.html) property, which is true by default. When this property is set to true, date grouping is created automatically to match Excel-compatible behavior when a date field is placed in the Row or Column area during pivot table creation or refresh.

>type=warning
> **Important**:
>
> * Date grouping can only be applied to pure date fields. If the field contains mixed or non-date values, calling the [Group](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.IPivotField.Group.html) method throws an exception.
> * If a date field is grouped multiple times, the previous grouping is not retained, and only the last grouping takes effect. For example, if you first group by Year and Month, and then group again by Year and Quarter, only the Year and Quarter fields will be kept in the end.

## Group Numeric Fields

Use numeric grouping to group a numeric field into ranges based on a start value, end value, and interval.
Numeric grouping groups values into buckets on the base field and **does not** **create generated grouped fields**. Bucket labels are derived from the base field and the configured range and interval.
You can use the [Start](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.PivotFieldNumberGroupOptions.Start.html), [End](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.PivotFieldNumberGroupOptions.End.html), [AutoStart](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.PivotFieldNumberGroupOptions.AutoStart.html), and [AutoEnd](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.PivotFieldNumberGroupOptions.AutoEnd.html) properties of [PivotFieldNumberGroupOptions](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.PivotFieldNumberGroupOptions.html) to control the grouping range.
Refer to the following example code to group a numeric field into ranges.

```csharp
// Create a new workbook.
var workbook = new GrapeCity.Documents.Excel.Workbook();

// Set data.
object[,] sourceData = new object[,]
{
    { "Order ID", "Product", "Shipping Fee", "Sales" },
    { 1001, "Apple", 18d, 120d },
    { 1002, "Banana", 42d, 95d },
    { 1003, "Carrot", 68d, 150d },
    { 1004, "Dates", 85d, 90d },
    { 1005, "Eggplant", 110d, 180d },
    { 1006, "Fig", 135d, 160d },
    { 1007, "Grapes", 170d, 140d },
    { 1008, "Honey", 205d, 210d },
    { 1009, "Ice Tea", 28d, 80d },
};

IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["G1:J10"].Value = sourceData;
worksheet.Range["I2:J10"].NumberFormat = "$#,##0.00";

// Add a pivot table.
IPivotCache pivotcache = workbook.PivotCaches.Create(worksheet.Range["G1:J10"]);
IPivotTable pivottable = worksheet.PivotTables.Add(pivotcache, worksheet.Range["A1"], "numberGroupPivot");
IPivotField fieldShippingFee = pivottable.PivotFields["Shipping Fee"];
fieldShippingFee.Orientation = PivotFieldOrientation.RowField;
IPivotField fieldSales = pivottable.PivotFields["Sales"];
fieldSales.Orientation = PivotFieldOrientation.DataField;
fieldSales.NumberFormat = "$#,##0.00";

// Group the "Shipping Fee" field by numeric intervals.
// Define grouping range from 0 to 250 with an interval of 50.
fieldShippingFee.Group(new PivotFieldNumberGroupOptions
{
    Start = 0d,
    End = 250d,
    AutoStart = false,
    AutoEnd = false,
    Interval = 50d,
});

worksheet.Range["A:J"].EntireColumn.AutoFit();

// Save to an excel file.
workbook.Save("NumberGroup.xlsx");
```

The output is shown in the figure below:
![Document Solutions for Excel displays numeric pivot field values grouped into ranges, demonstrating PivotFieldNumberGroupOptions for developers defining numeric summary bands.](https://cdn.mescius.io/document-site-files/images/313b64e2-4e9c-4791-9cf1-0f2eca7d8ca4/image-20260609.ccd738.png)

>type=warning
> **Important**:
>
> * Numeric grouping can only be applied to pure numeric fields. If the field contains non-numeric values, grouping throws an exception.
> * If a numeric field is grouped more than once, the previous range definition is replaced.
> * To maintain compatibility with Microsoft Excel, applying custom grouping to a field that is already grouped into numeric ranges has no effect.

## Group Selected Items

You can group selected pivotItems to create a custom group. Custom grouping creates a generated grouped field. You can use the [Name](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.PivotFieldCustomGroupOptions.Name.html) property of [PivotFieldCustomGroupOptions](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.PivotFieldCustomGroupOptions.html) to specify a custom group name when the group is created. Custom grouping can be applied to all types of fields, including pure date and numeric fields.
If a new custom grouping overlaps with an existing grouped selection, the new grouping overrides the previous one for the overlapping items. If the selections do not overlap, the new grouped item is added to the generated grouped field.
Custom grouping also supports nested grouping, which means you can create a new grouping based on an existing grouped field.
Similar to date grouping, when the base field is placed in the row or column area, automatic layout insertion for generated fields is controlled by the [AddGeneratedFieldsToLayout](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.PivotFieldCustomGroupOptions.AddGeneratedFieldsToLayout.html) property.
Refer to the following example code to group selected items in a pivot table field.

```csharp
// Create a new workbook.
var workbook = new GrapeCity.Documents.Excel.Workbook();

// Set data.
object[,] sourceData = new object[,]
{
    { "Order ID", "Product", "Region", "Sales" },
    { 1001, "Apple", "East", 120d },
    { 1002, "Banana", "North", 95d },
    { 1003, "Carrot", "South", 150d },
    { 1004, "Dates", "West", 90d },
    { 1005, "Eggplant", "East", 180d },
    { 1006, "Fig", "North", 160d },
    { 1007, "Grapes", "Central", 140d },
    { 1008, "Honey", "South", 210d },
    { 1009, "Ice Tea", "West", 80d },
};

IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["G1:J10"].Value = sourceData;
worksheet.Range["J2:J10"].NumberFormat = "$#,##0.00";

// Add a pivot table.
IPivotCache pivotcache = workbook.PivotCaches.Create(worksheet.Range["G1:J10"]);
IPivotTable pivottable = worksheet.PivotTables.Add(pivotcache, worksheet.Range["A1"], "customGroupPivot");
IPivotField fieldRegion = pivottable.PivotFields["Region"];
fieldRegion.Orientation = PivotFieldOrientation.RowField;
IPivotField fieldSales = pivottable.PivotFields["Sales"];
fieldSales.Orientation = PivotFieldOrientation.DataField;
fieldSales.NumberFormat = "$#,##0.00";

// Create a custom group in the "Region" field.
// Group "East" and "North" into a new group.
fieldRegion.Group(new PivotFieldCustomGroupOptions
{
    Name = "Core Regions",
    Items = new List<string> { "East", "North" },
});

// Rename the created group caption in the pivot table.
fieldRegion.PivotItems["Core Regions"].Caption = "Primary Regions";
worksheet.Range["A:J"].EntireColumn.AutoFit();

// Save to an excel file.
workbook.Save("CustomGroup.xlsx");
```

The output is shown in the figure below:
![Document Solutions for Excel displays selected pivot items combined into custom groups, demonstrating PivotFieldCustomGroupOptions for developers organizing related categories.](https://cdn.mescius.io/document-site-files/images/313b64e2-4e9c-4791-9cf1-0f2eca7d8ca4/image-20260609.0b8047.png)

## Rename Grouped Items

Use the [Caption](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.IPivotItem.Caption.html) property of the [IPivotItem](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.IPivotItem.html) interface to rename a grouped item.
Refer to the following example code to rename a grouped item created by custom grouping.

```csharp
IPivotItem item = pivotTable.PivotFields["Product2"].PivotItems["Group1"];
item.Caption = "Fresh Fruit";
```

## Ungroup

Use the [UnGroup()](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.IPivotField.Ungroup.html) method of the [IPivotField](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.IPivotField.html) interface to ungroup a pivot table field. You can use the method on the base field or on a generated grouped field. DsExcel clears all related group fields and restores the original source layout.
If multiple pivot tables share the same [IPivotCache](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.IPivotCache.html), ungrouping changes are reflected in all dependent pivot tables.
Refer to the following example code to remove grouping from a pivot table field.

```csharp
orderDateField.UnGroup();
```