Convert Pivot
Effortlessly convert a PivotTable or Power PivotTable into well-formatted standard cells, maintaining hierarchies. This tool:
Applies customizable formatting
Inserts SUM or SUBTOTAL functions to aggregated hierarchies
Adds row groups/outlines for collapsible details
Indents labels for clearer visual separation
Pro Tip: Use PivotTables for quick data analysis and then convert them to standard cells for complete formatting and customization control for presentational purposes.
The video below demonstrates how to automatically convert a Power PivotTable into formatted CUBE formulas (CUBEVALUE and CUBEMEMBER).
Unfamiliar with Excel CUBE formulas? Learn more.
For the row or vertical hierarchy, the tool applies formatting according to these rules:
The 'Total Row' format is always used for totals.
Hierarchy levels 1 through your specified level (see screenshot) are formatted with the 'Subtotal Row' format.
The next hierarchy level remains unchanged.
All additional hierarchy levels are grouped into higher rows groups and indented by one level.
Additional options:
Aggregation function: You can choose between using the SUM or SUBTOTAL functions for calculations.
Include mapping information: Selecting this will create a separate column for each hierarchy level / row field. If not selected, there will be a single column.
Exclude repetitive subtotal rows: This option removes duplicate labels when the same item appears on multiple levels.
Create a separate ‘ID column’: Useful for generating a two-column output, such as account names alongside account numbers.
Pivot Design
Simultaneously apply standard PivotTable design properties. Ideal to apply the same settings quickly on multiple PivotTables.
Tip: If you use this tool to collapse rows or columns, it will start collapsing from the lowest hierarchy level. This ensures that when you expand a group or hierarchy later, the lower levels remain collapsed, which typically doesn't happen with manual collapsing.
Other Tools
Remove Data Model
For PowerPivot/ PowerQuery users, this tool offers the ability to extract sheets without Excel's Data Model, converting CUBE formulas and PivotTables into values.
Note: The 'Data Model' refers to Excel's PowerPivot functionality, found under 'Data' > 'Data Tools' > 'Data Model'.
Convert Cube Formulas to Values
Convert all CUBE formulas in the selection to values, keeping all other formulas intact.