Pivot Table Issues

How to Display Multiple PivotTable Hierarchies in Separate Columns

Huda QurayshiHuda Qurayshi Nov 11, 2026 869 views

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.

How to Display Multiple PivotTable Hierarchies in Separate Columns
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.
Before you start

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.

Solution 1Recommended

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.

1
Import data to Data Model

Import your dataset or connect your Power BI semantic model to the Excel Data Model using Power Pivot.

2
Create explicit measures

Create explicit measures for the values you want to analyze, such as Total Cost or Revenue, within the Power Pivot manager.

3
Insert PivotTable

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.

4
Convert to Formulas

Select the PivotTable, go to the 'PivotTable Analyze' tab on the ribbon, click on 'OLAP Tools', and choose 'Convert to Formulas'.

5
Customize layout manually

Manually arrange the resulting CUBEVALUE and CUBEMEMBER functions into separate columns or rows to achieve your desired independent hierarchy layout.

Use an Excel Data Model and Cube Functions
Cube Functions Flexibility: Converting to formulas removes the rigid PivotTable grid, replacing it with formulas that extract data directly. This allows you to insert blank columns or rows anywhere in your report.
Free Microsoft Office alternative

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. 1. Download and Install WPS Office: Visit the official WPS website, download the free version of WPS Office, and follow the installation prompts.
  2. 2. Open WPS Spreadsheet: Launch WPS Office and click on 'Spreadsheet' to open a new or existing workbook.
  3. 3. Create a PivotTable: Select your data range, go to the 'Insert' tab, and click 'PivotTable' to start analyzing your data.
Fully compatible with Microsoft Excel formats (.xlsx, .xls, .csv)Create, manage, and customize PivotTables with an intuitive, familiar interfaceLightweight application that runs smoothly on Windows, Mac, and LinuxBuilt-in advanced formulas for comprehensive data analysis
microsoft office alternative - wps office

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.