Hierarchy in Excel (Create, Customize, Features)

Get FREE Advanced Excel Exercises with Solutions!

In this article, we are going to learn about hierarchy in Excel. We shall use the SmartArt feature to create and customize a hierarchical structure such as an organizational chart.

We’ll also use the PivotTable and Power Pivot features of Excel to create and customize a structured arrangement of hierarchical data.

Excel Hierarchy finds application in representing organizational structures, product categorization, geographical hierarchies, financial analysis, project management, human resources, decision trees, and academic structures.

It facilitates clear visualization, effective reporting, and informed decision-making by representing complex relationships and enabling drill-down capabilities.

Overview to Hierarchy in Excel

Download Practice Book

You can download the free Excel workbook here and practice on your own.

What Is Hierarchy?

The term “hierarchy” has two distinct meanings. The first, and simpler definition refers to a particular kind of chart that aids in visualizing a hierarchical structure, such as an organizational chart.

On the other hand, a hierarchy refers to a structured arrangement of data where items are organized into levels or categories based on their relationships.

In Excel, hierarchies are often used in conjunction with pivot tables and Power Pivot.

How to Create Hierarchy in Excel

Method 1: Using SmartArt Feature

First, select the raw data table for the hierarchy (B5:B11). Click on Insert >> Illustrations >> SmartArt.

Launching SmartArt Feature

A dialogue box will appear. From the left side, select Hierarchy. Choose a hierarchy style and press OK.

Choosing a SmartArt Graphic

Click on the marked arrow sign. Place the cursor inside the hierarchy chart and press CTRL+A.

Selecting All the Attributes

Press Delete. All the default text will disappear.

Deleting All the Attributes

Then, copy range B5:B11 and paste them on the Organization Chart… Now, place managers below the CEO by selecting them individually and pressing Tab once for each. And as for the executives, you need to press Tab twice for each of them.

Creating Hierarchy in Excel

If your desired organogram is bigger and more detailed, follow the same procedure. That means you have to press Tab thrice, four times, or more if you want to add more levels below executives.

Lastly, you can choose one layout from the Layouts options and one style from SmartArt Styles, to give your hierarchy a customized look.

Fixing Layout and SmartArt Styles of Hierarchy

Method 2: Using Pivot Table

First, select the range B4:D10. Click on Insert >> Pivot Table.

Inserting Pivot Table for Excel Hierarchy

You will see that a dialogue box will appear and Table/Range: field is filled with your previously selected range. Press OK.

Confirming Table or Range for Pivot Table

A new worksheet will be created. Then, under PivotTable Fields, check the table attributes. Team and Executives are selected as rows and Revenue is placed under Values as the Sum of Revenue. Your Pivot Table hierarchy is now complete.

Organizing Pivot Table for Excel Hierarchy

Read More: Create Hierarchy in Excel Pivot Table

Method 3: Using Power Pivot

You can also create a hierarchy using the Power Pivot feature in Excel. First, select the range B4:D10 and press CTRL+T. Your dataset is now turned into an Excel table.

Inserting Table for Excel Hierarchy with Power Pivot

Again select the newly created table and click on Power Pivot >> Add to Data Model.

Adding Table to Data Model

Note: The Power Pivot is not a default tab inside Excel. If you have not used this feature before, you have to manually add the Power Pivot tab to your Excel Ribbon.

A new window named Power Pivot for Excel will be opened. Select the three columns that appear inside the window under the table name and right-click on them. Select Create Hierarchy.

Creating Hierarchy with Power Pivot

After that, select the table and click on Home >> PivotTable.

Inserting Pivot Table with Power Pivot

A new worksheet will be created containing a Pivot Table. Under PivotTable Fields, check Hierarchy1 under Table1 and check Revenue under More Fields. Your Power Pivot hierarchy is now complete.

Organizing Pivot Table with Power Pivot

Read More: Create Hierarchy in Excel

How to Customize SmartArt Hierarchy in Excel

Example 1: Add or Delete Boxes

Now, if you want to add extra boxes to your previously generated SmartArt hierarchy, first, click on the box after which you want to add the extra box(s) and click on SmartArt Design >> Add Shape >> Add Shape After.

Adding Hierarchy Box

Following this method, I have added a whole extra hierarchy branch with three extra attributes.

New Hierarchy Branch in Excel

Deleting a box is simpler. Just select the box you want to delete and press Delete on the keyboard. The job is done.

Example 2: Change Colors or Hierarchy Styles

If you want to change the current hierarchy style, all you need to do is to select the hierarchy flowchart, then click on SmartArt Design, and from the SmartArt Styles group select a style of your preference.

Exploring SmartArt Styles

In case of changing colors, select an object (box or flowchart lines) and right-click on it. From the options list, select Format Shape.

Under Fill & Line, there is a number of customization options for that particular object such as Color, Transparency, Width, etc. You can use them to change the color or appearance of your hierarchy flowchart.

Changing Line Color of Excel Hierarchy

Different Features in Excel Power Pivot Hierarchy

Changing Hierarchy Order

Suppose, you have created a hierarchy using the Excel Power Pivot feature. Now, if you want to change the order of that hierarchy, simply right-click on the element that you want to change the position of and select Move Up or Move Down.

The order of the hierarchy will be adjusted according to your need.

Changing Order of Excel Hierarchy

Hiding or Showing Source Column Name in Hierarchy

The source column name is by default visible inside a bracket beside every column name. But if you want to hide them, simply right-click on any of the columns and select Hide Source Column Name.

Repeat the process for the rest of the columns.

Hiding Source Column

Now, if you want again to show the hidden source column names, just right-click on any of them and select Show Source Column Name.

Repeat this process for the rest of the column names and the source column names will be visible again.

Showing Source Column

Drill Up and Drill Down in Hierarchy

Suppose, after creating a hierarchy Pivot Table using Power Pivot, you want to show only the attributes inside a specific row and hide all the other rows and attributes.

You just need to select that particular row and click on PivotTable Analyze >> Drill Down under Active Field group.

Selecting Drill Down for Marketing Team

The Marketing Team is now drilled down.

Marketing Team Drilled Down

Again if you want to drill up, select the first attribute of the drilled-down list and click on PivotTable Analyze >> Drill Up under Active Field group.

Selecting Drill Up for Marketing Team

The Marketing Team is now drilled up as before.

Marketing Team Drilled Up

Things to Remember

  • Arrange your data in a logical order within each level. Sorting the data within each level can make it easier to navigate and analyze.
  • Hierarchies may require updates as your data changes. If new items are added or existing ones are modified or removed, ensure that your hierarchy accurately reflects these changes to maintain data integrity.

Frequently Asked Questions

  • How do I add a hierarchy slicer in Excel?

To add a hierarchy slicer in Excel, follow these steps:

  1. Select the range of cells that contains your data, including the headers.
  2. Go to the “Insert” tab in the Excel ribbon.
  3. In the “Filters” group, click on the “Slicer” button.
  4. In the “Insert Slicers” dialog box, select the column that represents the top-level of your hierarchy.
  5. Click on the “OK” button.
  6. A slicer box will be added to your worksheet. You can resize and reposition it as needed.
  7. To create the hierarchy, right-click on the slicer and choose “Slicer Settings.”
  8. In the “Slicer Settings” dialog box, click on the “Report Connections” button.
  9. Check the boxes for the columns that represent the lower levels of your hierarchy.
  10. Click on the “OK” button.
  11. Your hierarchy slicer is now ready. You can use it to filter and navigate through your data by selecting different levels of the hierarchy.
  • How do you create a hierarchy tree in Excel?

Select your data. Go to the Insert tab >> Insert Hierarchy Chart >> Treemap. You can also use Recommended Charts to create a treemap chart by going to Insert >> Recommended Charts >> All Charts.


By going through this article, I hope you can now effectively manage and analyze data using hierarchy in Excel, ensuring accurate insights and meaningful representations of your data structure.

To further expand your knowledge and expertise in Excel, I encourage you to visit Exceldemy, a valuable resource for in-depth information on various uses of Microsoft Excel. There, you can explore a wide range of tutorials, tips, and tricks that will empower you to leverage the full potential of this powerful spreadsheet software.

Hierarchy in Excel: Knowledge Hub

<< Go Back to SmartArt in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Nafis Soumik
Md. Nafis Soumik

Md. Nafis Soumik graduated from Bangladesh University of Engineering & Technology, Dhaka, with a BSc.Engg in Naval Architecture & Marine Engineering. In January 2023, he joined Softeko as an Excel and VBA content developer, contributing 50+ articles on topics including Data Analysis, Visualization, Pivot Tables, Power Query, and VBA. Soumik participated in 2 specialized training programs on VBA and Chart & Dashboard designing in Excel. During leisure, he enjoys music, travel, and science documentaries, reflecting a diverse range... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF