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.
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.
- 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.
- 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.
- 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.
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.
- 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.
- 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.
- 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
Get FREE Advanced Excel Exercises with Solutions!
