How to Create Hierarchy in Excel (3 Easy Ways)

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.


Download Practice Workbook

You can download the practice workbook here.


3 Handy Ways to Create Hierarchy in Excel

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

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 Make Hierarchy Chart in Excel (3 Easy Ways)


2. Utilizing Pivot Table

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 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

Read More: Create Date Hierarchy in Excel Pivot Table (with Easy Steps)


3. Create Hierarchy in 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

Read More: How to Create Multi Level Hierarchy in Excel (2 Easy Ways)


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

Adnan Masruf

Adnan Masruf

I am an engineering graduate. I graduated from Bangladesh University of Engineering and Technology(BUET), one of the top universities in Bangladesh from department of Naval Architecture & Marine Engineering with a major in structure. I am an avid reader of fiction especially fantasy. I also keep myself abreast of the recent developments in science and technology. I believe diligence will eventually pay off and luck tends to favor those who work hard.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo