Although PivotTables and ExcelWriter’s Grouping and Nesting can produce similar looking results when used with ExcelTemplate, these options provide different functionality. This post outlines the pros and cons of using both options.
Pivot Tables
Pivot tables are a native Excel feature that are designed to sort and filter aggregate data. You can use them to transpose and combine data to present grouped views.
Useful Features
- Group data – This can be done horizontally in row groups or vertically in column groups.
- Sort data – Pivot tables automatically sort and group data.
- Produce grouped calculations – i.e. the sum over all of the sales for a region, or the larger expense for a particular quarter
- Transpose data
Limitations (when working with ExcelWriter)
- You cannot pre-select filters, create groupings by hand etc. – Any feature that requires data to be in the pivot table cannot be selected in the template because the data won’t be imported until after the report is generated
- Pivot tables have limited style options. Pivot tables can have table styles, but creating custom headers/footers for groups is challenging
- You cannot put anything to the right or below the pivot table. This is because when the pivot table is populated, it will expand to the right and down. It will overwrite anything that was in those cells. For this reason it is recommended that you do not have multiple pivot tables on a worksheet.
- Pivot tables do not allow for text in the data values area of the table. Text is only allowed in the report filters, row labels, or column labels.
PivotTables in OfficeWriter Docs:
- Templates and PivotTables (for ExcelWriter 7.6 and above)
- Template and PivotTables in Older Versions (for ExcelWriter 7.5.1 and below)
Grouping and Nesting
ExcelWriter Grouping and Nesting allows you to format grouped data in OOXML (XLSX, XLSM) files using ExcelTemplate.
Features
- Custom formatting – You have the ability to create multi-row headers and footers that contain whatever formulas, formatting, and content you’d like.
- ‘Push down’ behavior – ExcelTemplate automatically pushes down rows when inserting data. This means that anything below a grouping block will be retained in the final report.
- Accepts any data type – There are no limitations on the type of data that be represented (unlike pivot tables which only allow non-numerical data in the row/column labels).
Limitations
- Data must be sorted ahead of time, otherwise Grouping and Nesting will not work properly (see information below on how to create Grouped and Nested reports)
- Unable to group over columns
Grouping and Nesting in OfficeWriter Docs:
Share the post "Grouping and Nesting vs. PivotTables in ExcelTemplate"
Follow