How to Create Hierarchy in Excel (3 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

In Excel, 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. Power Pivot hierarchies, on the other hand, let you quickly drill up and down through a list of nested columns in a table. In this article, we will discuss how to create a hierarchy in Excel in 3 ways.

In this article, we will discuss 3 easy ways to create a hierarchy in Excel. Firstly, we will use the SmartArt feature. Then, we will go to the pivot table to create a hierarchy. Finally, we will illustrate the use of the Power Pivot toolbar to create a hierarchy in Excel. We will use the sample data below to illustrate the methods.

how to create hierarchy in excel


1. Using SmartArt Feature to Create Hierarchy in Excel

In this method, we will visually represent a hierarchy of an organization using the SmartArt feature. This feature allows us to choose a graphic that represents hierarchy.

Steps:

  • Firstly, copy the entire dataset.
  • Secondly, go to the Insert tab in the ribbon.
  • From the Illustration group, select the SmartArt toolbar.
  • Consequently, a dialogue bar will be on the screen.

Using SmartArt Feature

  • Then, from the dialogue box firstly, select the Hierarchy option.
  • Next, choose the type of hierarchy graphic you prefer.
  • Finally, click OK.

Selecting Hierarchy Graphic

  • From the graphic, click on the outward arrow to get a dialogue box.

Using SmartArt Feature

  • Then, keep the cursor on the dialogue box and press Ctrl+A.
  • Consequently, the entire data in the graphic will be selected.

Selecting Default Data From Graphic

  • After that, click the Delete button to delete the default data.

Deleting Default Data

  • Next, keep your cursor on the dialogue box and press Ctrl+V.
  • Consequently, our dataset will be pasted in the dialogue box.

Pasting Sample Dataset

  • Then, choose the Sales Manager option and press Tab once.
  • Since the Sales Manager reports to the CEO, this will allow us to illustrate that.

Tab to Create Hierarchy

  • Next, select the Sales Executive1 option and double Tab.

Tab to Create Hierarchy

  • Repeat the previous two steps to get a proper hierarchy illustration.

 Creating Hierarchy in Excel

  • Finally, you can format the hierarchy tree by using the Layouts and SmartArt Styles options from the SmartArt Design options.

Formatting Hierarchy

Read More: How to Use SmartArt Hierarchy in Excel


2. Use of Excel Pivot Table to Create Hierarchy

In this instance, we will opt for the Pivot Table to create a hierarchy in Excel. This table will allow us to illustrate our data in hierarchical order.

Steps:

  • To begin with, select any data from the dataset.
  • Then, go to the Insert tab in the ribbon.
  • From there select the Pivot Table tab.
  • Consequently, the Pivot Table dialogue box will appear.

Inserting Pivot Table

  • From the dialogue box, select the range of your dataset as the Table/Range.
  • Finally, click OK.

Selecting Pivot Table Range

  • As a result, you will have a PivotTable Fields option in a new worksheet.
  • After that, select the Executives and Team option from the Pivot Table Fields
  • The options will be illustrated as Rows in the pivot table.

Using PivotTable Fields

  • Then select the Revenue option as Values.

Using PivotTable Fields

  • Finally, you get the hierarchy of different teams.
  • You can easily show, who works in which team/ department and also their revenue.

Create Hierarchy in Excel

  • You can also minimize the tabs, to give a terse appearance to your pivot table.

Minimizing or Expanding PivotTable


3. Creating Hierarchy in Excel Power Pivot

In the final method, we will use the Power Pivot add-in to create a hierarchy. This is nothing but a pivot table. But unlike the pivot table, this allows us to group the data to create a hierarchy.

Steps:

  • Firstly, select the entire dataset.
  • Then, go to the Insert tab in the ribbon.
  • From there, insert a Table.

Inserting Table

  • Click OK, from the Create Table dialogue box.

Selecting Table Range

  • Consequently, the dataset will be turned into a table.

Data as Table

  • After that, go to the Power Pivot tool bar.
  • Then, select Add to Data Model option.
  • Consequently, a new Power Pivot window will be opened.

Using Power Pivot Toolbar

  • In the Power Pivot window, firstly, go to the Home tab.
  • Then, from the View group select the Diagram View.
  • Consequently, the dataset will appear in the diagram view.

Accessing Diagram View

  • Now, right-click after selecting all the options simultaneously.
  • From the available options, select Create Hierarchy.
  • Consequently, a hierarchy will be created containing all the selected options.

Creating Hierarchy

  • After that, from the Home tab select the Pivot Table command.

Inserting PivotTable

  • Consequently, you will find that a hierarchy is created.

Using PivotTable Fields


Download Practice Workbook

You can download the practice workbook here.


Conclusion

In this article, we have learned to create a hierarchy in Excel in 3 different ways. This will allow us to illustrate our data more clearly and allow the viewers to understand it properly.


Related Articles


<< Go Back to Hierarchy in Excel | 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.
Adnan Masruf
Adnan Masruf

Adnan Masruf, holding a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, plays a pivotal role as an Excel & VBA Content Developer at ExcelDemy. His deep passion for research and innovation seamlessly aligns with his dedication to Excel. In this capacity, Masruf not only skillfully addresses challenging issues but also exhibits enthusiasm and expertise in gracefully navigating intricate situations, underscoring his steadfast commitment to consistently delivering exceptional content. His interests... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo