How to Display Multiple PivotTable Hierarchies in Separate Columns
Question details
The user needs to display independent hierarchies (like GL Account and Cost Center) in separate columns within an Excel PivotTable connected to a Power BI semantic model.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Creating a customized financial or analytical report that requires independent expansion of multiple hierarchies into separate column groups.
- Observed behavior
- Standard Excel PivotTables do not provide a native layout option that independently expands multiple hierarchies into completely separate column groups.
Ensure you have access to the underlying Power BI semantic model or raw data, and verify that the Power Pivot add-in is enabled in your Excel application.
Use an Excel Data Model and Cube Functions
Converting your PivotTable to Cube Functions offers complete control over the layout, allowing you to place independent hierarchies in separate columns.
Since standard PivotTables cannot independently expand multiple hierarchies into separate columns, you can bypass this limitation by using an Excel Data Model. By building the model with explicit measures, you can use Cube Functions to design a fully customized report layout.
Import your dataset or connect your Power BI semantic model to the Excel Data Model using Power Pivot.
Create explicit measures for the values you want to analyze, such as Total Cost or Revenue, within the Power Pivot manager.
Insert a standard PivotTable from the Data Model and add your hierarchies (e.g., GL Account, Cost Center) and measures to the Rows and Values areas.
Select the PivotTable, go to the 'PivotTable Analyze' tab on the ribbon, click on 'OLAP Tools', and choose 'Convert to Formulas'.
Manually arrange the resulting CUBEVALUE and CUBEMEMBER functions into separate columns or rows to achieve your desired independent hierarchy layout.

Change PivotTable Report Layout to Tabular Form
The Tabular layout separates different fields into different columns, which might meet your needs without requiring complex formulas.
Try WPS Office for Powerful Spreadsheet Data Analysis
While Power BI integration is specific to the Microsoft ecosystem, WPS Spreadsheet offers robust, free, and highly compatible tools for everyday data analysis, pivot tables, and reporting without heavy subscription costs.
- 1. Download and Install WPS Office: Visit the official WPS website, download the free version of WPS Office, and follow the installation prompts.
- 2. Open WPS Spreadsheet: Launch WPS Office and click on 'Spreadsheet' to open a new or existing workbook.
- 3. Create a PivotTable: Select your data range, go to the 'Insert' tab, and click 'PivotTable' to start analyzing your data.

Frequently Asked Questions
Can I place two different PivotTables side by side instead?
Yes, you can create two separate PivotTables from the same data source and place them in adjacent columns. This allows each hierarchy to expand independently, though they won't share the same row context or filter together automatically without Slicers.
Why does the Compact Form merge my PivotTable hierarchies?
By default, Excel uses Compact Form to save horizontal space, which places multiple row fields into a single column with indentation. Changing the layout to Tabular Form separates these fields into distinct columns.
What are Cube Functions in Excel?
Cube Functions are specialized Excel formulas (like CUBEVALUE and CUBEMEMBER) used to extract data directly from an OLAP cube or an Excel Data Model. They offer total freedom over report layout because they function as independent formulas rather than a rigid table.




