Power Pivot Hierarchy: Create and Use One in Excel

A Power Pivot hierarchy is an ordered set of related columns from one table in Excel’s Data Model, such as Year, Quarter, Month, and Date. Create it in the Power Pivot window by switching to Diagram View, right-clicking a column, choosing Create Hierarchy, and adding the other columns as levels; then use the hierarchy in a PivotTable to drill through those levels.

What a Power Pivot hierarchy does

A hierarchy gives PivotTable users a convenient way to move between levels of detail without adding each field to the layout separately. For example, a Geography hierarchy might contain Country, State or Province, and City. A user can start with totals by country, expand a country to see its states, and then expand a state to see its cities.

The hierarchy is model metadata: it organizes existing columns but does not combine their values, create new records, or change the source data. It also does not calculate totals. Measures and numeric columns still provide the values summarized in the PivotTable; hierarchy levels provide the categories by which those values are grouped.

In Excel, Power Pivot hierarchies are useful when a workbook’s Data Model is reused for multiple reports. Instead of asking every report author to remember the right order of fields, a model creator can define a reusable structure once. The hierarchy then appears as a single expandable item in the PivotTable Fields list.

Hierarchy versus relationship

A hierarchy and a relationship solve different problems. A hierarchy arranges columns from the same table into an ordered drill path. A relationship connects tables so that fields from different tables can work together in a Data Model. For example, a Calendar table can relate to a Sales table through a date key, while Year, Quarter, Month, and Date can form a hierarchy within Calendar.

Creating a hierarchy does not establish a relationship, and creating a relationship does not automatically create a hierarchy. If a PivotTable needs fields from separate tables, check the relationships in the model as well as the hierarchy. A missing or incorrect relationship can lead to unexpected totals even when the hierarchy itself is set up correctly.

Power Pivot hierarchies are also different from a parent-child structure where each item can have a variable number of descendants, such as an organization chart with uneven reporting depth. A standard hierarchy is a fixed sequence of levels. It is best suited to predictable categories such as continent, country, and city, or year, quarter, month, and day.

Before you create one

Power Pivot works with the Excel Data Model, rather than only with a normal worksheet range. Your source data should have clearly named columns and consistent values. A hierarchy can contain columns from a single table; it cannot combine, for instance, Country from one table and City from a separate table into one Power Pivot hierarchy. You can still place related fields from different tables in a PivotTable, but that is not the same feature.

Check the intended order before building the hierarchy. Each level should move from broader categories to more detailed ones. For a product catalog, that might be Category, Subcategory, and Product Name. For a location report, it might be Region, Country, and City. Poorly ordered levels make drill-down confusing even if the data is otherwise valid.

In Excel for Microsoft 365 on Windows, if the Power Pivot tab is not visible, enable the add-in through File > Options > Add-ins. In the Manage box, select COM Add-ins, choose Go, and select Microsoft Power Pivot for Excel if it is listed. The Power Pivot tab should then be available. Excel for Mac does not provide the same Power Pivot window for creating and managing hierarchies; Mac users can work with ordinary PivotTables, but should use Excel for Windows for these Power Pivot modeling steps.

Create a hierarchy in Power Pivot

Make sure the relevant table is in the Data Model. For an Excel table, use Power Pivot > Add to Data Model. You can also load data into the model through Power Query by choosing to add the data to the Data Model during the load process.
Choose Power Pivot > Manage to open the Power Pivot window. Select Diagram View so the model tables and their columns are visible.
Find the table that contains the levels. Right-click the first, broadest column and choose Create Hierarchy. Power Pivot creates a hierarchy under that table, initially using the selected column.
Give the hierarchy a meaningful name, such as Product Drilldown or Calendar. Drag the remaining columns from the same table onto the hierarchy, arranging them in the desired order. You can also add a field and then reposition it within the hierarchy.
Confirm the order from top to bottom, then close or return from the Power Pivot window. The hierarchy is stored in the workbook’s Data Model.

For example, if the Product table has Category, Subcategory, and Product Name columns, create a hierarchy starting with Category, then add Subcategory and Product Name. Do not add a sales amount as a level: amounts are generally values to summarize, not categories to drill through.

Use the hierarchy in a PivotTable

Create a PivotTable from the Data Model using Insert > PivotTable and choose the option to use this workbook’s Data Model. In the PivotTable Fields pane, locate the table and expand it if needed. The hierarchy appears as an item that can be expanded to reveal its levels. Add the hierarchy to Rows or Columns, and add a measure or suitable numeric field to Values.

With the Product Drilldown hierarchy in Rows and Sales Amount in Values, the PivotTable can show sales by category, then subcategory, then product. Expand and collapse controls let a reader change the displayed detail. Depending on the PivotTable layout and Excel settings, the user can also right-click a label and use the expand or collapse options. A hierarchy is a navigation aid; it does not force every report to display every level at once.

To keep the report readable, consider starting at a useful summary level and expanding only where investigation is needed. If a PivotTable displays every product immediately, the hierarchy still exists, but the initial view may be too detailed to scan. Report authors can also place separate levels in the layout when they need a more customized arrangement.

Build a useful date hierarchy

A common example is a Calendar table with Year, Quarter, Month Name, and Date columns. Place those columns in that order in a hierarchy. Relate the Calendar table’s date column to the corresponding date column in the fact table, such as Sales. Using a dedicated Calendar table makes the reporting structure explicit and lets the same date categories serve multiple measures or fact tables when the model relationships support that design.

Month names need special attention. If they are stored as text, Excel may sort them alphabetically rather than January through December. In the Power Pivot window, select the Month Name column and use Home > Sort by Column, choosing a numeric Month Number column. The month labels can then display in calendar order. If the report spans more than one year, include Year above Month so that January from different years is not mistaken for one period.

Excel may also offer date grouping in a PivotTable, such as grouping dates by months or years. That grouping is not the same as a hierarchy you define in Power Pivot. A model hierarchy is reusable and explicitly ordered; PivotTable date grouping is a separate report-level feature and may not be available in the same way for every Data Model setup.

Common problems and how to fix them

The hierarchy is missing from the field list: Confirm that the table is in the Data Model and that the hierarchy was created in the Power Pivot window. Refresh or reopen the PivotTable Fields pane if it was already open when the model changed.
A level cannot be added: Check that the column belongs to the same table as the hierarchy. A relationship between two tables does not make their columns eligible for one hierarchy.
Months or categories appear in the wrong order: Text labels often sort alphabetically. For months, sort Month Name by a numeric Month Number column in Power Pivot. For other categories, check whether the source has a reliable sort key.
Totals look duplicated or unexpected: Investigate the model relationships and the fields used in Values. A hierarchy only changes how categories are organized; it cannot correct a many-to-many modeling issue, an incorrect relationship, or an inappropriate aggregation.
The hierarchy expands to an unhelpful level: Review the level order and the starting view of the PivotTable. Remove redundant levels, use clearer names, or add only the detail the audience needs.

Also distinguish a blank value from a missing hierarchy level. If a source row has no City, expanding to City may show a blank category. That is a data-quality issue in the source or model, not evidence that the hierarchy has failed. Decide whether blanks should be corrected, labeled, or intentionally retained.

When to use a hierarchy—and alternatives

Use a Power Pivot hierarchy when users need a repeatable drill path through columns in one table. It is especially useful for dimensions such as dates, geography, and product classifications, where each level has a clear relationship to the next. Give the hierarchy a name that describes its purpose, and check that the order matches how people naturally explore the information.

If the report needs a custom layout rather than a reusable drill path, add the individual fields to Rows or Columns instead. If users need to compare unrelated categories, separate fields may be clearer than forcing them into one hierarchy. If the values belong to different tables, model the relationships correctly and use the related fields in the PivotTable rather than trying to combine them into a single hierarchy.

For a simple worksheet table that is not part of the Data Model, a conventional PivotTable can still group fields or use multiple row fields, but it does not offer the same Power Pivot hierarchy metadata. If you need the model-based hierarchy feature, add the source to the Data Model and create the hierarchy in Power Pivot.

Frequently Asked Questions

Can a Power Pivot hierarchy include columns from different tables?
No. A Power Pivot hierarchy uses columns from one table. Relationships can let a PivotTable use fields from connected tables together, but they do not make a cross-table hierarchy.

Does a hierarchy change the source data or calculate totals?
No. It organizes existing columns for navigation in reports. PivotTable values and measures perform the calculations; the hierarchy supplies the ordered categories used to group or explore those values.

Can I rename a hierarchy or change its level order?
Yes. Open Power Pivot > Manage, locate the hierarchy in Diagram View, and edit its name or reorder its levels. Keep the broadest level first and the most detailed level last unless the report has a specific reason to use another sequence.

Why does my Month Name level sort alphabetically?
Month names are text, so they may sort alphabetically. Add a numeric month-order column to the Calendar table, then select Month Name in Power Pivot and use Home > Sort by Column to sort it by Month Number.

Is a Power Pivot hierarchy the same as a date hierarchy?
Not necessarily. A Power Pivot hierarchy is a structure you define from columns in a table. Excel’s PivotTable date grouping is a separate feature, and a date hierarchy is simply a hierarchy whose levels represent time, such as Year, Quarter, Month, and Date.

Can I create a Power Pivot hierarchy in Excel for Mac or Excel for the web?
The Power Pivot modeling window used to create and edit these hierarchies is a Windows Excel feature. Mac and web editions do not provide the same authoring workflow, so create or modify the hierarchy in Excel for Windows.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.