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.
You can download the practice workbook here.
Create Hierarchy.xlsx
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.
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.
- Then, from the dialogue box firstly, select the Hierarchy option.
- Next, choose the type of hierarchy graphic you prefer.
- Finally, click OK.
- From the graphic, click on the outward arrow to get a dialogue box.
- Then, keep the cursor on the dialogue box and press Ctrl+A.
- Consequently, the entire data in the graphic will be selected.
- After that, click the Delete button to delete the default data.
- Next, keep your cursor on the dialogue box and press Ctrl+V.
- Consequently, our dataset will be pasted in the dialogue box.
- 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.
- Next, select the Sales Executive1 option and double Tab.
- Repeat the previous two steps to get a proper hierarchy illustration.
- Finally, you can format the hierarchy tree by using the Layouts and SmartArt Styles options from the SmartArt Design options.
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.
- From the dialogue box, select the range of your dataset as the Table/Range.
- Finally, click OK.
- 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.
- Then select the Revenue option as Values.
- Finally, you get the hierarchy of different teams.
- You can easily show, who works in which team/ department and also their revenue.
- You can also minimize the tabs, to give a terse appearance to your pivot table.
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.
- Click OK, from the Create Table dialogue box.
- Consequently, the dataset will be turned into a 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.
- 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.
- 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.
- After that, from the Home tab select the Pivot Table command.
- Consequently, you will find that a hierarchy is created.
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
- How to Add Row Hierarchy in Excel (2 Easy Methods)