How to Create Hierarchy in Excel Pivot Table (with Easy Steps)

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.


Download Practice Workbook

You can download the workbook used for the demonstration from the download link below.


Step-by-Step Procedure to Create Hierarchy in Excel Pivot Table

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.

create hierarchy in excel pivot table

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.

create hierarchy in excel pivot table

Finally, click on OK. Thus a new worksheet will open up that will contain the pivot table.


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.

create hierarchy in excel pivot table

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.

create hierarchy in excel pivot table

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.

create hierarchy in excel pivot table

As we can see from the figure, the hierarchy list is created already in the pivot table.


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.

create hierarchy in excel pivot table

This way, we can easily create a hierarchy in an Excel pivot table only.

Read More: Create Date Hierarchy in Excel Pivot Table (with Easy Steps)


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.

create hierarchy in excel pivot table

  • In the COM Add-ins box that pops up next, check the Microsft Power Pivot for Excel option and click on OK.

create hierarchy in excel pivot table

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.

create hierarchy in excel pivot table

  • 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.

create hierarchy in excel pivot table

  • Next, go to the Power Pivot tab on your ribbon and select Manage from the Data Model group.

create hierarchy in excel pivot table

  • 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.

create hierarchy in excel pivot table

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.

create hierarchy in excel pivot table

  • Now create a name for the hierarchy group. We are naming it “Location” here. After naming, press Enter.

create hierarchy in excel pivot table

  • 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.

create hierarchy in excel pivot table

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

create hierarchy in excel pivot table

Now we can say that our pivot table with the hierarchy is ready on the Excel spreadsheet.

create hierarchy in excel pivot table

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.

create hierarchy in excel pivot table

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.

create hierarchy in excel pivot table

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.

create hierarchy in excel pivot table

  • After some modifications, you can make it more presentable.

create hierarchy in excel pivot table

This way you can create a hierarchy chart in Excel easily.

Read More: How to Use SmartArt Hierarchy in Excel (With Easy Steps)


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.

For more guides like this, visit ExcelDemy.com.


Related Articles

Abrar Niloy

Abrar Niloy

Hi! my name is Abrar-ur-Rahman Niloy. I have completed B.Sc. in Naval Architecture and Marine Engineering. I have found my calling, if you like, in Data Science and Machine Learning and in pursuing so, I have realized the importance of Data Analysis. And Excel is one excel-lent tool do so. I am always trying to learn everyday, and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo