Data
- Example Pivot Table
- Break down
1. Set up the data source
The data source needs to be a continuous block of cells with a header row with column names. The data source can be defined as an Area
or a NamedRange
. The column names become the
Here is a snapshot of the data for this tutorial:
<IMAGE>
2.
3. After creating the PivotTable, always set RefreshOnOpen
to true
.
ExcelWriter does not have the ability to render a PivotTable, so any modifications made to a PivotTable will not take affect until the output file is opened in Excel and the PivotTable is refreshed. If RefreshOnOpen
is true
, Excel will refresh the PivotTable when the workbook opens, which will re-render the PivotTable.
4. Next, add PivotTableFields
to the PivotTable. There are four types of PivotTableField
: DataFields
, RowLabels
, ColumnLabels
, and PageFields
(or report filters).
To add
5. Adding data fields - calculated fields, naming conventions, changing the display name
3. Adding a pivot table field - row labels / column labels, source fields, naming convention, changing the display name
4. Adding a page field - behavior
5. CHECK
6. Other PivotTable properties - friendly error on value, empty value
7. Field properties, set row label
8. Summarize By
9. Show Value As
- Additional resources
- Answers post on CopySheet
- Modifying an existing PivotTable
- Documentation on creating an ExcelTemplate PivotTable
- PivotTable Glossary
- Answers post on how to set properties that aren't available
- Modifying an existing PivotTable