How to Create Multi Level Hierarchy in Excel (2 Easy Ways)

While working in Excel, we often need to create multi level hierarchy. By using a multi level hierarchy, we can represent our data in a simple and concise format. Microsoft Excel is a powerful and versatile software. Using Excel, we can create multi level hierarchy by following some easy steps. In this article, we will learn about 2 simple methods to create multi level hierarchy in Excel.

How to Create Multi Level Hierarchy in Excel: 2 Simple Methods

Of the 2 methods, in the first method, we will use the Data Validation feature of Excel. And in the second method, we will utilize the Power Pivot Add-in of Excel.

Not to mention that we have used Microsoft Excel 365 version for this article, you can use any other version according to your convenience.

1. Using Data Validation Feature to Create Multi Level Hierarchy

Using the Data Validation feature is one of the easiest ways to create a multi-level hierarchy in Excel. In the following dataset, we have Movie Name, Show Schedule, and Seat Type for some ongoing shows of a cineplex. Our aim is to create a multi-level hierarchy using this data. Let’s follow the steps mentioned below to do this.

Step 01: Editing Data Validation Option for Movie Name Column

In the Movie Name column, we need to create a drop-down list so that we can choose any movie we like. It’s quite straightforward to do. Just follow along.

• Firstly, select cell B5 under the column Movie Name.
• Following that, go to the Data tab from the Ribbon.
• Then, click on the Data Validation option from the Data Tools group.

Consequently, the Data Validation dialogue box will open as shown in the following picture.

• Now, click on the drop-down icon as marked in the image given below.
• After that, select List from the drop-down.

• Next, click on the Source box and enter the following formula.

Here, $B$5:$B$10 is the range of the cells under the Movie Name column of the given dataset.

Note: Here, we have used absolute cell reference because in each cell we need to show all of the movie names to choose one from them.

• Following that, click OK.

• Subsequently, a drop-down icon will be available beside cell B5 as marked in the following picture. Click on that.

As a result, in the drop-down list, you can choose any movie you like.

Step 02: Pasting Validation Feature in Remaining Cells in Movie Name Column

Here, we will use the Paste Special feature of Excel to paste the Data Validation feature of cell B5 into the other cells of the column named Movie Name.

• Firstly, select cell B5 and press CTRL + C to copy it.

• After that, select the remaining cells of the column Movie Name.
• Following that go to the Home tab from the Ribbon.
• Next, click on the Paste option.
• Now, select Paste Special option from the drop-down.

• Afterward, from the Paste Special dialogue box, choose the Validation option as marked in the following image.
• Then, click on OK.

Consequently, the Data Validation feature will be copied to the selected cells. You can choose any of the Movie Names from the drop-down list beside each cell and get an output like the following image.

Step 03: Editing Data Validation Option for Show Schedule Column

Now, we need to create a drop-down list based on the selection in the Movie name column. For example, the movie Into the Deep has shows available at 5 different times of the day. But the movie Bullet Train is only available at 3 different times of the day. So, the option that will be available in the Show Schedule column depends on the movie selected in the Movie Name column. To do this we are going to use the OFFSET function and the MATCH function. Let’s follow the steps mentioned below.

• Firstly, select cell C5 and follow the steps mentioned in Step 01 to obtain the following output.

• After that, enter the following formula in the Source Box.

Here, cell $B$14 refers to the header of the Show Schedule of Dataset 1, cell $B5 represents the selected movie under the Movie Name column, and the range $B$14:$G$14 is the array of the name of the movie in the worksheet the Dataset 1.

• Then, click on OK.

• As a result, a drop-down icon will be visible beside cell C5 as shown in the image given below. Click on that.

Consequently, you will see the available Show Schedule for the movie Into the Deep.

Step 04: Pasting Validation Feature in Remaining Cells in Show Schedule Column

Now, we will copy the Data Validation properties of cell C5 into the remaining cells by using the Paste Special feature of Excel.

• Now, select cell C5 and follow the steps mentioned in Step 02.
• Following that, choose the movie Bullet Train, you can see that there are only 3 available options for the Show Schedule just like our given dataset.

• Similarly, you can choose the Show Schedule for the other movies and get an output like the image demonstrated below.

Step 05: Editing Data Validation Option for Seat Type Column

Now, we need to create another drop-down list in the Seat Type column based on our selection of the Show Schedule. In the dataset, we can see that all the Show Schedule don’t have the same Seat Type. For example, for the show at 11:00 AM, all 3 Seat Types are available, but for the show at 6:00 PM, only the Regular and VIP Seat Types are available. To create this dependent drop-down list, we will again use the OFFSET function and the MATCH function. Let’s use the steps discussed below to do this.

• Firstly, select cell D5 and follow the steps mentioned in Step 01 to get the following output.

• After that, enter the following formula in the Source Box in the Data Validation dialogue box.

Here, cell $B$23 refers to the header of Seat Type in Dataset 1, cell $C5 represents the selected Show Schedule, and the range $B$23:$F$23 is the array of the Show Schedule in the worksheet Dataset 1.

• Subsequently, click on OK.

Consequently, you will see a drop-down icon beside cell D5. As the Show Schedule selected is 6:00 PM, the Regular and the VIP options are available in cell D5.

Step 06: Pasting Validation Feature in Remaining Cells in Seat Type Column

At this stage, we will copy the Data Validation properties of cell D5 into the other cells of the column Seat Type. Let’s follow the steps discussed below.

• Firstly, select cell D5 and follow the steps mentioned in Step 02.
• Now, click on the drop-down icon beside cell C6. As the Show Schedule selected is 11:00 AM, you can see that all 3 Seat Types are available.

• Similarly, select the Seat Type option for the other cells and you will get an output like the following picture.

2. Utilizing Power Pivot to Create Multi Level Hierarchy in Excel

Utilizing the Power Pivot feature is another efficient way to create multi-level hierarchy in Excel. In the following dataset, we have a table of Sales data from different Cities in various States of the USA. We will create a multi-level hierarchy using this table. Let’s follow the procedure discussed below.

Step 01: Adding the Table to the Power Pivot Window

If you don’t have the Power Pivot Add-in enabled, then enable it and import data. After you have enabled the Power Pivot Add-in use the steps given below.

• Firstly, select the entire table, and following that press CTRL + C to copy it.

• Now, go to the Power Pivot tab from the Ribbon.
• Then, click on the Manage option as marked in the following image.

As a result, a blank Power Pivot Window will open like in the picture below.

• Next, in the Power Pivot Window click on the Clipboard option.
• Following that, select the Paste option from the drop-down.

Consequently, the Paste Preview dialogue box will be available.

• After that, from the Paste Preview dialogue box, click on OK.

As a result, your table will be added to the Power Pivot Window as shown in the following image.

Step 02: Creating Hierarchy in Diagram View

In this step, we will create a hierarchy that describes the cities under the states and the states under the country.

• Firstly, click on the Diagram View option from the View tab of the Power Pivot window.

Consequently, the Diagram View will be visible on your screen as demonstrated in the following image.

• Now, click on the marked portion of the image given below.

As a result, a Hierarchy will be created.

• Now, drag the column named Country and drop it into the newly created Hierarchy.

• Similarly, drag and drop the other columns sequentially and you will see the following output.

Read More: How to Create a Hierarchy of the State City and Zip Code in Excel

Step 03: Constructing Pivot Table

• Following that, click on the Pivot Table option.

• Subsequently, the Create Pivot Table dialogue box will be available, and click OK.

As a result, a new worksheet will be created as shown in the following picture.

• Afterward, from the Pivot Table Fileds dialogue box, drag and drop the Hierarchy1 into the Rows section and the Sales into the Sum of Values section.

Consequently, you will get the following Pivot Table in your worksheet.

• Now, click on the + sign as marked in the following picture.

As a result, you will see the States, and the Sum of Sales of each State as shown in the following image.

• Afterward, again click on the + sign beside the name of the State Arizona.

This will show the Cities in the State of Arizona along with their Sales.

Similarly, by clicking the + icon beside the other States, you can get the following output.

Read More: How to Create Hierarchy in Excel Pivot Table

Practice Section

In the Excel Workbook, we have provided a Practice Section on the right side of each worksheet. Please practice it by yourself.

Download Practice Workbook

Conclusion

That’s all about today’s session. I strongly believe that this article was able to guide you to create a multi-level hierarchy in Excel. Please feel free to leave a comment if you have any queries or recommendations for improving the article’s quality. Happy learning!

Related Articles

• Create Date Hierarchy in Excel Pivot Table
• How to Use SmartArt Hierarchy in Excel
• How to Add Row Hierarchy in Excel
• How to Make Hierarchy Chart in Excel

<< Go Back to Hierarchy in Excel | SmartArt in Excel | Learn Excel