In data analysis and data visualizations, a hierarchy is a list of nested columns that can be considered as a single item. It is very helpful for visualizing and representing the pecking order of a hierarchical structure. With the help of Microsoft Excel’s pivot table feature, we can easily create such a hierarchy. In this tutorial, we are going to show detailed ways of different ways you can create and represent hierarchy and hierarchy charts in Excel using pivot table, power pivot, and SmartArt.
Create Hierarchy in Excel Pivot Table: Step-by-Step Procedure
In this section, we are going to demonstrate how to create a hierarchy in Excel using the pivot table feature only. The main idea is to include the child fields of a hierarchy under the parent fields while arranging the pivot table.
For the example, we are going to use the following dataset.
We can see from the dataset that, the states are included in the city in the hierarchy. Follow these detailed steps below to see how we can do that.
Step 1: Select Dataset
First of all, select the whole dataset or the part of the dataset you want to convert into a pivot table. Keep in mind that only the selection of this stage will go into the hierarchy.
We have selected the whole dataset for the demonstration.
Step 2: Convert Dataset into Pivot Table
After we have selected the dataset, we are going to convert it from a mere chart to a pivot table. To do that, go to the Insert tab on your ribbon and then select PivotTable from the Tables group.
A box will appear next. Now select whether you want the pivot table in the existing worksheet or the new worksheet in this box. Here, we are going for the new worksheet option.
Finally, click on OK. Thus a new worksheet will open up that will contain the pivot table.
Read More: How to Make Hierarchy Chart in Excel
Step 3: Drag Parent Fields to Rows
Now go to the newly created spreadsheet and select any cell on the supposed pivot table. On the right side of the spreadsheet, you will find the PivotTable Fields window.
In our dataset, we have the state as the parent field. So click and drag it first to the Rows area and release it.
The pivot table will now look something like this.
Step 4: Drag Child Fields Under Parent Fields
Now do the same for the child fields. In this case, we have only one child field- the city column. So click and drag the city field in the PivotTable Field to the Rows area.
Make sure that the child field is under the parent field after release in the Rows area. If you have more than one child field, maintain the order of the hierarchy here.
The pivot table will now look something like this.
As we can see from the figure, the hierarchy list is created already in the pivot table.
Read More: How to Create a Hierarchy of the State City and Zip Code in Excel
Step 5: Drag Other Necessary Fields
Once you are done with the previous steps, add the extra fields you think necessary to represent your data by the same clicking and dragging process in the PivotTable Field.
For example, we want to add the population column too in this hierarchy pivot table. To include that in the pivot table, click and drag the population field into the Values area and then release it.
Finally, the pivot table will look like this.
This way, we can easily create a hierarchy in an Excel pivot table only.
Read More: How to Create Multi Level Hierarchy in Excel
How to Create Hierarchy in Excel Power Pivot
There is another way to create a hierarchy in Excel using the Power Pivot feature. This feature has been in existence for some time now in Excel. But you have to add it in manually as an extension. Follow these steps to see how you can add power pivot in your Excel and then create the hierarchy.
Steps to Add Power Pivot Feature in Excel:
- First, click on the File tab on the ribbon.
- Then click on Options on the left side of the backstage view.
- After that, the Excel Options box will open up. Now select the Add-ins from the left of the box.
- Next, select COM Add-ins as the Manage option.
- Then click on Go.
- In the COM Add-ins box that pops up next, check the Microsft Power Pivot for Excel option and click on OK.
Finally, you can see the Power Pivot tab appear on your ribbon.
Steps to Create Hierarchy Using Power Pivot:
- First, select the dataset in the Excel spreadsheet.
- Then go to the Insert tab and click on PivotTable from the Tables group.
- In the next box that pops up, select whether you want the pivot table in the existing worksheet or a new one.
- Then make sure to check the Add this data to the Data Model option.
- After that, click on OK.
- Next, go to the Power Pivot tab on your ribbon and select Manage from the Data Model group.
- As a result, the power pivot window will open up. It will look something like this in the figure.
- Now go to the File tab on the ribbon of the power pivot window and select Diagram View from the View group.
The sheet of the power pivot window will now look like this.
- Next, select the fields that will go into the hierarchy and right-click on the selection. Then select Create Hierarchy from the context menu.
- Now create a name for the hierarchy group. We are naming it “Location” here. After naming, press Enter.
- Next, go back to the spreadsheet of the pivot table and select a cell from the pivot table range. You will find the PivotTable Field on the right of the spreadsheet. In it, you can find the newly created hierarchy group in the fields.
- Now click and drag “Location” to the Rows area and release it there.
The pivot table will now look something like this.
- Finally, select any other columns you need and do the same for them to represent them in the pivot table. For example, we are clicking and dragging the population into the Values
Now we can say that our pivot table with the hierarchy is ready on the Excel spreadsheet.
We can see the parent class of the hierarchy initially in the pivot table. To view the child classes, just click on the plus icon at the start of the class and it will expand into the classes that are below them.
To see all, right-click on the column containing the hierarchy and select Expand/Collapse, and then Expand Entire Field from the context menu.
This will expand all of the entries of the hierarchy and it will look something like this.
This way, you can create a hierarchy in Excel using the power pivot feature.
How to Create a Hierarchy Chart in Excel
We can also create a hierarchy chart in Excel. For that, we need to use the SmartArt feature of Microsoft Excel. Follow the steps below to see how we can create a hierarchy chart in Excel using the SmartArt feature.
Steps:
- First, go to the Insert tab on your ribbon.
- Then select SmartArt from the Illustrations group.
- Now select Hierarchy from the left of the Choose a SmartArt Graphic Then select the type of hierarchy chart you want from the right side of the box.
- After clicking on OK, something like this will appear on the spreadsheet.
- On the left of it, under the Type your text here section, manually write down the hierarchy classes. If any of a class is a child class, type it under the parent class and press the Tab button on your keyboard at any point of the typing.
We have written the “New York City” and “Buffalo” with a tab after typing “New York” state.
- To enter a parent class after this, press Shift+Tab on your keyboard and follow the same pattern to enter the parent and child class.
- Similarly, fill out all of the classes manually and you will have something like this.
- After some modifications, you can make it more presentable.
This way you can create a hierarchy chart in Excel easily.
Read More: How to Use SmartArt Hierarchy in Excel
Download Practice Workbook
You can download the workbook used for the demonstration from the download link below.
Conclusion
These were all the procedures you can follow to create hierarchy and hierarchy charts in Excel with and without pivot tables. Hopefully, you can create your desired hierarchy patterns with ease now. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know in the comments below.
Related Articles
<< Go Back to Hierarchy in Excel | SmartArt in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
