Tag Archives: creating a pivottable with excelwriter

ExcelWriter, Knowledge Base, OfficeWriter

PivotTables are not displaying correctly

Problem

After creating or modifying a PivotTable with ExcelWriter 8.4 or later, the PivotTable has not changed or is empty.

Solution

ExcelWriter does not have the ability to render PivotTables. Changes made to a PivotTable using ExcelApplication will not take effect until the PivotTable is refreshed. There are several ways to refresh the PivotTable:

  1. If you are editing an existing PivotTable, in the original file, go to PivotTable Options > Data > Check off “Refresh data when opening the file.” This will ensure that Excel refreshes the PivotTable as soon as the output is opened.
  2. If you are creating a new PivotTable from scratch, make sure to set the PivotTable.PivotTableSettings.RefreshOnOpen to true. This will force the PivotTable to refresh automatically when the output is opened.
  3. If you don’t want to have Excel refresh the PivotTable when the workbook opens, you can refresh the PivotTable manually or with a macro. Right-click the PivotTable and select Refresh.

We encourage users to keep “Refresh on Open” enabled when working with PivotTables in ExcelWriter to make sure that modified PivotTables load correctly in Excel.