A newer version of this page is available. Switch to the current version.

How to: Change a Data Source for a Pivot Table

  • 2 minutes to read

To change a data source for a PivotTable report, use the PivotTable.ChangeDataSource method overloads. You can update your pivot table to reflect changes in the initial source range (e.g., if it was extended by new rows or columns, or existing records or fields were removed), or rebuild the report based on a completely different cell range, as shown in the example below. In the latter case, you should re-create the pivot table and fill it with new data by adding necessary fields to it.

Worksheet worksheet = workbook.Worksheets["Report1"];
workbook.Worksheets.ActiveWorksheet = worksheet;

// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
Worksheet sourceWorksheet = workbook.Worksheets["Data2"];
// Change the data source of the pivot table.

// Add the "State" field to the row axis area.
// Add the "Yearly Earnings" field to the data area.
PivotDataField dataField = pivotTable.DataFields.Add(pivotTable.Fields["Yearly Earnings"]);
// Calculate the average of the "Yearly Earnings" values for each state.
dataField.SummarizeValuesBy = PivotDataConsolidationFunction.Average;