How to Create Hierarchy Tree from Data in Excel (3 Examples)

Get FREE Advanced Excel Exercises with Solutions!

The article will show you how to create a Hierarchy Tree in Excel from data. The Hierarchy Trees are essential when we need to describe the workflow or the position of the employees in an organization to the new recruits. There are several formats of creating a Hierarchy Tree in Excel. I’ll show you an example by using one of them. In addition, I’ll also show you how to create a Treemap by using Excel charts.


Create Hierarchy Tree from Data in Excel: 3 Examples

We will create a hierarchy tree of an organization in this article. A brief description of this hierarchy tree is given below.

excel hierarchy tree from data


1. Using SmartArt Tool to Create Hierarchy Tree from Data

The hierarchy that we just mentioned can be made by using the SmartArt tools from the Insert tab. Please follow the description below for a better understanding.

Steps:

  • First, go to Insert >> Illustrations >> SmartArt.

Using SmartArt Tool to Create Hierarchy Tree from Data in Excel

  • After that, you will see the SmartArt Graphic
  • Next, select Hierarchy and choose a template you want. Here, I picked the Labeled Hierarchy

  • Thereafter, the Hierarchy template will appear.

  • Next, fill up the blank parts with the necessary designations that we mentioned in the beginning. The designations will be labeled by Level 1, Level 2, and so on.

  • We can see that the Hierarchy template isn’t enough to represent all the designations. From the description, we saw that a Departmental Head reports to the Production Manager. For that reason, we select the Production Manager box and go to SmartArt Design >> Add Shape >> Add Shape Below.

excel hierarchy tree from data method 1

  • Next, you will see a box created under the Production Manager. Fill it with the appropriate designation.

  • Similarly, Add Shapes below the RND Head and fill them with Departmental Head 1 and 2 as there are two Departmental Heads under the RND Head.

  • After that, add the Executives following the same procedure.

  • Now, to add the labels for the following designations, select any of the Levels and copy it by pressing CTRL+C.

  • After that, press CTRL+V two times to paste the labels.

excel hierarchy tree from data

  • Thereafter, edit the labels correctly.

If you want to change the design of your hierarchy tree, there are some options in the SmartArt Design group.

Thus you can create a Hierarchy Tree using the SmartArt Tools.

Read More: How to Create a Treemap with Multiple Levels In Excel


2. Applying Power Pivot to Create Hierarchy Tree from Data

We can also apply the Power Pivot feature to create a hierarchy tree. This is useful when we want to make a hierarchy tree using an Excel Dataset. Suppose we will make a hierarchy tree based on the department of an organization. The dataset is given in the following picture.

Applying Power Pivot to Create Hierarchy Tree from Data

Steps:

  • First, convert the data range to a table by selecting it and pressing CTRL+T.
  • Make sure you select My table has headers and click OK.

excel hierarchy tree from data method 2

  • After that, go to Power Pivot >> Manage. This command will open the Power Pivot

  • Next, go back to the table and select Insert >> PivotTable.
  • After that, insert the Table/Range (Table1).
  • Thereafter, check to Add this data to the Data Model.
  • Click OK.

  • Next, the command will create a new sheet to store the PivotTable and add the table to the Power Pivot window as well. Select Home >> Diagram View.

  • Later, the Diagram View will appear. Remember that we are creating the hierarchy tree based on the Department and Employee Names. So selecting the Table Headers with an order is necessary.
  • Long press the CTRL key and select Department and Name
  • After that, select Create Hierarchy. You can name it if you want.

  • Next, go back to the Pivot Table sheet and you will find the Hierarchy tree (Hierarchy1) in the PivotTable Fields. Just check it.

excel hierarchy tree from data

  • Thereafter, the hierarchy will appear in the PivotTable showing the Department Names.

  • You can add other PivotTable Fields to have a better and more detailed view of the hierarchy.

  • After that, click on the Plus Icons beside the Department Names to unhide the Employee Names with their Salaries.

Thus you can use the Power Pivot feature to create a Hierarchy from data.

Read More: How to Make a Treemap Chart in Excel


3. Using Charts to Create Hierarchy Treemap from Data

In this section, I’ll show you how you can create a treemap in Excel by using hierarchy data. The data we use in this section contains some football Clubs’ names, their Region and Countries, and also their Net Worth in Million Dollars. The procedure is pretty simple. Let’s have a look at the following instructions.

Steps:

  • First, simply select the entire dataset (B4:E13) and then go to Insert >> Chart >> Treemap.

Using Charts to Create Hierarchy Treemap from Data

  • After that, a treemap will be created. You can see the clubs according to the countries. Each club takes an area in the chart according to their Net Worth.

Thus you can create a hierarchy treemap using Excel charts.

Read More: Create Treemap Chart to Show Values in Excel


Download Practice Workbook


Conclusion

In the end, we can conclude that you will learn the necessary tricks to create a hierarchy tree in Excel from data. If you have any better suggestions, questions, or feedback regarding this article please share them in the comment box. This will help me enrich my upcoming articles.


Related Articles


<< Go Back to Treemap Chart Excel | Excel Charts | 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.
Meraz Al Nahian
Meraz Al Nahian

Md. Meraz Al Nahian has worked with the ExcelDemy project for over 1.5 years. He wrote 140+ articles for ExcelDemy. He also solved a lot of user problems and worked on dashboards. He is interested in data analysis, advanced Excel, statistics, and dashboards. He also likes to explore various Excel and VBA applications. He completed his graduation in Electrical & Electronic Engineering from Bangladesh University of Engineering & Technology (BUET). He enjoys exploring Excel-related features to gain efficiency... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo