If you want to make a hierarchy chart, then Excel can be really useful. The main focus of this article is to explain how to make hierarchy chart in Excel.
What Is a Hierarchy Chart?
A hierarchy chart shows how a system or organization is broken down into its most manageable components. It often features a top-down structure or a left-right layout. Rectangles in the diagram indicate various system entities, and lines will be utilized to connect them. In some aspects, these diagrams resemble organizational charts.
How to Make Hierarchy Chart in Excel: 3 Easy Ways
Here, I have taken the following dataset to show you how you can make a hierarchy chart. This dataset contains employee information. I will use this dataset to explain how to make a hierarchy chart in Excel. I will show it in 3 different ways.
1. Using SmartArt Feature to Make Hierarchy Chart in Excel
In this method, I will explain how to make hierarchy chart in Excel using the SmartArt feature. Here, I will explain everything about the hierarchy chart in 6 easy steps.
Step-01: Inserting Hierarchy Chart in Excel
In this first step, I will explain how you can insert the hierarchy chart into your Excel sheet.
- Firstly, go to the Insert tab.
- Secondly, select SmartArt.
Now, you will see a dialog box named Choose a SmartArt Graphic will appear.
- Firstly, select the Hierarchy tab.
Here, you will see many options for your hierarchy chart.
- Secondly, chose the option you like. Here, I chose Hierarchy.
- Thirdly, select OK.
Now, you will see your hierarchy chart is inserted into your Excel sheet.
Read More: How to Use SmartArt Hierarchy in Excel
Step-02: Adding Text from a List
In this step, I will show you how you can add text to your hierarchy chart.
- To begin with, select the marked button in the following picture to open the text panel.
Now, the text panel will appear.
At this point, I will add text from my dataset.
- Firstly, select the data range you want to show in your hierarchy chart.
- Secondly, press CTRL+C to copy the data. Here, CTRL+C is the keyboard shortcut for copy.
- Secondly, paste that data into the text panel by pressing CLRT+V.
Here, you can see all the texts are shown in a single line.
Now, I will fix it and add levels to this hierarchy chart according to the dataset.
- Firstly, select the text where you want to add the level. Here, I selected Morty who is the Project Manager.
- Secondly, press Tab once from the keyboard because Morty works under Adam who is the Managing Director.
Here, you can see that Morty’s position is now showing under Adam.
- After that, press Tab twice for Rachel because she works under Morty and Morty works under Adam.
- Now, press Tab thrice for Marry who works under Rachel.
In the same way, I have leveled all the employees and I have got my hierarchy chart.
Now, the Name and the Designation are shown in the same line. I will show you how you can fix that.
- Firstly, put your mouse cursor before the Designation.
- Secondly, press SHIFT+ENTER.
Here, you will see the designation is now in a new line.
In the same way, I have fixed this for all the employees and now my hierarchy chart looks like this.
Now you can change the font size also.
- Firstly, select the data for which you want to change the font size. Here, I selected Adam.
- Secondly, select the font size you want. Here, I selected 14.
In the same way, I changed the font size of the whole hierarchy chart.
In the following picture, you can see my hierarchy chart.
Step-03: Formatting Hierarchy Chart in Excel
Here, I will explain how you can format your hierarchy chart.
- Firstly, select the hierarchy chart.
- Secondly, go to the SmartArt tab.
- Thirdly, select Choose Colors.
Now, a drop-down menu will appear.
- After that, select the color you want for your chart. Here, I selected the marked color from the Colorful Range.
Now, you will see the color of the chart has changed to the selected color.
After that, I will change the style of the chart.
- Firstly, select the hierarchy chart.
- Secondly, go to the SmartArt tab.
- Thirdly, select the style you want for your hierarchy chart. Here, I selected the Subtle Effect.
Now, you can see the chart style has changed to my selected chart style.
Step-04: Rearranging the Order of Nodes
In this step, I will show you how you can rearrange the order of the nodes in your hierarchy chart.
- Firstly, select the hierarchy chart.
- Secondly, go to the SmartArt tab.
- Thirdly, select Right to Left.
Here, you can see the order of the nodes is rearranged in the hierarchy chart.
Step-05: Adding Promotion or Demotion
In this fifth step, I will show you how you can add promotion or demotion to your hierarchy chart.
For example, Ron had been working really hard and for that reason, he got a promotion. He is now a Team Leader. From now, he will report to the Project Manager and Jake will be working in his team.
Let’s see how you update this info in your hierarchy chart.
- Firstly, select the box in your hierarchy chart that carries the information of the employee who got promoted. Here, I selected the box for Ron.
- Secondly, go to the SmartArt tab.
- Thirdly, select Promote.
Now, you can see Ron is working under the Project Manager in the hierarchy chart. But, his designation is still the same as before which is Technical Writer.
- Finally, change the designation. Here, I changed mine to Team Leader.
Step-06: Changing Layout of Hierarchy Chart in Excel
Here, I will explain how you can change the Layout of your hierarchy chart.
- Firstly, select the hierarchy chart.
- Secondly, go to the SmartArt tab.
- Thirdly, select the marked button from Layouts to get all the available Layouts.
Now, you will see a list of layouts will appear.
- After that, select the layout you like. Here, I selected the Horizontal Hierarchy.
Here, you can see the Layout of my hierarchy chart is changed to Horizontal Hierarchy.
Finally, in the following picture, you can see my final hierarchy chart.
2. Employing Excel Add-ins to Make Hierarchy Chart
In this method, I will explain how to make hierarchy chart in Excel by employing Excel Add-ins. I will use the following dataset to explain this method. It contains Employee ID, Employee Name, Designation, Manager ID, and Role Type.
Step-01: Adding Visio Data Visualizer
In this step, I will show you how you can add the Visio Data Visualizer to your Add-ins.
- Firstly, go to the Insert tab.
- Secondly, select Get Add-ins.
Now, the Office Add-ins window will open.
- Firstly, search for Visio Data Visualizer.
- Secondly, select Add.
After that, another window will appear.
- Now, select Continue.
Finally, you will see that the Visio Data Visualizer is added to your Add-ins.
Step-02: Inserting Hierarchy Chart
Now, I will show you how you can Insert hierarchy chart using the Visio Data Visualizer.
- Firstly, go to the Insert tab.
- Secondly, select Visio Data Visualizer.
Here, the Data Visualizer window will appear.
- Now, select Continue without signing in (Preview).
Now, the Data Visualizer will be opened.
- Firstly, select the cell where you want your chart. Here, I selected cell B15.
- Secondly, select Organization Chart.
- Thirdly, select Create from the layout you want. Here, I selected Vertical.
Finally, you will see the hierarchy chart template is inserted along with its data table.
Step-03: Updating Information
In this step, I will show you how you can update your employee information into the template you inserted.
- Firstly, select the column you want to copy.
- Secondly, press CTRL+C to copy the data.
- Thirdly, select the first cell of the column where you want to insert that data. Here, I selected the first cell of the column named Employee ID.
- After that, press CTRL+V to paste the column.
- Now, in the same way, insert all the employee information into the template table.
- After that, select Refresh in the hierarchy chart to update it.
Now, you can see that I have got my desired hierarchy chart.
Here, in the following image, you can see the final hierarchy chart that I made using Excel Add-ins.
3. Using Shapes Feature to Make Hierarchy Chart in Excel
In this section, I will explain how to make hierarchy chart in Excel using the Shapes feature. Here, I will use the following dataset to explain this method.
Let’s see the steps.
Steps:
- Firstly, go to the Insert tab.
- Secondly, select Stapes.
Now, a drop-down menu will appear.
- After that, select any box shape you like. Here, I selected Rectangle: Round Corner.
- Now, click where you want the box and it will be inserted into your Excel sheet.
- After that, you can resize the box if you want. By dragging the marked portion in the following image.
Here, you can see I have resized my box as I wanted.
- Now, copy the box by pressing CTRL+C in your keyboard.
- Then, select where you want it. Here, I selected cell C14.
- Next, press CTRL+V to paste.
In the same way, I have copied 4 more boxes because I have 6 employees in total.
- Now, rearrange the box to give them levels.
Now, I will connect them.
- Firstly, go to the Insert tab.
- Secondly, select Shapes.
- Thirdly, select Line.
- After that, connect the first 2 boxes with the Line.
- Now, connect all the boxes in the same way.
Here, you can see I have connected my boxes.
- Now, type name and designation the way you want to show them in your hierarchy chart. Here, I have typed the Name and Designation in the first box.
Finally, I have written the Name and Designation for every employee and got my hierarchy chart in Excel using the Shapes feature.
Things to Remember
- It should be noted that the third method is only suitable if you have a small set of data otherwise it will be time-consuming.
Practice Section
Here, I have provided a practice sheet for you to practice how to make hierarchy chart in Excel.
Download Practice Workbook
Conclusion
In this article, I tried to cover how to make hierarchy chart in Excel. Here, I explained it in 3 different ways. I hope this article was helpful for you. Lastly, if you have any questions, feel free to let me know in the comment section below.
