# Create Pivot Table

Learn how to create pivot tables using DsExcel in a spreadsheet. Learn more in DsExcel docs.

## Content

DsExcel provides functionality to create and add pivot tables in a spreadsheet using either a specified range or a table. [Create](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.IPivotCaches.Create.html) method of [IPivotCaches](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.IPivotCaches.html) interface initializes the pivot cache for the pivot table, while [Add](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.IPivotTables.Add.html) method of [IPivotTables](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.IPivotTables.html) interface uses this cache to insert the new pivot table into the spreadsheet.

## Create Pivot Table with Range-Based Data Source

Refer to the following example code to create a pivot table in a worksheet with a range:

```csharp
//Create a new workbook.
Workbook workbook = new Workbook();

object[,] sourceData = new object[,] {
{ "Order ID", "Product",               "Category",              "Amount", "Date",                    "Country" },
{ 1,          "Bose 785593-0050",      "Consumer Electronics",  4270,     new DateTime(2018, 1, 6),  "United States" },
{ 2,          "Canon EOS 1500D",       "Consumer Electronics",  8239,     new DateTime(2018, 1, 7),  "United Kingdom" },
{ 3,          "Haier 394L 4Star",      "Consumer Electronics",  617,      new DateTime(2018, 1, 8),  "United States" },
{ 4,          "IFB 6.5 Kg FullyAuto",  "Consumer Electronics",  8384,     new DateTime(2018, 1, 10), "Canada" },
{ 5,          "Mi LED 40inch",         "Consumer Electronics",  2626,     new DateTime(2018, 1, 10), "Germany" },
{ 6,          "Sennheiser HD 4.40-BT", "Consumer Electronics",  3610,     new DateTime(2018, 1, 11), "United States" },
{ 7,          "Iphone XR",             "Mobile",                9062,     new DateTime(2018, 1, 11), "Australia" },
{ 8,          "OnePlus 7Pro",          "Mobile",                6906,     new DateTime(2018, 1, 16), "New Zealand" },
{ 9,          "Redmi 7",               "Mobile",                2417,     new DateTime(2018, 1, 16), "France" },
{ 10,         "Samsung S9",            "Mobile",                7431,     new DateTime(2018, 1, 16), "Canada" },
{ 11,         "OnePlus 7Pro",          "Mobile",                8250,     new DateTime(2018, 1, 16), "Germany" },
{ 12,         "Redmi 7",               "Mobile",                7012,     new DateTime(2018, 1, 18), "United States" },
{ 13,         "Bose 785593-0050",      "Consumer Electronics",  1903,     new DateTime(2018, 1, 20), "Germany" },
{ 14,         "Canon EOS 1500D",       "Consumer Electronics",  2824,     new DateTime(2018, 1, 22), "Canada" },
{ 15,         "Haier 394L 4Star",      "Consumer Electronics",  6946,     new DateTime(2018, 1, 24), "France" },
};

// Assigning data to the range.
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["G1:L16"].Value = sourceData;

// Creating pivot table with range.
var pivotcache = workbook.PivotCaches.Create(worksheet.Range["G1:L16"]);
var pivottable = worksheet.PivotTables.Add(pivotcache, worksheet.Range["A1"], "pivottable1");

// Configure pivot table's fields.
var field_Category = pivottable.PivotFields["Category"];
field_Category.Orientation = PivotFieldOrientation.ColumnField;

var field_Product = pivottable.PivotFields["Product"];
field_Product.Orientation = PivotFieldOrientation.RowField;

var field_Amount = pivottable.PivotFields["Amount"];
field_Amount.Orientation = PivotFieldOrientation.DataField;
field_Amount.NumberFormat = "$#,##0.00";

var field_Country = pivottable.PivotFields["Country"];
field_Country.Orientation = PivotFieldOrientation.PageField;

//Save the workbook.
workbook.Save(@"ExcelOutput\PivotTable.xlsx");
```

![](https://cdn.mescius.io/document-site-files/images/e333ad47-35da-43f9-a389-25433765f491/images/pivot-table-range.png)

## Create Pivot Table with Table-Based Data Source

Refer to the following example code to create a pivot table in a worksheet with a table:

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

// Create source for the table.
object[,] sourceData = new object[,] {
{ "Order ID", "Product",               "Category",              "Amount", "Date",                    "Country" },
{ 1,          "Bose 785593-0050",      "Consumer Electronics",  4270,     new DateTime(2018, 1, 6),  "United States" },
{ 2,          "Canon EOS 1500D",       "Consumer Electronics",  8239,     new DateTime(2018, 1, 7),  "United Kingdom" },
{ 3,          "Haier 394L 4Star",      "Consumer Electronics",  617,      new DateTime(2018, 1, 8),  "United States" },
{ 4,          "IFB 6.5 Kg FullyAuto",  "Consumer Electronics",  8384,     new DateTime(2018, 1, 10), "Canada" },
{ 5,          "Mi LED 40inch",         "Consumer Electronics",  2626,     new DateTime(2018, 1, 10), "Germany" },
{ 6,          "Sennheiser HD 4.40-BT", "Consumer Electronics",  3610,     new DateTime(2018, 1, 11), "United States" },
{ 7,          "Iphone XR",             "Mobile",                9062,     new DateTime(2018, 1, 11), "Australia" },
{ 8,          "OnePlus 7Pro",          "Mobile",                6906,     new DateTime(2018, 1, 16), "New Zealand" },
{ 9,          "Redmi 7",               "Mobile",                2417,     new DateTime(2018, 1, 16), "France" },
{ 10,         "Samsung S9",            "Mobile",                7431,     new DateTime(2018, 1, 16), "Canada" },
{ 11,         "OnePlus 7Pro",          "Mobile",                8250,     new DateTime(2018, 1, 16), "Germany" },
{ 12,         "Redmi 7",               "Mobile",                7012,     new DateTime(2018, 1, 18), "United States" },
{ 13,         "Bose 785593-0050",      "Consumer Electronics",  1903,     new DateTime(2018, 1, 20), "Germany" },
{ 14,         "Canon EOS 1500D",       "Consumer Electronics",  2824,     new DateTime(2018, 1, 22), "Canada" },
{ 15,         "Haier 394L 4Star",      "Consumer Electronics",  6946,     new DateTime(2018, 1, 24), "France" },
};

// Assigning data to the range.
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["G1:L16"].Value = sourceData;
worksheet.Range["G:L"].ColumnWidth = 15;

// Create table with range G1:L16.
ITable table = worksheet.Tables.Add(worksheet.Range["G1:L16"], true);

// Create pivot table using table as data source.
var pivotcache = workbook.PivotCaches.Create(table);
var pivottable = worksheet.PivotTables.Add(pivotcache, worksheet.Range["A1"], "pivottable1");
worksheet.Range["J1:J16"].NumberFormat = "$#,##0.00";

// Configure pivot table's fields.
var field_Category = pivottable.PivotFields["Category"];
field_Category.Orientation = PivotFieldOrientation.ColumnField;

var field_Product = pivottable.PivotFields["Product"];
field_Product.Orientation = PivotFieldOrientation.RowField;

var field_Amount = pivottable.PivotFields["Amount"];
field_Amount.Orientation = PivotFieldOrientation.DataField;
field_Amount.NumberFormat = "$#,##0.00";

var field_Country = pivottable.PivotFields["Country"];
field_Country.Orientation = PivotFieldOrientation.PageField;

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

// Save the workbook.
workbook.Save("CreatePivotTableUsingTable.xlsx");
```

![](https://cdn.mescius.io/document-site-files/images/e333ad47-35da-43f9-a389-25433765f491/images/pivot-table-table.png)

## Use Picture in Cell as Pivot Table Data Source

Pivot tables support using ranges that contain in-cell pictures as the data source. When sorting or filtering pivot fields that are based on in-cell pictures, the picture's alt text is used for comparison and filtering.
Refer to the following example code to create a pivot table with in-cell pictures as a source.

```csharp
// Create a new workbook.
Workbook workbook = new Workbook();
IWorksheet sheet = workbook.ActiveSheet;

// Set data.
object[,] data = new object[,]
{
    { "Item", "Category", "Quantity", "Image" },
    { "Dress", "Clothing", 10, null },
    { "Hat", "Accessories", 5, null },
    { "Boots", "Shoes", 8, null },
    { "Glasses", "Accessories", 3, null }
};
sheet.Range["G1:J5"].Value = data;

// Set pictures in cells with alt text.
string[] imageFiles = { "Hat.png", "Boots.png", "Dress.png", "Glasses.png" };
for (int i = 0; i < imageFiles.Length; i++)
{
    byte[] imageData = File.ReadAllBytes(imageFiles[i]);
    sheet.Range[$"J{i + 2}"].CellPicture = new CellPicture(imageData, Path.GetFileNameWithoutExtension(imageFiles[i]));
}

// Configure pivot table's fields.
var pivotcache = workbook.PivotCaches.Create(sheet.Range["G1:J5"]);
var pivottable = sheet.PivotTables.Add(pivotcache, sheet.Range["A1"], "pivottable");
pivottable.PivotFields["Category"].Orientation = PivotFieldOrientation.ColumnField;
pivottable.PivotFields["Image"].Orientation = PivotFieldOrientation.RowField;
pivottable.PivotFields["Quantity"].Orientation = PivotFieldOrientation.DataField;

// Save to an Excel file.
workbook.Save("PivotTablewithPicture.xlsx");
```

The output is shown in the figure below:
![image](https://cdn.mescius.io/document-site-files/images/6c089d65-4816-4591-8c0d-bb0bfb8c43df/image-20260414.eb2da1.png)
**Limitation**
DsExcel does not support referencing partial table area, such as 'Table1[[Column1]:[Column3]]'.

<br>